求SQL语名统计数据，搞半天了，没解决

[ll_e_mail]

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

[--小F--]

什么意思？每组都去掉一个？

[天-笑]

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