从时间段生成【yyyy年mm月】行

代码：

DECLARE @startDate DATETIME = '2015-09-01'
       ,@endDate DATETIME = '2016-03-01'

--写法一
SELECT CONVERT(NVARCHAR(7),DATEADD(MONTH,number,@startDate),120) 年月
FROM master..spt_values 
WHERE TYPE = 'p'
      AND CONVERT(nvarchar(10),dateadd(MONTH,number,@startDate),120)<=@endDate

--写法二
SELECT CONVERT(NVARCHAR(7),DATEADD(MONTH,number,@startDate),120) 年月
FROM master..spt_values 
WHERE TYPE = 'p'
      AND number<=DATEDIFF(MONTH,@startDate,@endDate)

/*

年月
-------
2015-09
2015-10
2015-11
2015-12
2016-01
2016-02
2016-03

(7 行受影响)

*/

spt_values说明：

1. master..spt_values是内部字典表，供SQL Server内部使用，许多系统存储过程和函数的源代码中都使用到了它；
2. 列名分别为名称、值、类型、下限、上限、状态；
3. 类型列的取值含义：D=Database Option P=Projection DBR=Database Role DC=Database Replication I=Index L=Locks V=Device Type
   因为比较多，无法一一列举。其中类型P较为特殊，它只是0-2047(与版本有关)之间的数字的简单列表，作为对所有类型之间关系的预测。
4. msdn查不到master..spt_values的说明，不需要太深入了解此表。

 

应用场景：

按月份的统计时，若某些月份无数据，统计结果就不会是月份上连续的。可以通过下面方法得到月份连续的结果：

DECLARE @startDate DATETIME = '2015-09-01'
       ,@endDate DATETIME = '2016-03-01'

SELECT B.年月,ISNULL(A.数量,0) 数量  --无数据的月份，数量设为0
FROM (
        --模拟某个按月份的统考计结果，中间存在无数据的月份
        SELECT '2015-09' 年月, 100 数量
        UNION ALL
        SELECT '2015-12' 年月, 45 数量
        UNION ALL
        SELECT '2016-02' 年月, 78 数量
) A
FULL JOIN 
(
        --用于生成连续的月份
        SELECT CONVERT(NVARCHAR(7),DATEADD(MONTH,number,@startDate),120) 年月
        FROM master..spt_values 
        WHERE TYPE = 'p'
              AND number<=DATEDIFF(MONTH,@startDate,@endDate)
) B ON A.年月 = B.年月
ORDER BY B.年月+'-01'

/*
--月份不连续的结果
年月      数量
------- -----------
2015-09 100
2015-12 45
2016-02 78

(3 行受影响)

------------------------------------------------------------
--月份连续的结果

年月      数量
------- -----------
2015-09 100
2015-10 0
2015-11 0
2015-12 45
2016-01 0
2016-02 78
2016-03 0

(7 行受影响)
*/

 

转载于:https://www.cnblogs.com/startonly/p/5960339.html