怎么用Spt_Values解決SQL中的連續日期問題

本篇內容主要講解“怎么用Spt_Values解決SQL中的連續日期問題”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學習“怎么用Spt_Values解決SQL中的連續日期問題”吧!

spt_values是什么

spt_values是SQL Server新增的一個系統表，表里面都是一些枚舉數據。我們可以通過如下查詢語句來查看里面的數據

select * from master..spt_values

(因為該表屬于系統數據庫master下面，所以通常在表名前面添加庫名master)

結果為：

(記錄較多，只截取部分記錄)

spt_values連續記錄

但是通常我們使用的是Type='P'的數據記錄，這些記錄是一組從0開始，2047為止的連續整數，具體如下：

select * from master..spt_values where type='P'

結果為：

(記錄較多，只截取部分記錄)

我們經常使用的就是number列，通過該列我們可以生成很多連續的記錄，包括連續的日期，例如每天的24小時，每個月的每天，每年的12個月等等。

生成每天的24小時我們只需要指定開始和結束時間，就可以生成該時間段的連續小時了，這里從0點到23點。

SELECT    SUBSTRING(CONVERT(CHAR(32),   DATEADD(HH,number,CONCAT('2021-01-05',' ', '00:00')),120),1,16) AS GroupDay FROM   master..spt_values  WHERE TYPE = 'P'  AND DATEDIFF(HH,DATEADD(HH,number, CONCAT('2021-01-05',' ', '00:00')), CONCAT('2021-01-05',' ', '23:00'))>=0

(提示：可以左右滑動代碼)結果為：

(完整的有24條記錄，這里只截取前幾條)

生成每月的每天我們只需要指定開始和結束日期，就可以生成該日期段的連續天了，這里從1月1日到1月31日。

SELECT    CONVERT(NVARCHAR(10), DATEADD(DAY, number, '2021-01-01'),120) AS GroupDay FROM    master..spt_values  WHERE TYPE = 'P'   AND number <= DATEDIFF(DAY, '2021-01-01', '2021-01-31')

結果為：

(完整的有31條記錄，這里只截取前幾條)

生成每年的每月我們只需要指定開始和結束月份，就可以生成該月份段的連續月了，這里從1月到12月。

SELECT    SUBSTRING(CONVERT(NVARCHAR(10), DATEADD(MONTH, number, '2021-01-01'),120),1,7) AS GroupMonth FROM    master..spt_values  WHERE TYPE = 'P'   AND number <= DATEDIFF(MONTH, '2021-01-01', '2021-12-01')

結果為：

spt_values應用實例

有如下一張表Test

要求：顯示1月份所有日期的DataValue值，如果沒有值的，就顯示為0。

分析：我們數據庫中只存儲了4條數據，這時候我們可以利用SQL的表spt_values來實現。

解法：

SELECT DATEADD(DAY, number, CONVERT(DATETIME, '2021-01-01')) [DataTime],        ISNULL(DataValue,0) DataValue FROM master..spt_values     LEFT JOIN Test         ON DATEADD(DAY, number, CONVERT(DATETIME, '2021-01-01')) = [DataTime] WHERE type = 'P'       AND number       BETWEEN 0 AND DATEDIFF(DAY, '2021-01-01', DATEADD(MONTH, 1, '2021-01-01'))-1;

結果為：

(完整的有31條記錄，這里只截取前幾條)

到此，相信大家對“怎么用Spt_Values解決SQL中的連續日期問題”有了更深的了解，不妨來實際操作一番吧！這里是億速云網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！