比如说有一个数据库里面有
我就是想把去年和今年的12个月份比较。只要去年或今年任意一年有就要列出。
具体情况见下面，我想在存储过程里面解决。请高手帮忙。
qy years month1 month2 month3
上海 去年 1000 2000 2000
南京 去年 2000 1500 1000
北京 去年 1000 1000 2000
上海 今年 1000 4000 1000
武汉 今年 2000 4000 1000
我要把它变成
qy years month1 month2 month3
上海 去年 1000 2000 2000
上海 今年 1000 4000 1000
上海 比例 1 0.5 2
南京 去年 2000 1500 1000
南京 今年 0 0 0
南京 比例 -- -- --
北京 去年 1000 1000 2000
北京 今年 0 0 0
北京 比例 -- -- --
武汉 去年 0 0 0
武汉 今年 2000 4000 1000
武汉 比例 -- -- --
---------------------------------------------------------------
create function getS(@qy nvarchar(20),@month int)
return varchar(10)
as
declare @f decimal(18,3) ,@l decimal(18,3)
if @month=1
begin
select @f= month1 from uTable where years='去年'
select @l= month1 from uTable where years='今年'
end
if @month=2
begin
select @f= month2 from uTable where years='去年'
select @l= month2 from uTable where years='今年'
end
if @month=3
begin
select @f= month3 from uTable where years='去年'
select @l= month3 from uTable where years='今年'
end
if (@f is null) or (@l is null) or (@l = 0)
return '--'
else
return cast((@f / @l) as varchar(10))
--------先寫一個FUNCTION，然後運行：（未測試！）
select * from
(
select * ,'1' as stype from uTable where years = '去年'
union
select * ,'2' as stype from uTable where years = '今年'
union
select qy,'去年',0,0,0,'1'as stype from uTable where qty not in (select qy from uTable where qy= '去年')
union
select qy,'去年',0,0,0,'2'as stype from uTable where qty not in (select qy from uTable where qy= '今年')
union
select distinct qy ,'比例',dbo.getS(qy,1),dbo.getS(qy,2),dbo.getS(qy,3),'3' as stype from uTable
) as Temp
order by qy , stype
---------------------------------------------------------------
select c.qy,c.years,isnull(d.month1,0) as month1,isnull(d.month2,0) as month2,isnull(d.month3,0) as month3
into #Temp
from (
select qy,years
from (
select
distinct qy
from tablename
) as a cross join
(
select '去年' as years
union all
select '今年' as years
) as b
) as c
left join tablename d
on c.qy=d.qy
and c.years=d.years
select
qy,years,month1,month2,month3
from
(
select *,1 as ordertype from #Temp
union all
select t1.qy,'比例'=years,case when t1.month1=0 or t2.month1 then null else t1.month1/t2.month1 end as month1,case when t1.month2=0 or t2.month2 then null else t1.month2/t2.month2 end as month2,case when t1.month3=0 or t2.month3 then null else t1.month3/t2.month3 end as month3,2 as ordertype
from #Temp t1,#Tempt2
where t1.qy=t2.qy
and t1.years='去年'
and t2.years='今年'
) as t3
order by qy,ordertype,years
---------------------------------------------------------------
晚了 呵呵
楼上的改为
select
qy,years,month1,month2,month3
from
(
select *,1 as ordertype from #Temp
union all
select t1.qy,'比例',
case when t1.month1=0 or t2.month1=0 then null else t1.month1/t2.month1 end as month1,
case when t1.month2=0 or t2.month2=0 then null else t1.month2/t2.month2 end as month2,
case when t1.month3=0 or t2.month3=0 then null else t1.month3/t2.month3 end as month3,
2 as ordertype
from #Temp t1,#Temp t2
where t1.qy=t2.qy
and t1.years='去年'
and t2.years='今年'
) as t3
order by qy,ordertype,years
测试能通过了
---------------------------------------------------------------
select X.qy,X.years,X.month1,X.month2,X.month3 from
(select qy,years,month1,month2,month3,'1' as num from tablename where years = '去年'
union
select qy,years,month1,month2,month3,'2' as num from tablename where years = '今年'
union
select A.qy,'比例',
case when (A.month1 is not null) and (B.month1 is not null) then A.month1/B.month1 else '--' end,
case when (A.month2 is not null) and (B.month2 is not null) then A.month2/B.month2 else '--' end,
case when (A.month3 is not null) and (B.month3 is not null) then A.month3/B.month3 else '--' end,
'3' as num from (select qy from tablename group by qy) as A
left join tablename as B on A.qy = B.qy and B.years = '去年'
left join tablename as C on A.qy = B.qy and B.years = '今年') as X
order by X.qy, X.num