数据库环境：SQL SERVER 2008R2
有一个数据表，只有2个字段，一个是日期字段，另一个是数据字段，其中，
日期字段的日期是不连续的。要求：补全日期，对应的数据为上一个日期的数据除于7。
现有数据如图1，
实现的效果如图2（数据太多，已省略部分）
实现思路：
1.用数字辅助表补全缺失的日期
2.将当前开始补录日期到下一个补录日期之间的日期视为一组，不包括下一个补录日期
3.分析函数求得分组内的最大数据，并计算结果
建表，导入测试数据
CREATE TABLE test(cdate DATE,num NUMERIC(6,2))INSERT INTO test VALUES ('2015-10-03','21')INSERT INTO test VALUES ('2015-10-10','49')INSERT INTO test VALUES ('2015-10-17','147')INSERT INTO test VALUES ('2015-10-24','63')View Code
实现
/*取得最小、最大日期*/WITH x0 AS ( SELECT MIN(cdate) AS date_begin , MAX(cdate) AS date_end FROM test ),/*生成最小、最大日期之间的所有日期*/ x1 AS ( SELECT DATEADD(DAY, number, date_begin) AS cdate , number AS rn FROM x0 CROSS APPLY master..spt_values sv WHERE sv.type = 'P' AND sv.number <= DATEDIFF(DAY, date_begin, date_end) ),/*和原表左连接，取到num*/ x2 AS ( SELECT x1.cdate , t.num , rn , CASE WHEN t.num IS NOT NULL THEN 1 ELSE 0 END AS gp FROM x1 LEFT JOIN test t ON t.cdate = x1.cdate ),/*生成分组依据*/ x3 AS ( SELECT cdate , num , ( SELECT SUM(gp) FROM x2 x WHERE x.rn <= x2.rn ) AS gp FROM x2 )/*计算结果*/ SELECT cdate , CASE WHEN num IS NOT NULL THEN num ELSE MAX(num / 7) OVER ( PARTITION BY gp ) END AS num FROM x3
View Code
实现的方法不止这一种，也有网友提供了另一种解法。
WITH tmp AS ( SELECT DATEADD(d, number, '2015-10-03') d , number % 7 number FROM master..spt_values WHERE type = 'P' AND DATEADD(d, number, '2015-10-03') <= '2015-10-24' ) SELECT d , CASE WHEN tmp1.cdate IS NULL THEN t.num / 7 ELSE tmp1.num END AS num FROM tmp LEFT JOIN test tmp1 ON tmp.d = tmp1.cdate OUTER APPLY ( SELECT TOP 1 * FROM test tmp1 WHERE tmp.d > cdate ORDER BY cdate DESC ) t
View Code
我们还可以再升级一下需求，再计算结果的步骤，不再是除于固定值7，而是除于两个日期
之间相差的天数。感兴趣的朋友可以做下，就当练练手。
原标题：补充缺失日期及对应数据
关键词：日期
我知道了 