SQL:自联接构建同比增长率视图

自联接构建同比,同比数据已出来，增长率就简单了

 

--################################################################
drop table #p;

create table #p
(
    id int,
    year varchar(4),
    month varchar(2),
    qty int
)
 
insert into #p values (1,'2012','1',10);
insert into #p values (2,'2012','2',15);
insert into #p values (3,'2012','3',20);
insert into #p values (4,'2013','5',30);
insert into #p values (5,'2013','6',35);
insert into #p values (6,'2013','7',40);
insert into #p values (7,'2013','8',45)
insert into #p values (8,'2013','9',50)
insert into #p values (9,'2013','12',100)
insert into #p values (10,'2014','1',10);
insert into #p values (11,'2014','3',15);
insert into #p values (12,'2014','4',20);
insert into #p values (13,'2014','5',30);
insert into #p values (14,'2014','7',40);
insert into #p values (15,'2014','8',45);
insert into #p values (16,'2014','9',50);
insert into #p values (17,'2015','5',30);
insert into #p values (18,'2016','7',40);
insert into #p values (19,'2017','8',45);
insert into #p values (120,'2017','9',50);

select * from #p

 如图：

 

select t1.v_year as 本年年份, t1.v_month as 本年月份,t1.v_sum as 本月数据,t2.v_sum 上月数据,t2.v_year as 上年年份, t2.v_month as 上月月份,t3.v_sum 本年数据,t4.v_sum 上年数据 from
(
    (
        select distinct cast(year as char(4)) as v_year,cast(month as char(2)) as v_month,sum(qty) over (partition by (cast(year as nvarchar)+'-'+cast(month as nvarchar))) v_sum from #p
    )t1 left join
    (
         select distinct cast(year as char(4)) as v_year,cast(month as char(2)) as v_month,sum(qty) over (partition by (cast(year as nvarchar)+'-'+cast(month as nvarchar))) v_sum  from #p
    )t2 on dateadd(month,0,cast(t1.v_year as nvarchar)+'-'+ cast(t1.v_month as nvarchar)+'-01')=dateadd(month,1,cast(t2.v_year as nvarchar)+'-'+ cast(t2.v_month as nvarchar)+'-01')
     left join(
		select distinct cast(year as char(4)) as v_year,sum(qty) over (partition by (cast(year as nvarchar))) v_sum from #p
    )t3 on t1.v_year=t3.v_year
    left join(
		select distinct cast(year as char(4)) as v_year,sum(qty) over (partition by (cast(year as nvarchar))) v_sum from #p
    )t4 on t1.v_year=t4.v_year+1
   
)

 如图：

 

 

select t1.v_year as 本年年份, t1.v_month as 本年月份,t1.v_sum as 本月数据,t2.v_sum 上月数据,t2.v_year as 上年年份, t2.v_month as 上月月份,t3.v_sum 本年累计,t4.v_sum 上年累计 from  
(  
    (  
        select distinct cast(year as char(4)) as v_year,cast(month as char(2)) as v_month,sum(qty) over (partition by (cast(year as nvarchar)+'-'+cast(month as nvarchar))) v_sum from #p  
    )t1 left join  
    (  
         select distinct cast(year as char(4)) as v_year,cast(month as char(2)) as v_month,sum(qty) over (partition by (cast(year as nvarchar)+'-'+cast(month as nvarchar))) v_sum  from #p  
    )t2 on dateadd(month,0,cast(t1.v_year as nvarchar)+'-'+ cast(t1.v_month as nvarchar)+'-01')=dateadd(month,1,cast(t2.v_year as nvarchar)+'-'+ cast(t2.v_month as nvarchar)+'-01')  
     left join(  
       select _t1.year as v_year,_t1.month as v_month,SUM(_t2.qty) as v_sum from #p _t1 join #p _t2 on _t1.year=_t2.year and cast(_t2.month as int)<=cast(_t1.month as int) group by _t1.year,_t1.month
    )t3 on t1.v_year=t3.v_year and t1.v_month=t3.v_month
    left join(  
       select _t1.year as v_year,_t1.month as v_month,SUM(_t2.qty) as v_sum from #p _t1 join #p _t2 on _t1.year=_t2.year and cast(_t2.month as int)<=cast(_t1.month as int) group by _t1.year,_t1.month
    )t4 on t1.v_year=t4.v_year+1 and t1.v_month=t4.v_month
     
)