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='今年'
endif @month=3
begin
select @f= month3 from uTable where years='去年'
select @l= month3 from uTable where years='今年'
endif (@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
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='今年'
endif @month=3
begin
select @f= month3 from uTable where years='去年'
select @l= month3 from uTable where years='今年'
endif (@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
建立去年临时表,今年临时表
每个表字段为 区域,years,month1,month2.....
先将对应区域,年份的值取进相应临时表 没有值的月份填0然后按区域对数据处理 所得结果和原有数据按你所要的最后数据格式填如新的临时表 全部处理完 再返回结果sql server2000 自己也有处理成类似结果的方法 也比较复杂 是什么函数我忘了 去查查book online吧
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 qy,'比例' as years,(case when num>1 then cast(month1/tmonth1 as varchar) else '--' end) as month1,... from (select qy,count(*) as num,sum(case when years='去年' then month1 end) as month1,sum(case when years='今年' then month1) as tmonth1,... from utable group by qy) k
虽然这样写麻烦一些,其实也不怎么麻烦,月数多的时候用循环写一个动态的
sql就行了
但是比写cursor要快得多,因为这个是只检索数据一遍
returns varchar(10)
as begindeclare @f int
declare @l int
declare @r varchar(10)if @month=1
begin
select @f= month1 from test16 where years='去年'
select @l= month1 from test16 where years='今年'
end
if @month=2
begin
select @f= month2 from test16 where years='去年'
select @l= month2 from test16 where years='今年'
endif @month=3
begin
select @f= month3 from test16 where years='去年'
select @l= month3 from test16 where years='今年'
endif (@f is null) or (@l is null) or (@l = 0)
set @r='-'
else
set @r=cast((@f/@l) as int)
return (@r)
end
(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