一张表中 求与上一年的 同比 ym后两位相同的进行计算(2014开头的减去2013开头的求同比)
比如 (201510 - 201410 / 201410 )
想得到的结果如下
后面还有 201509与201409,201503与201403,201505数据为0 ，201405的数据则为21 没有同比
写存储过程传入年份显示全年12个月的同比数据
------解决思路----------------------
-- 参数
DECLARE @y int
SET @y = 2015
;WITH
/* 测试数据
table1(num1,ym)AS(
SELECT CONVERT(decimal(5,2),21.00),201503 UNION ALL
SELECT 3.00,201509 UNION ALL
SELECT 6.00,201510 UNION ALL
SELECT 7.00,201403 UNION ALL
SELECT 21.00,201405 UNION ALL
SELECT 2.00,201409 UNION ALL
SELECT 4.00,201410
), */
a AS (
SELECT num1,
ym % 100 AS m
FROM table1
WHERE (ym / 100) = @y
)
,b AS (
SELECT num1,
ym % 100 AS m
FROM table1
WHERE (ym / 100) = (@y - 1)
)
,c AS (
SELECT number AS m
FROM master..spt_values
WHERE type = 'p'
AND number BETWEEN 1 AND 12
)
SELECT c.m,
a.num1,
b.num1,
CASE WHEN ISNULL(b.num1,0) = 0 THEN
NULL
ELSE
1.0*(ISNULL(a.num1,0)-b.num1)/b.num1
END AS rate
FROM c
LEFT JOIN a ON c.m = a.m
LEFT JOIN b ON c.m = b.m
m num1 num1 rate
----------- -------------- -------------- --------------
1 NULL NULL NULL
2 NULL NULL NULL
3 21.00 7.00 2.000000000
4 NULL NULL NULL
5 NULL 21.00 -1.000000000
6 NULL NULL NULL
7 NULL NULL NULL
8 NULL NULL NULL
9 3.00 2.00 0.500000000
10 6.00 4.00 0.500000000
11 NULL NULL NULL
12 NULL NULL NULL