[[373719]]

本文轉載自微信公眾號「SQL數(shù)據庫」，作者丶平凡世界   。轉載本文請聯(lián)系開發(fā)公眾號。

spt_values是什么

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

select * from master..spt_values 

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

結果為：

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

spt_values連續(xù)記錄

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

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

結果為：

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

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

生成每天的24小時我們只需要指定開始和結束時間，就可以生成該時間段的連續(xù)小時了，這里從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條記錄，這里只截取前幾條)

生成每月的每天我們只需要指定開始和結束日期，就可以生成該日期段的連續(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') 

結果為：

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

生成每年的每月我們只需要指定開始和結束月份，就可以生成該月份段的連續(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') 

結果為：

spt_values應用實例

有如下一張表Test

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

分析：我們數(shù)據庫中只存儲了4條數(shù)據，這時候我們可以利用SQL的表spt_values來實現(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; 

結果為：

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

以上就是spt_values的一些用法，當然它不止在連續(xù)日期上的應用，只要是連續(xù)數(shù)字的問題，均可關聯(lián)spt_values來解決。