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

ll_e_mail 2012-07-05 03:21:40

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

...全文

80 2 打赏收藏转发到动态举报

写回复

用AI写文章

2 条回复

切换为时间正序

请发表友善的回复…

发表回复

天-笑 2012-07-05

打赏
举报



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