ID TIME
-------------------------
1 2013/11/1 6:00
2 2013/11/1 6:00
3 2013/11/1 6:00
4 2013/11/1 6:00
5 2013/11/1 6:00
1 2013/11/1 7:00
2 2013/11/1 7:00
3 2013/11/1 7:00
4 2013/11/1 7:00
5 2013/11/1 7:00
6 2013/11/1 7:00
7 2013/11/1 7:00
8 2013/11/1 7:00
9 2013/11/1 7:00
77 2013/11/1 8:00
84 2013/11/1 8:00
85 2013/11/1 8:00
88 2013/11/1 8:00
89 2013/11/1 8:00
9 2013/11/1 8:00
-------------------------
需要汇总成如下格式
COUNT TIME
-------------------------
5 2013/11/1 6:00
9 2013/11/1 7:00
6 2013/11/1 8:00
------解决方案--------------------
select count(*) as ct,[Time]
from tb
group by [Time]
------解决方案--------------------
;with cte(ID,[time]) as
(
select 1,'2013/11/1 6:00'
union all select 2,'2013/11/1 6:00'
union all select 3,'2013/11/1 6:00'
union all select 4,'2013/11/1 6:00'
union all select 5,'2013/11/1 6:00'
union all select 1,'2013/11/1 7:00'
union all select 2,'2013/11/1 7:00'
union all select 3,'2013/11/1 7:00'
union all select 4,'2013/11/1 7:00'
union all select 5,'2013/11/1 7:00'
union all select 6,'2013/11/1 7:00'
union all select 7,'2013/11/1 7:00'
union all select 8,'2013/11/1 7:00'
union all select 9,'2013/11/1 7:00'
union all select 77,'2013/11/1 8:00'
union all select 84,'2013/11/1 8:00'
union all select 85,'2013/11/1 8:00'
union all select 88,'2013/11/1 8:00'
union all select 89,'2013/11/1 8:00'
union all select 9,'2013/11/1 8:00'
)
select count(*) as ct,[Time]
from cte
group by [Time]
/*
ct Time
5 2013/11/1 6:00
9 2013/11/1 7:00
6 2013/11/1 8:00
*/