在大家的帮助下,我拼出了这样一个语句,能得出我要的结果
可是过于复杂,在程序中不好用,那位高手帮我把这个语句的第三段优化下,小弟感激涕零
declare @b table(意见 varchar(20),日期 smalldatetime,类型 int)
insert @b select 'asdf','2007-10-11 0:15:15',1
union all select 'fdasd','2007-10-11 0:12:15',2
union all select 'asasd','2007-10-12 10:15:15',3
union all select 'asds', '2007-10-15 03:12:23',4
union all select 'asfd', '2007-10-17 04:32:12',4
union all select 'fdasd','2007-10-11 0:12:15',3
union all select 'asasd','2007-10-12 10:15:15',2
union all select 'asds', '2007-10-15 03:12:23',1
union all select 'asfd', '2007-10-17 04:32:12',1
declare @a table(id int identity(0,1) ,s smalldatetime,e smalldatetime)
declare @s smalldatetime,@e smalldatetime
select @s='2007-10-09',@e='2007-10-20'
insert @a select top 31 null,null from syscolumns
select ee.*,ff.类型3和4意见数 from
(select cc.*,dd.类型2意见数 from
(select convert(varchar(10),aa.ss,120) 日期,count(意见) 类型1意见数 from
(select id,dateadd(day,id,@s) SS from @a where dateadd(day,id,@s)<=@e)aa
left join (select * from @b where 类型 in(1)) bb
on datediff(day,aa.ss,bb.日期)=0
group by aa.ss) cc
left join
(select convert(varchar(10),aa.ss,120) 日期,count(意见) 类型2意见数 from
(select id,dateadd(day,id,@s) SS from @a where dateadd(day,id,@s)<=@e)aa
left join (select * from @b where 类型 in(2)) bb
on datediff(day,aa.ss,bb.日期)=0
group by aa.ss) dd
on cc.日期=dd.日期)ee
left join
(select convert(varchar(10),aa.ss,120) 日期,count(意见) 类型3和4意见数 from
(select id,dateadd(day,id,@s) SS from @a where dateadd(day,id,@s)<=@e)aa
left join (select * from @b where 类型 in(3,4)) bb
on datediff(day,aa.ss,bb.日期)=0
group by aa.ss) ff
on ee.日期=ff.日期
可是过于复杂,在程序中不好用,那位高手帮我把这个语句的第三段优化下,小弟感激涕零
declare @b table(意见 varchar(20),日期 smalldatetime,类型 int)
insert @b select 'asdf','2007-10-11 0:15:15',1
union all select 'fdasd','2007-10-11 0:12:15',2
union all select 'asasd','2007-10-12 10:15:15',3
union all select 'asds', '2007-10-15 03:12:23',4
union all select 'asfd', '2007-10-17 04:32:12',4
union all select 'fdasd','2007-10-11 0:12:15',3
union all select 'asasd','2007-10-12 10:15:15',2
union all select 'asds', '2007-10-15 03:12:23',1
union all select 'asfd', '2007-10-17 04:32:12',1
declare @a table(id int identity(0,1) ,s smalldatetime,e smalldatetime)
declare @s smalldatetime,@e smalldatetime
select @s='2007-10-09',@e='2007-10-20'
insert @a select top 31 null,null from syscolumns
select ee.*,ff.类型3和4意见数 from
(select cc.*,dd.类型2意见数 from
(select convert(varchar(10),aa.ss,120) 日期,count(意见) 类型1意见数 from
(select id,dateadd(day,id,@s) SS from @a where dateadd(day,id,@s)<=@e)aa
left join (select * from @b where 类型 in(1)) bb
on datediff(day,aa.ss,bb.日期)=0
group by aa.ss) cc
left join
(select convert(varchar(10),aa.ss,120) 日期,count(意见) 类型2意见数 from
(select id,dateadd(day,id,@s) SS from @a where dateadd(day,id,@s)<=@e)aa
left join (select * from @b where 类型 in(2)) bb
on datediff(day,aa.ss,bb.日期)=0
group by aa.ss) dd
on cc.日期=dd.日期)ee
left join
(select convert(varchar(10),aa.ss,120) 日期,count(意见) 类型3和4意见数 from
(select id,dateadd(day,id,@s) SS from @a where dateadd(day,id,@s)<=@e)aa
left join (select * from @b where 类型 in(3,4)) bb
on datediff(day,aa.ss,bb.日期)=0
group by aa.ss) ff
on ee.日期=ff.日期
insert @b select 'asdf','2007-10-11 0:15:15',1
union all select 'fdasd','2007-10-11 0:12:15',2
union all select 'asasd','2007-10-12 10:15:15',3
union all select 'asds', '2007-10-15 03:12:23',4
union all select 'asfd', '2007-10-17 04:32:12',4
union all select 'fdasd','2007-10-11 0:12:15',3
union all select 'asasd','2007-10-12 10:15:15',2
union all select 'asds', '2007-10-15 03:12:23',1
union all select 'asfd', '2007-10-17 04:32:12',1 declare @s smalldatetime,@e smalldatetime
select @s='2007-10-09',@e='2007-10-20'
declare @a table(s smalldatetime)
declare @i int
set @i=datediff(d,@s,@e)
while @i>=0
begin
insert into @a values(dateadd(d,-@i,@e))
set @i=@i-1
endselect 日期=convert(varchar(10),a.s,120)
,类型1意见数=isnull(sum1,0)
,类型2意见数=isnull(sum2,0)
,类型3和4意见数=isnull(sum34,0)
from @a a left join
(select 日期=convert(varchar(10),日期,120)
,sum1=sum(case 类型 when 1 then 1 else 0 end)
,sum2=sum(case 类型 when 2 then 1 else 0 end)
,sum34=sum(case when 类型>2 then 1 else 0 end)
from @b group by convert(varchar(10),日期,120)) b on datediff(d, a.s, b.日期)=0
/*
日期 类型1意见数 类型2意见数 类型3和4意见数
---------- ----------- ----------- -----------
2007-10-09 0 0 0
2007-10-10 0 0 0
2007-10-11 1 1 1
2007-10-12 0 1 1
2007-10-13 0 0 0
2007-10-14 0 0 0
2007-10-15 1 0 1
2007-10-16 0 0 0
2007-10-17 1 0 1
2007-10-18 0 0 0
2007-10-19 0 0 0
2007-10-20 0 0 0(12 row(s) affected)
*/