MyException - 我的异常网
当前位置:我的异常网» Java Web开发 » 关于日期连续显示有关问题

关于日期连续显示有关问题(3)

www.MyException.Cn  网友分享于:2013-12-11  浏览:225次

改一下,这样写更合理点
SQL code


create table DAYPRODUCT(id int identity,date smalldatetime,quantity int)

insert DAYPRODUCT
select '2008-3-10',1000
union all
select '2008-3-11',1001
union all
select '2008-3-12',1002
union all
select '2008-3-13',1003
union all
select '2008-3-14',1004
union all
select '2008-3-15',1005

declare @reportDate smalldatetime
set @reportDate='2008-3-1'

;with product as(select date,quantity from DAYPRODUCT where date between @reportDate and dateadd(mm,1,@reportDate)-1)

select t1.date,(case when t2.quantity is null then  0 else t2.quantity end) quantity  
from (select top 31 @reportDate+ row_number() over(order by colid)-1 date,0 quantity from syscolumns) t1 
left join product t2 on t1.date=t2.date and t2.date<dateadd(mm,1,@reportDate) 
order by date

drop table DAYPRODUCT

文章评论

软件开发程序错误异常ExceptionCopyright © 2009-2015 MyException 版权所有