年份和月份是变的,怎么将前面的日(1,2,3,4,5,....,31)都显示出来,记录从每月的一号一直到月末,为空则置为零
例如;
原表为
2003年一月
日期 数据
2 20
3 65
5 54
....
31 89
2003年一月
日期 数据
1 0
2 20
3 65
4 0
5 54
6 0
....
31 89
---------------------------------------------------------------
1，建序数表
select top 8000 identity(int,1,1) as N into numtab from
(select top 100 id=1 from sysobjects) as a,
(select top 100 id=1 from sysobjects) as b,
(select top 100 id=1 from sysobjects) as c
2,
declare @D datetim @EndD datetime
set @d='2003-01-01'
set @endD '2003-01-30'
select @d+a.N-1,isnull(b.数据,0)
from numtab a left join 你的表 b on day(@d+a.N-1)=b.日期
where @d+a.n-1<=@endD
---------------------------------------------------------------
select top 31 identity(int,1,1) as days into #t from sysobjects a ,sysobjects b
select b.* as 日期 ,isnull(a.数据 ,0) 数据
from #t b left join e a
on b.days=a.日期
---------------------------------------------------------------
select tem1.c,isnull(tem2.数据,0) 数据 from (
select top 32 c from
(select t1.b+t2.b*10 c from
(select 1 b union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t1,
(select 0 b union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2
) t5 order by c) tem1 left join 原表 tem2 on tem1.c=tem2.日期
---------------------------------------------------------------
declare @ int --一月的总天数
select top 31 identity(int,1,1) as id_num
into # from syscolums
set @=? --一月的总天数
select b.* as 日期 ,isnull(a.数据 ,0) 数据
from # b left join 原表 a
on b.id_num=a.日期 and id_num<=@
---------------------------------------------------------------
先创建一个函数
create function getdata( p_date in date)
return number is
out_data number;
cursor c1 is
select sum(data) from yourtable where yourdate=p_date;
begin
open c1;
fetch c1 into out_data;
if c1%NOTFOUND then
return 0;
end if;
close c1;
return out_data;
end getdata;