怎么用Spt_Values解決SQL中的連續(xù)日期問(wèn)題

本篇內(nèi)容主要講解“怎么用Spt_Values解決SQL中的連續(xù)日期問(wèn)題”，感興趣的朋友不妨來(lái)看看。本文介紹的方法操作簡(jiǎn)單快捷，實(shí)用性強(qiáng)。下面就讓小編來(lái)帶大家學(xué)習(xí)“怎么用Spt_Values解決SQL中的連續(xù)日期問(wèn)題”吧!

spt_values是什么

spt_values是SQL Server新增的一個(gè)系統(tǒng)表，表里面都是一些枚舉數(shù)據(jù)。我們可以通過(guò)如下查詢語(yǔ)句來(lái)查看里面的數(shù)據(jù)

select * from master..spt_values

(因?yàn)樵摫韺儆谙到y(tǒng)數(shù)據(jù)庫(kù)master下面，所以通常在表名前面添加庫(kù)名master)

結(jié)果為：

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

spt_values連續(xù)記錄

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

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

結(jié)果為：

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

我們經(jīng)常使用的就是number列，通過(guò)該列我們可以生成很多連續(xù)的記錄，包括連續(xù)的日期，例如每天的24小時(shí)，每個(gè)月的每天，每年的12個(gè)月等等。

生成每天的24小時(shí)我們只需要指定開(kāi)始和結(jié)束時(shí)間，就可以生成該時(shí)間段的連續(xù)小時(shí)了，這里從0點(diǎn)到23點(diǎn)。

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

(提示：可以左右滑動(dòng)代碼)結(jié)果為：

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

生成每月的每天我們只需要指定開(kāi)始和結(jié)束日期，就可以生成該日期段的連續(xù)天了，這里從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')

結(jié)果為：

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

生成每年的每月我們只需要指定開(kāi)始和結(jié)束月份，就可以生成該月份段的連續(xù)月了，這里從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')

結(jié)果為：

spt_values應(yīng)用實(shí)例

有如下一張表Test

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

分析：我們數(shù)據(jù)庫(kù)中只存儲(chǔ)了4條數(shù)據(jù)，這時(shí)候我們可以利用SQL的表spt_values來(lái)實(shí)現(xiàn)。

解法：

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;

結(jié)果為：

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

到此，相信大家對(duì)“怎么用Spt_Values解決SQL中的連續(xù)日期問(wèn)題”有了更深的了解，不妨來(lái)實(shí)際操作一番吧！這里是億速云網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！