hessen 发表于 2019-11-5 06:52:41

一个表间公式计算同比、年累、环比的方法

对于填报的数据统计时需要月度合计，而后月度会和同期月度数据比较，每月也会计算年度累计数据，然后和同期年度累计数据比较，根据本月与上月的数据计算环比的增长及增长率，下面通过一个表间公式来实现，可以简化原来需要多个公式进行行列匹配的写法以及支持应用与WEBAPP。

下面作为示例说明可以先行在SQL查询分析器执行

创建表并插入数据

CREATE TABLE .(
        (50) NULL,
        NULL,
        (18, 0) NULL
) ON
GO


INSERT INTO . (,,) VALUES ('E','2018-01-01',1)
INSERT INTO . (,,) VALUES ('E','2018-02-01',2)
INSERT INTO . (,,) VALUES ('E','2018-03-01',3)
INSERT INTO . (,,) VALUES ('E','2018-04-01',4)
INSERT INTO . (,,) VALUES ('E','2018-05-01',5)
INSERT INTO . (,,) VALUES ('E','2018-06-01',6)
INSERT INTO . (,,) VALUES ('E','2018-07-01',7)
INSERT INTO . (,,) VALUES ('E','2018-08-01',8)
INSERT INTO . (,,) VALUES ('E','2018-09-01',9)
INSERT INTO . (,,) VALUES ('E','2018-10-01',10)
INSERT INTO . (,,) VALUES ('E','2018-11-01',11)
INSERT INTO . (,,) VALUES ('E','2018-12-01',12)
INSERT INTO . (,,) VALUES ('E','2019-01-01',13)
INSERT INTO . (,,) VALUES ('E','2019-02-01',14)
INSERT INTO . (,,) VALUES ('E','2019-03-01',15)
INSERT INTO . (,,) VALUES ('E','2019-04-01',16)
INSERT INTO . (,,) VALUES ('E','2019-05-01',17)
INSERT INTO . (,,) VALUES ('E','2019-06-01',18)
INSERT INTO . (,,) VALUES ('E','2019-07-01',19)
INSERT INTO . (,,) VALUES ('E','2019-08-01',20)
INSERT INTO . (,,) VALUES ('E','2019-09-01',21)
INSERT INTO . (,,) VALUES ('E','2019-10-01',22)
INSERT INTO . (,,) VALUES ('E','2019-11-01',23)
INSERT INTO . (,,) VALUES ('E','2019-12-01',24)
INSERT INTO . (,,) VALUES ('E','2018-01-02',1)
INSERT INTO . (,,) VALUES ('E','2018-02-02',2)
INSERT INTO . (,,) VALUES ('E','2018-03-02',3)
INSERT INTO . (,,) VALUES ('E','2018-04-02',4)
INSERT INTO . (,,) VALUES ('E','2018-05-02',5)
INSERT INTO . (,,) VALUES ('E','2018-06-02',6)
INSERT INTO . (,,) VALUES ('E','2018-07-02',7)
INSERT INTO . (,,) VALUES ('E','2018-08-02',8)
INSERT INTO . (,,) VALUES ('E','2018-09-02',9)
INSERT INTO . (,,) VALUES ('E','2018-10-02',10)
INSERT INTO . (,,) VALUES ('E','2018-11-02',11)
INSERT INTO . (,,) VALUES ('E','2018-12-02',12)
INSERT INTO . (,,) VALUES ('E','2019-01-02',13)
INSERT INTO . (,,) VALUES ('E','2019-02-02',14)
INSERT INTO . (,,) VALUES ('E','2019-03-02',15)
INSERT INTO . (,,) VALUES ('E','2019-04-02',16)
INSERT INTO . (,,) VALUES ('E','2019-05-02',17)
INSERT INTO . (,,) VALUES ('E','2019-06-02',18)
INSERT INTO . (,,) VALUES ('E','2019-07-02',19)
INSERT INTO . (,,) VALUES ('E','2019-08-02',20)
INSERT INTO . (,,) VALUES ('E','2019-09-02',21)
INSERT INTO . (,,) VALUES ('E','2019-10-02',22)
INSERT INTO . (,,) VALUES ('E','2019-11-02',23)
INSERT INTO . (,,) VALUES ('E','2019-12-02',24)
INSERT INTO . (,,) VALUES ('D','2018-01-01',1)
INSERT INTO . (,,) VALUES ('D','2018-02-01',2)
INSERT INTO . (,,) VALUES ('D','2018-03-01',3)
INSERT INTO . (,,) VALUES ('D','2018-04-01',4)
INSERT INTO . (,,) VALUES ('D','2018-05-01',5)
INSERT INTO . (,,) VALUES ('D','2018-06-01',6)
INSERT INTO . (,,) VALUES ('D','2018-07-01',7)
INSERT INTO . (,,) VALUES ('D','2018-08-01',8)
INSERT INTO . (,,) VALUES ('D','2018-09-01',9)
INSERT INTO . (,,) VALUES ('D','2018-10-01',10)
INSERT INTO . (,,) VALUES ('D','2018-11-01',11)
INSERT INTO . (,,) VALUES ('D','2018-12-01',12)
INSERT INTO . (,,) VALUES ('D','2019-01-01',13)
INSERT INTO . (,,) VALUES ('D','2019-02-01',14)
INSERT INTO . (,,) VALUES ('D','2019-03-01',15)
INSERT INTO . (,,) VALUES ('D','2019-04-01',16)
INSERT INTO . (,,) VALUES ('D','2019-05-01',17)
INSERT INTO . (,,) VALUES ('D','2019-06-01',18)
INSERT INTO . (,,) VALUES ('D','2019-07-01',19)
INSERT INTO . (,,) VALUES ('D','2019-08-01',20)
INSERT INTO . (,,) VALUES ('D','2019-09-01',21)
INSERT INTO . (,,) VALUES ('D','2019-10-01',22)
INSERT INTO . (,,) VALUES ('D','2019-11-01',23)
INSERT INTO . (,,) VALUES ('D','2019-12-01',24)
INSERT INTO . (,,) VALUES ('D','2018-01-02',1)
INSERT INTO . (,,) VALUES ('D','2018-02-02',2)
INSERT INTO . (,,) VALUES ('D','2018-03-02',3)
INSERT INTO . (,,) VALUES ('D','2018-04-02',4)
INSERT INTO . (,,) VALUES ('D','2018-05-02',5)
INSERT INTO . (,,) VALUES ('D','2018-06-02',6)
INSERT INTO . (,,) VALUES ('D','2018-07-02',7)
INSERT INTO . (,,) VALUES ('D','2018-08-02',8)
INSERT INTO . (,,) VALUES ('D','2018-09-02',9)
INSERT INTO . (,,) VALUES ('D','2018-10-02',10)
INSERT INTO . (,,) VALUES ('D','2018-11-02',11)
INSERT INTO . (,,) VALUES ('D','2018-12-02',12)
INSERT INTO . (,,) VALUES ('D','2019-01-02',13)
INSERT INTO . (,,) VALUES ('D','2019-02-02',14)
INSERT INTO . (,,) VALUES ('D','2019-03-02',15)
INSERT INTO . (,,) VALUES ('D','2019-04-02',16)
INSERT INTO . (,,) VALUES ('D','2019-05-02',17)
INSERT INTO . (,,) VALUES ('D','2019-06-02',18)
INSERT INTO . (,,) VALUES ('D','2019-07-02',19)
INSERT INTO . (,,) VALUES ('D','2019-08-02',20)
INSERT INTO . (,,) VALUES ('D','2019-09-02',21)
INSERT INTO . (,,) VALUES ('D','2019-10-02',22)
INSERT INTO . (,,) VALUES ('D','2019-11-02',23)
INSERT INTO . (,,) VALUES ('D','2019-12-02',24)
INSERT INTO . (,,) VALUES ('C','2018-01-01',1)
INSERT INTO . (,,) VALUES ('C','2018-02-01',2)
INSERT INTO . (,,) VALUES ('C','2018-03-01',3)
INSERT INTO . (,,) VALUES ('C','2018-04-01',4)
INSERT INTO . (,,) VALUES ('C','2018-05-01',5)
INSERT INTO . (,,) VALUES ('C','2018-06-01',6)
INSERT INTO . (,,) VALUES ('C','2018-07-01',7)
INSERT INTO . (,,) VALUES ('C','2018-08-01',8)
INSERT INTO . (,,) VALUES ('C','2018-09-01',9)
INSERT INTO . (,,) VALUES ('C','2018-10-01',10)
INSERT INTO . (,,) VALUES ('C','2018-11-01',11)
INSERT INTO . (,,) VALUES ('C','2018-12-01',12)
INSERT INTO . (,,) VALUES ('C','2019-01-01',13)
INSERT INTO . (,,) VALUES ('C','2019-02-01',14)
INSERT INTO . (,,) VALUES ('C','2019-03-01',15)
INSERT INTO . (,,) VALUES ('C','2019-04-01',16)
INSERT INTO . (,,) VALUES ('C','2019-05-01',17)
INSERT INTO . (,,) VALUES ('C','2019-06-01',18)
INSERT INTO . (,,) VALUES ('C','2019-07-01',19)
INSERT INTO . (,,) VALUES ('C','2019-08-01',20)
INSERT INTO . (,,) VALUES ('C','2019-09-01',21)
INSERT INTO . (,,) VALUES ('C','2019-10-01',22)
INSERT INTO . (,,) VALUES ('C','2019-11-01',23)
INSERT INTO . (,,) VALUES ('C','2019-12-01',24)
INSERT INTO . (,,) VALUES ('C','2018-01-02',1)
INSERT INTO . (,,) VALUES ('C','2018-02-02',2)
INSERT INTO . (,,) VALUES ('C','2018-03-02',3)
INSERT INTO . (,,) VALUES ('C','2018-04-02',4)
INSERT INTO . (,,) VALUES ('C','2018-05-02',5)
INSERT INTO . (,,) VALUES ('C','2018-06-02',6)
INSERT INTO . (,,) VALUES ('C','2018-07-02',7)
INSERT INTO . (,,) VALUES ('C','2018-08-02',8)
INSERT INTO . (,,) VALUES ('C','2018-09-02',9)
INSERT INTO . (,,) VALUES ('C','2018-10-02',10)
INSERT INTO . (,,) VALUES ('C','2018-11-02',11)
INSERT INTO . (,,) VALUES ('C','2018-12-02',12)
INSERT INTO . (,,) VALUES ('C','2019-01-02',13)
INSERT INTO . (,,) VALUES ('C','2019-02-02',14)
INSERT INTO . (,,) VALUES ('C','2019-03-02',15)
INSERT INTO . (,,) VALUES ('C','2019-04-02',16)
INSERT INTO . (,,) VALUES ('C','2019-05-02',17)
INSERT INTO . (,,) VALUES ('C','2019-06-02',18)
INSERT INTO . (,,) VALUES ('C','2019-07-02',19)
INSERT INTO . (,,) VALUES ('C','2019-08-02',20)
INSERT INTO . (,,) VALUES ('C','2019-09-02',21)
INSERT INTO . (,,) VALUES ('C','2019-10-02',22)
INSERT INTO . (,,) VALUES ('C','2019-11-02',23)
INSERT INTO . (,,) VALUES ('C','2019-12-02',24)
INSERT INTO . (,,) VALUES ('B','2018-01-01',1)
INSERT INTO . (,,) VALUES ('B','2018-02-01',2)
INSERT INTO . (,,) VALUES ('B','2018-03-01',3)
INSERT INTO . (,,) VALUES ('B','2018-04-01',4)
INSERT INTO . (,,) VALUES ('B','2018-05-01',5)
INSERT INTO . (,,) VALUES ('B','2018-06-01',6)
INSERT INTO . (,,) VALUES ('B','2018-07-01',7)
INSERT INTO . (,,) VALUES ('B','2018-08-01',8)
INSERT INTO . (,,) VALUES ('B','2018-09-01',9)
INSERT INTO . (,,) VALUES ('B','2018-10-01',10)
INSERT INTO . (,,) VALUES ('B','2018-11-01',11)
INSERT INTO . (,,) VALUES ('B','2018-12-01',12)
INSERT INTO . (,,) VALUES ('B','2019-01-01',13)
INSERT INTO . (,,) VALUES ('B','2019-02-01',14)
INSERT INTO . (,,) VALUES ('B','2019-03-01',15)
INSERT INTO . (,,) VALUES ('B','2019-04-01',16)
INSERT INTO . (,,) VALUES ('B','2019-05-01',17)
INSERT INTO . (,,) VALUES ('B','2019-06-01',18)
INSERT INTO . (,,) VALUES ('B','2019-07-01',19)
INSERT INTO . (,,) VALUES ('B','2019-08-01',20)
INSERT INTO . (,,) VALUES ('B','2019-09-01',21)
INSERT INTO . (,,) VALUES ('B','2019-10-01',22)
INSERT INTO . (,,) VALUES ('B','2019-11-01',23)
INSERT INTO . (,,) VALUES ('B','2019-12-01',24)
INSERT INTO . (,,) VALUES ('B','2018-01-02',1)
INSERT INTO . (,,) VALUES ('B','2018-02-02',2)
INSERT INTO . (,,) VALUES ('B','2018-03-02',3)
INSERT INTO . (,,) VALUES ('B','2018-04-02',4)
INSERT INTO . (,,) VALUES ('B','2018-05-02',5)
INSERT INTO . (,,) VALUES ('B','2018-06-02',6)
INSERT INTO . (,,) VALUES ('B','2018-07-02',7)
INSERT INTO . (,,) VALUES ('B','2018-08-02',8)
INSERT INTO . (,,) VALUES ('B','2018-09-02',9)
INSERT INTO . (,,) VALUES ('B','2018-10-02',10)
INSERT INTO . (,,) VALUES ('B','2018-11-02',11)
INSERT INTO . (,,) VALUES ('B','2018-12-02',12)
INSERT INTO . (,,) VALUES ('B','2019-01-02',13)
INSERT INTO . (,,) VALUES ('B','2019-02-02',14)
INSERT INTO . (,,) VALUES ('B','2019-03-02',15)
INSERT INTO . (,,) VALUES ('B','2019-04-02',16)
INSERT INTO . (,,) VALUES ('B','2019-05-02',17)
INSERT INTO . (,,) VALUES ('B','2019-06-02',18)
INSERT INTO . (,,) VALUES ('B','2019-07-02',19)
INSERT INTO . (,,) VALUES ('B','2019-08-02',20)
INSERT INTO . (,,) VALUES ('B','2019-09-02',21)
INSERT INTO . (,,) VALUES ('B','2019-10-02',22)
INSERT INTO . (,,) VALUES ('B','2019-11-02',23)
INSERT INTO . (,,) VALUES ('B','2019-12-02',24)
INSERT INTO . (,,) VALUES ('A','2018-01-01',1)
INSERT INTO . (,,) VALUES ('A','2018-02-01',2)
INSERT INTO . (,,) VALUES ('A','2018-03-01',3)
INSERT INTO . (,,) VALUES ('A','2018-04-01',4)
INSERT INTO . (,,) VALUES ('A','2018-05-01',5)
INSERT INTO . (,,) VALUES ('A','2018-06-01',6)
INSERT INTO . (,,) VALUES ('A','2018-07-01',7)
INSERT INTO . (,,) VALUES ('A','2018-08-01',8)
INSERT INTO . (,,) VALUES ('A','2018-09-01',9)
INSERT INTO . (,,) VALUES ('A','2018-10-01',10)
INSERT INTO . (,,) VALUES ('A','2018-11-01',11)
INSERT INTO . (,,) VALUES ('A','2018-12-01',12)
INSERT INTO . (,,) VALUES ('A','2019-01-01',13)
INSERT INTO . (,,) VALUES ('A','2019-02-01',14)
INSERT INTO . (,,) VALUES ('A','2019-03-01',15)
INSERT INTO . (,,) VALUES ('A','2019-04-01',16)
INSERT INTO . (,,) VALUES ('A','2019-05-01',17)
INSERT INTO . (,,) VALUES ('A','2019-06-01',18)
INSERT INTO . (,,) VALUES ('A','2019-07-01',19)
INSERT INTO . (,,) VALUES ('A','2019-08-01',20)
INSERT INTO . (,,) VALUES ('A','2019-09-01',21)
INSERT INTO . (,,) VALUES ('A','2019-10-01',22)
INSERT INTO . (,,) VALUES ('A','2019-11-01',23)
INSERT INTO . (,,) VALUES ('A','2019-12-01',24)
INSERT INTO . (,,) VALUES ('A','2018-01-02',1)
INSERT INTO . (,,) VALUES ('A','2018-02-02',2)
INSERT INTO . (,,) VALUES ('A','2018-03-02',3)
INSERT INTO . (,,) VALUES ('A','2018-04-02',4)
INSERT INTO . (,,) VALUES ('A','2018-05-02',5)
INSERT INTO . (,,) VALUES ('A','2018-06-02',6)
INSERT INTO . (,,) VALUES ('A','2018-07-02',7)
INSERT INTO . (,,) VALUES ('A','2018-08-02',8)
INSERT INTO . (,,) VALUES ('A','2018-09-02',9)
INSERT INTO . (,,) VALUES ('A','2018-10-02',10)
INSERT INTO . (,,) VALUES ('A','2018-11-02',11)
INSERT INTO . (,,) VALUES ('A','2018-12-02',12)
INSERT INTO . (,,) VALUES ('A','2019-01-02',13)
INSERT INTO . (,,) VALUES ('A','2019-02-02',14)
INSERT INTO . (,,) VALUES ('A','2019-03-02',15)
INSERT INTO . (,,) VALUES ('A','2019-04-02',16)
INSERT INTO . (,,) VALUES ('A','2019-05-02',17)
INSERT INTO . (,,) VALUES ('A','2019-06-02',18)
INSERT INTO . (,,) VALUES ('A','2019-07-02',19)
INSERT INTO . (,,) VALUES ('A','2019-08-02',20)
INSERT INTO . (,,) VALUES ('A','2019-09-02',21)
INSERT INTO . (,,) VALUES ('A','2019-10-02',22)
INSERT INTO . (,,) VALUES ('A','2019-11-02',23)
INSERT INTO . (,,) VALUES ('A','2019-12-02',24)


计算得到结果    回帖后可见
**** Hidden Message *****

得到的结果


shuzhongsw 发表于 2019-11-18 16:38:13

1111111111111111111111111111111111

yzsteven 发表于 2020-3-6 21:45:48

学习一下，看看是怎样操作的

jimmie 发表于 2020-4-21 11:17:13

很好
   

※MVP※ 发表于 2020-4-24 17:18:53

来学习，学习

258998357 发表于 2020-6-17 14:19:57

学习一下，哈哈

buhe272003 发表于 2020-7-28 13:01:42

这个厉害了，我学习下。

xzlzl 发表于 2020-8-28 11:42:41

不知具体如何操作，高大上！

班明生 发表于 2020-9-4 11:18:41

学习一下，大佬加油

dehuizhi 发表于 2020-12-14 10:54:21

RE: 一个表间公式计算同比、年累、环比的方法 [修改]
页: [1] 2
查看完整版本: 一个表间公式计算同比、年累、环比的方法