大家帮忙看看,这个SQL怎么写?(报表数据源),谢谢(dc_ding已经给答案,但需要修改)

[菜亲虫]

源问题:

源数据:
SID SNAME DATE CLICKS
1 IE 2006-09-01 102
1 IE 2006-09-02 95
1 IE 2006-09-04 69
1 IE 2006-09-05 24
1 IE 2006-09-08 89
1 IE 2006-09-09 145

其中源数据中,缺少，３，６，７ 月的数据
我怎么写SQL能查询出如下结果：
SID SNAME DATE CLICKS
1 IE 2006-09-01 102
1 IE 2006-09-02 95
1 IE 2006-09-03 0(没有出现的月设置为0)
1 IE 2006-09-04 69
1 IE 2006-09-05 24
1 IE 2006-09-06 0
1 IE 2006-09-07 0
1 IE 2006-09-08 89
1 IE 2006-09-09 145

谢谢大家！

----------------------------------------------
dc_ding给出的答案

--建立存储过程
create proc test_proc
@startdate datetime,
@enddate datetime
as
declare @testdb table(SID int, SNAME varchar(10), [DATE] datetime,CLICKS int)
insert @testdb
select 1,'IE','2006-09-01',102 union all
select 1,'IE','2006-09-02',95 union all
select 1,'IE','2006-09-04',69 union all
select 1,'IE','2006-09-05',24 union all
select 1,'IE','2006-09-08',89 union all
select 1,'IE','2006-09-09',145

select
SID=isnull(SID,(select top 1 SID from @testdb where [DATE]<dateadd(day,b.x,a.[DATE]) order by [DATE] desc)),
SNAME=isnull(SNAME,(select top 1 SNAME from @testdb where [DATE]<dateadd(day,b.x,a.[DATE]) order by [DATE] desc)),
[DATE]=dateadd(day,b.x,a.[DATE]),
CLICKS=isnull(c.CLICKS,0)
from
(select min([DATE]) [DATE] from @testdb where [DATE]>=@startdate) a
cross join
(
SELECT b8.i + b7.i + b6.i + b5.i + b4.i + b3.i + b2.i + b1.i + b0.i x
FROM(SELECT 0 i UNION ALL SELECT 1) b0
CROSS JOIN(SELECT 0 i UNION ALL SELECT 2) b1
CROSS JOIN(SELECT 0 i UNION ALL SELECT 4) b2
CROSS JOIN(SELECT 0 i UNION ALL SELECT 8) b3
CROSS JOIN(SELECT 0 i UNION ALL SELECT 16) b4
CROSS JOIN(SELECT 0 i UNION ALL SELECT 32) b5
CROSS JOIN(SELECT 0 i UNION ALL SELECT 64) b6
CROSS JOIN(SELECT 0 i UNION ALL SELECT 128) b7
CROSS JOIN(SELECT 0 i UNION ALL SELECT 256) b8
)b
left join
@testdb c on dateadd(day,b.x,a.[DATE])=c.[DATE]
where dateadd(day,b.x,a.[DATE])<@enddate and dateadd(day,b.x,a.[DATE])>=@startdate
order by 3
go

--调用存储过程
exec test_proc '2006-09-01','2006-09-08'
go

--删除存储过程
drop proc test_proc

--查看结果
/*
SIDSNAMEDATE CLICKS
1IE2006-09-01 00:00:00.000102
1IE2006-09-02 00:00:00.00095
1IE2006-09-03 00:00:00.0000
1IE2006-09-04 00:00:00.00069
1IE2006-09-05 00:00:00.00024
1IE2006-09-06 00:00:00.0000
1IE2006-09-07 00:00:00.0000
1IE2006-09-08 00:00:00.00089
*/


-------------------------------------------
新问题dc_ding给出的答案,有局限性,如果数据源是从09-01到09-08,而exec test_proc '2006-08-01','2006-09-08'(时间段从8月开始)那么,将不出现8月的,就是说dc_ding写的只能以数据源中,最小时间开始.

请问这个过程脚本应该怎样修改才能显示指定时间段内所有月份数据

[九斤半]

自立更生，艰苦创业~~~

[菜亲虫]

哎,自立过,可是就是不会写.时间不等人啊,搞不好要下岗了

[w75251455]

我只喜欢做~不喜欢看!~~~我做个你看看

[菜亲虫]

w75251455,谢谢呀.

[菜亲虫]

大家帮帮忙啊

[w75251455]

--完工
create table 源数据(SID int, SNAME varchar(10), DATE datetime,CLICKS int)
insert 源数据 select 1, 'IE', '2006-09-01', 102
union all select 1, 'IE', '2006-09-02', 95
union all select 1, 'IE', '2006-09-04', 69
union all select 1, 'IE', '2006-09-05', 24
union all select 1, 'IE', '2006-09-08', 89
union all select 1, 'IE', '2006-09-09', 145

drop proc p_ww
create proc p_ww
@sd datetime,
@ed datetime
as
begin
create table #(DATE datetime)
while datediff(dd,@sd,@ed)>=0
begin
insert into # values(@sd)
set @sd = dateadd(d,1,@sd)
end
select SID='1',SNAME='IE',*,isnull((select max(CLICKS) from 源数据 b where b.DATE=a.DATE),0)CLICKS from # a
drop table #
end

exec p_ww '2006-8-8','2006-9-9'

[w75251455]

有点不对...SID 和 SNAME ~~~你想怎么判断~~~~~

[chuifengde]

Create proc Tss
@s varchar(10),
@e varchar(10)
as
declare @MaxV int
declare @sid sysname,@sname sysname,@ye int,@mo int
declare @sql varchar(1000)

declare @a table(SID int, SNAME varchar(10), [DATE] varchar(10), CLICKS int)
insert @a select 1, 'IE', '2006-09-01', 102
union all select 1, 'IE', '2006-09-02', 95
union all select 1, 'IE', '2006-09-04', 69
union all select 1, 'IE', '2006-09-05', 24
union all select 1, 'IE', '2006-09-08', 89
union all select 1, 'IE', '2006-09-09', 145
union all select 2,'dfd','2007-04-28',23
declare cur cursor for
select sid,sname,year([date]) ye,month([date]) mo from @a where [date] between @s and @e group by sid,sname,year([date]),month([date])
open cur
fetch next from cur into @sid,@sname,@ye,@mo
while @@fetch_status=0
begin
if object_id('tmp') is not null
drop table tmp
select @MaxV=max(day([date])) from @a where ([date] between @s and @e) and sid=@sid and sname=@sname and year([date])=@ye and month([date])=@mo
select @sql='select top '+ltrim(@MaxV)+' id=identity(int,1,1),1 b into tmp from syscolumns'
exec(@sql)
insert @a select @sid as sid,@sname sname,ltrim(@ye)+'-'+right('0'+ltrim(@mo),2)+'-'+right('0'+ltrim(id),2) ,0 from tmp a where not exists(select 1 from @a b where ([date] between @s and @e) and sid=@sid and sname=@sname and year([date])=@ye and month([date])=@mo and day([date])=a.id)
fetch next from cur into @sid,@sname,@ye,@mo
end
select * from @a order by sid,sname,[date]
close cur
deallocate cur

go
tss '2006-09-01','2007-07-01'
go

[菜亲虫]

谢谢大家这么热情
chuifengde写的结果和dc_ding一样,不能显示数据源中最小日期以前的日期
w75251455的结果正解,但是会出现多个相同结果数据集

[chuifengde]

要显示最小日期以前的日期，就把里面的条件去掉 [date] between @s and @e

[gc_ding]

接分:)

[gc_ding]

--建立存储过程
create proc test_proc
@startdate datetime,
@enddate datetime
as
declare @testdb table(SID int, SNAME varchar(10), [DATE] datetime,CLICKS int)
insert @testdb
select 1,'IE','2006-09-01',102 union all
select 1,'IE','2006-09-02',95 union all
select 1,'IE','2006-09-04',69 union all
select 1,'IE','2006-09-05',24 union all
select 1,'IE','2006-09-08',89 union all
select 1,'IE','2006-09-09',145

select distinct
SID=isnull(SID,(select top 1 SID from @testdb where [DATE]<dateadd(day,b.x,a.[DATE]) or [DATE]>dateadd(day,-b.x,a.[DATE]) order by [DATE] desc))
,SNAME=isnull(SNAME,(select top 1 SNAME from @testdb where [DATE]<dateadd(day,b.x,a.[DATE]) or [DATE]>dateadd(day,-b.x,a.[DATE]) order by [DATE] desc))
,[DATE]=dateadd(day,b.x,a.[DATE])
,CLICKS=isnull(c.CLICKS,0)
from
(select @startdate as [DATE] from @testdb) a
cross join
(
SELECT b8.i + b7.i + b6.i + b5.i + b4.i + b3.i + b2.i + b1.i + b0.i x
FROM(SELECT 0 i UNION ALL SELECT 1) b0
CROSS JOIN(SELECT 0 i UNION ALL SELECT 2) b1
CROSS JOIN(SELECT 0 i UNION ALL SELECT 4) b2
CROSS JOIN(SELECT 0 i UNION ALL SELECT 8) b3
CROSS JOIN(SELECT 0 i UNION ALL SELECT 16) b4
CROSS JOIN(SELECT 0 i UNION ALL SELECT 32) b5
CROSS JOIN(SELECT 0 i UNION ALL SELECT 64) b6
CROSS JOIN(SELECT 0 i UNION ALL SELECT 128) b7
CROSS JOIN(SELECT 0 i UNION ALL SELECT 256) b8
) b
left join
@testdb c on dateadd(day,b.x,a.[DATE])=c.[DATE]
where dateadd(day,b.x,a.[DATE])<=@enddate and dateadd(day,b.x,a.[DATE])>=@startdate
order by 3
go

--调用存储过程
exec test_proc '2006-08-28','2006-09-12'
go

--删除存储过程
drop proc test_proc

--查看结果
/*
SID SNAME DATE CLICKS
---------------------------------------------------
1 IE 2006-08-28 00:00:00.000 0
1 IE 2006-08-29 00:00:00.000 0
1 IE 2006-08-30 00:00:00.000 0
1 IE 2006-08-31 00:00:00.000 0
1 IE 2006-09-01 00:00:00.000 102
1 IE 2006-09-02 00:00:00.000 95
1 IE 2006-09-03 00:00:00.000 0
1 IE 2006-09-04 00:00:00.000 69
1 IE 2006-09-05 00:00:00.000 24
1 IE 2006-09-06 00:00:00.000 0
1 IE 2006-09-07 00:00:00.000 0
1 IE 2006-09-08 00:00:00.000 89
1 IE 2006-09-09 00:00:00.000 145
1 IE 2006-09-10 00:00:00.000 0
1 IE 2006-09-11 00:00:00.000 0
1 IE 2006-09-12 00:00:00.000 0
*/

[菜亲虫]

结贴给分啦!