日期:2014-05-18 浏览次数:20487 次
declare @t1 table( id int, ext varchar(10), [time] datetime) insert into @t1 (id,ext,[time])values(0,'a','2012-04-10 13:20:04.000') insert into @t1 (id,ext,[time])values(0,'a','2012-04-10 14:29:12.000') insert into @t1 (id,ext,[time])values(0,'a','2012-04-25 18:29:34.000') insert into @t1 (id,ext,[time])values(1,'b','2012-04-25 14:03:18.000') insert into @t1 (id,ext,[time])values(1,'b','2012-04-25 10:53:08.000') insert into @t1 (id,ext,[time])values(1,'b','2012-04-30 11:51:38.000') insert into @t1 (id,ext,[time])values(1,'c','2012-04-30 12:09:36.000') insert into @t1 (id,ext,[time])values(1,'c','2012-04-30 17:04:05.000') select * from @t1 -----所要的结果----- --id ext count time --0 a 2 2012-04-10 --0 a 1 2012-04-25 --1 b 2 2012-04-25 --1 b 1 2012-04-30 --1 c 2 2012-04-30 select id,ext,count(id) as [count] ,convert(varchar(10),[time],120) as [Time] from @t1 group by id,ext,convert(varchar(10),[time],120) ----------------------------- id ext time ----------- ---------- ----------------------- 0 a 2012-04-10 13:20:04.000 0 a 2012-04-10 14:29:12.000 0 a 2012-04-25 18:29:34.000 1 b 2012-04-25 14:03:18.000 1 b 2012-04-25 10:53:08.000 1 b 2012-04-30 11:51:38.000 1 c 2012-04-30 12:09:36.000 1 c 2012-04-30 17:04:05.000 (8 行受影响) id ext count Time ----------- ---------- ----------- ---------- 0 a 2 2012-04-10 0 a 1 2012-04-25 1 b 2 2012-04-25 1 b 1 2012-04-30 1 c 2 2012-04-30 (5 行受影响)