大家帮忙看看,这个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写的只能以数据源中,最小时间开始.
请问这个过程脚本应该怎样修改才能显示指定时间段内所有月份数据
------解决方案--------------------
有点不对...SID 和 SNAME ~~~你想怎么判断~~~~~
[解决办法]
要显示最小日期以前的日期，就把里面的条件去掉 [date] between @s and @e
[解决办法]
--建立存储过程
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
--查看结果
/*
SIDSNAMEDATECLICKS
---------------------------------------------------
1IE2006-08-28 00:00:00.0000
1IE2006-08-29 00:00:00.0000
1IE2006-08-30 00:00:00.0000
1IE2006-08-31 00:00:00.0000
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
1IE2006-09-09 00:00:00.000145
1IE2006-09-10 00:00:00.0000
1IE2006-09-11 00:00:00.0000
1IE2006-09-12 00:00:00.0000
*/
