关于汇总的问题
ID TIME
-------------------------
12013/11/1 6:00
22013/11/1 6:00
32013/11/1 6:00
42013/11/1 6:00
52013/11/1 6:00
12013/11/1 7:00
22013/11/1 7:00
32013/11/1 7:00
42013/11/1 7:00
52013/11/1 7:00
62013/11/1 7:00
72013/11/1 7:00
82013/11/1 7:00
92013/11/1 7:00
772013/11/1 8:00
842013/11/1 8:00
852013/11/1 8:00
882013/11/1 8:00
892013/11/1 8:00
92013/11/1 8:00
-------------------------
需要汇总成如下格式
COUNT TIME
-------------------------
52013/11/1 6:00
92013/11/1 7:00
62013/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]
/*
ctTime
52013/11/1 6:00
92013/11/1 7:00
62013/11/1 8:00
*/
