自联接构建同比,同比数据已出来,增长率就简单了
--################################################################ 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 )
相关推荐
内容概要:本文介绍了MyBatis动态SQL的基本概念、常用标签和使用技巧,帮助读者了解如何构建灵活的查询语句。 使用人群:适用于使用MyBatis框架进行数据库操作的开发者,尤其是需要构建复杂查询逻辑的开发者。 ...
解决方案:使用PDI构建开源ETL解决方案源码示例,书中PDI示例和SQL
sql临时创建视图.sql
SQL SERVER 多表联接查询SQL SERVER 多表联接查询SQL SERVER 多表联接查询
1,什么是视图? 2,为什么要用视图; 3,视图中的ORDER BY; 4,刷新视图; ...当你查询视图时,无论是获取数据还是更新数据,Sql server都用视图的定义来访问基础表; 视图在我们日常操作也扮演
1. 掌握索引的创建语法 2. 掌握视图的创建语法 3. 使用视图更新数据 4. 编写各种批量
三个SQL视图查出所有SQL Server数据库字典! 值得下载看看!资源免费,大家分享!! 更多免费资源 http://ynsky.download.csdn.net/
sqlserver数据库角色创建及授权,视图的应用
新建视图: 保存视图: 刷新视图节点: 查看视图: 维护视图: 删除视图: 方式2-SQL语句: 创建视图: 查看视图中的数据: 修改视图: 删除视图: 方式1-图形用户界面: 新建视图: 保存视图: 刷新视图...
Delphi创建SQLserver数据库视图的例子,使用create View语句来生成视图,当然本程序创建好视图后,也可删除视图,下面是详细的代码: //创建一个视图: procedure TForm1.Button1Click(Sender: TObject); ...
HANA_SQL语句和系统视图 了解Hana的SQL语句 内存数据库CURD的基本操作
sql server视图详解,详细介绍了sql server的视图原理
SQL相关资料
sql Server 的视图管理 学习资料
SQL概要 汇报人姓名 SQL 指结构化查询语言,全称是 Structured Query Language。 SQL 是用于访问和处理数据库的标准的计算机语言。...SQL:1999(SQL3) SQL-92(SQL2) SQL-89(FIPS 127-1) 1974年 2.SQL标准发展过程 2
表联接.sql qq:292258449
用于从一个sql数据库中把视图批量一下复制到另外一个数据库。
sqlsever 刷新(更新)数据库中的所有的视图,用于表添加字段,但是视图还是没有字段
视图 sql server
从数据库管理系统 (DBMS) 的观点来看,视图是数据(元数据)的说明。创建典型视图时,通过 SELECT 语句(定义一个显示为虚拟表的结果集)来定义元数据。当其它查询的 FROM 子句中引用了某个视图时,将从系统目录中...