大家帮忙看看,这个SQL怎么写?(报表数据源),谢谢
源数据:
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
谢谢大家！
[解决办法]
create table T(SID int, SNAME varchar(10), [DATE] datetime, CLICKS int)
insert T 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 A.[Date], CLICKS=isnull(T.CLICKS, 0) from
(
select [Date]= '2006-09-01 '
union all select '2006-09-02 '
union all select '2006-09-03 '
union all select '2006-09-04 '
union all select '2006-09-05 '
union all select '2006-09-06 '
union all select '2006-09-07 '
union all select '2006-09-08 '
union all select '2006-09-09 '
)A
left join T on A.[Date]=T.[Date]
[解决办法]
--建立存储过程
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
*/
