你的位置:首页 > 数据库

[数据库]从时间段生成【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-092015-102015-112015-122016-012016-022016-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) 数量 --无数据的月份,数量设为0FROM (    --模拟某个按月份的统考计结果,中间存在无数据的月份    SELECT '2015-09' 年月, 100 数量    UNION ALL    SELECT '2015-12' 年月, 45 数量    UNION ALL    SELECT '2016-02' 年月, 78 数量) AFULL 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 1002015-12 452016-02 78(3 行受影响)--------------------------------------------------------------月份连续的结果年月   数量------- -----------2015-09 1002015-10 02015-11 02015-12 452016-01 02016-02 782016-03 0(7 行受影响)*/