Create table 表A ( 日期 varchar(8), 数量 int)Insert into 表A select '200401',10 union all select '200401',10 union all select '200402',20 union all select '200405',20 union all select '200405',30 union all select '200501',10 union all select '200502',10 union all select '200502', 20 union all select '200503',20 union all select '200506',30 go select 日期1=IsNULL(a.日期,b.日期-100), 日期2=IsNULL(b.日期,a.日期+100), 数量1=IsNULL(a.数量,0),数量2=IsNULL(b.数量,0), '同比(%)'=IsNULL(IsNULL(b.数量,0)*100/a.数量,0) from ( Select 日期,sum(数量) as 数量 from 表A where 日期 between '200401' and '200403' group by 日期 ) a full join (Select 日期,sum(数量) as 数量 from 表A where 日期 between '200501' and '200503' group by 日期 ) b on Right(a.日期,2)=Right(b.日期,2) drop table 表A
select *,convert(float,b.数量2)/convert(float,a.数量1)*100 as 同比 from (select 日期 as 日期1,sum(数量) as 数量1 from a where 日期 in (select distinct 日期 from a where left(日期,4)='2004') group by 日期) a join (select 日期 as 日期2,sum(数量) as 数量2 from a where 日期 in (select distinct 日期 from a where left(日期,4)='2005') group by 日期) b on right(a.日期1,1)=right(b.日期2,1)
select 日期1=IsNULL(a.日期,b.日期-100), 日期2=IsNULL(b.日期,a.日期+100), 数量1=IsNULL(a.数量,0),数量2=IsNULL(b.数量,0), '同比(%)'=IsNULL(IsNULL(b.数量,0)*100/a.数量,0) from ( Select 日期,sum(数量) as 数量 from 表A where 日期 between '200401' and '200403' group by 日期 ) a full join (Select 日期,sum(数量) as 数量 from 表A where 日期 between '200501' and '200503' group by 日期 ) b on Right(a.日期,2)=Right(b.日期,2)
--建测试表 create table #biao(rq char(6),num dec(5,2)) insert #biao values('200401', 10) insert #biao values('200401', 10) insert #biao values('200402', 20) insert #biao values('200405', 20) insert #biao values('200405', 30) insert #biao values('200501', 10) insert #biao values('200502', 10) insert #biao values('200502', 20) insert #biao values('200503', 20) insert #biao values('200506', 30) go--查询 select (case when a.rq is null then '2004'+right(b.rq,2) else a.rq end) as rq, b.rq, (case when a.num is null then 0 else a.num end) as num,b.num,(case when a.num is null then 0 else b.num/a.num end) as num1 from (select rq,right(rq,2) as mon ,sum(num) as num from #biao where left(rq,4)='2004' group by rq) a, (select rq,right(rq,2) as mon ,sum(num) as num from #biao where left(rq,4)='2005' group by rq) b where a.mon=*b.mon--删除测试表 drop table #biao
Create table 表A
( 日期 varchar(8),
数量 int)Insert into 表A
select '200401',10
union all select '200401',10
union all select '200402',20
union all select '200405',20
union all select '200405',30
union all select '200501',10
union all select '200502',10
union all select '200502', 20
union all select '200503',20
union all select '200506',30 go
select 日期1=IsNULL(a.日期,b.日期-100),
日期2=IsNULL(b.日期,a.日期+100),
数量1=IsNULL(a.数量,0),数量2=IsNULL(b.数量,0),
'同比(%)'=IsNULL(IsNULL(b.数量,0)*100/a.数量,0)
from ( Select 日期,sum(数量) as 数量
from 表A
where 日期 between '200401' and '200403'
group by 日期 ) a
full join
(Select 日期,sum(数量) as 数量 from 表A
where 日期 between '200501' and '200503'
group by 日期 ) b
on Right(a.日期,2)=Right(b.日期,2) drop table 表A
Result:
-------------------
日期1 日期2 数量1 数量2 同比(%)
200401 200501 20 10 50
200402 200502 20 30 150
200403 200503 0 20 0
from (select 日期 as 日期1,sum(数量) as 数量1
from a
where 日期 in
(select distinct 日期
from a
where left(日期,4)='2004')
group by 日期) a join
(select 日期 as 日期2,sum(数量) as 数量2
from a
where 日期 in
(select distinct 日期
from a
where left(日期,4)='2005')
group by 日期) b
on right(a.日期1,1)=right(b.日期2,1)
日期2=IsNULL(b.日期,a.日期+100),
数量1=IsNULL(a.数量,0),数量2=IsNULL(b.数量,0),
'同比(%)'=IsNULL(IsNULL(b.数量,0)*100/a.数量,0)
from ( Select 日期,sum(数量) as 数量
from 表A
where 日期 between '200401' and '200403'
group by 日期 ) a
full join
(Select 日期,sum(数量) as 数量 from 表A
where 日期 between '200501' and '200503'
group by 日期 ) b
on Right(a.日期,2)=Right(b.日期,2)
create table #biao(rq char(6),num dec(5,2))
insert #biao values('200401', 10)
insert #biao values('200401', 10)
insert #biao values('200402', 20)
insert #biao values('200405', 20)
insert #biao values('200405', 30)
insert #biao values('200501', 10)
insert #biao values('200502', 10)
insert #biao values('200502', 20)
insert #biao values('200503', 20)
insert #biao values('200506', 30)
go--查询
select (case when a.rq is null then '2004'+right(b.rq,2) else a.rq end) as rq,
b.rq, (case when a.num is null then 0 else a.num end) as num,b.num,(case when a.num is null then 0 else b.num/a.num end) as num1
from
(select rq,right(rq,2) as mon ,sum(num) as num from #biao where left(rq,4)='2004' group by rq) a,
(select rq,right(rq,2) as mon ,sum(num) as num from #biao where left(rq,4)='2005' group by rq) b
where a.mon=*b.mon--删除测试表
drop table #biao