22,300
社区成员




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 行受影响)