T-SQL——數字輔助表

shanzm發表於2023-02-21
SQL

目錄

志銘-2023年2月20日 22:50:32

0.永久性的連續數字表

使用迴圈可以快速建立一個Nums真實的表Nums

IF OBJECT_ID('dbo.Nums') IS NOT NULL 
DROP TABLE dbo.Nums;

CREATE TABLE dbo.Nums (n INT NOT NULL PRIMARY KEY);

DECLARE @i INT = 1;
WHILE @i < 100
BEGIN
    INSERT INTO dbo.Nums (n) VALUES (@i);
    SET @i = @i + 1;
END;
SELECT * FROM dbo.Nums;

其實還有其他一些產生日誌比較小，速度更快的方式建立連續數字表，但是沒有必要，因為建立一個真實表，這個sql操作只需要執行一次。


1.使用系統表：master..spt_values

使用master..spt_values表中的number欄位，可快速獲取0-2047之間的連續數字

SELECT number FROM  master..spt_values WHERE	 type='p'

2.使用遞迴CTE

使用遞迴的方式建立數字

該方法相對較慢，但是SQL語句簡潔明瞭

DECLARE @n AS BIGINT;
SET @n=1000000;
WITH Nums AS 
(
SELECT 1 AS n 
UNION ALL 
SELECT n+1 FROM Nums WHERE n<@n
)
SELECT * FROM Nums OPTION(MAXRECURSION 0)--預設遞迴次數為100，這裡設定取消遞迴次數限制

3.使用0-9乘以量級交叉連線

首先使用VALUES構造一個0-9的虛擬表
VALUES(0),(1), (2), (3), (4), (5), (6), (7), (8), (9)
個位數、十位數、百位數交叉連結構成1-1000
若是需要更多連續數字，則按照相同邏輯進行更多次的交叉連線

這裡我使用表變數進行示例：

DECLARE @Nums TABLE(n INT);
INSERT INTO @Nums
SELECT * FROM(VALUES(0),(1), (2), (3), (4), (5), (6), (7), (8), (9)) AS T1(n);
SELECT T1.n+1+T2.n*10+T3.n*100 AS n FROM @Nums AS T1 CROSS JOIN @Nums AS T2 CROSS JOIN @Nums AS T3
ORDER BY n

4.使用2的次冪和CTE生成和交叉連結 建立表值函式

透過交叉連線生成大量的記錄，然後取Row_Number（注意這裡是使用Row_Number來獲取連續的數字）

這裡的原理就是((((2^2)^2)^2)^2)^2=4294967296，這個數字已經足夠大足夠我們使用了

這裡我們建立一個表值函式GetNums
注1：這裡sql server不是先生成4294967296行資料 ，在篩選出我們需要的。而是根據我們的最大引數生成記錄，所以這裡沒有效能上的問題
注2：這個SQL函式來源於：《Microsoft SQL Server 2008技術內幕：T-SQL查詢:6.4數字輔助表》


IF OBJECT_ID('dbo.GetNums') IS NOT NULL DROP FUNCTION dbo.GetNums;
GO
CREATE FUNCTION dbo.GetNums(@startNum AS BIGINT, @endNum AS BIGINT)
RETURNS TABLE
AS
RETURN 
WITH 
L0 AS (SELECT c FROM(VALUES(1), (1)) AS D(c) ), 
L1 AS (SELECT 1 AS c FROM L0 CROSS JOIN L0 AS B),
L2 AS (SELECT 1 AS c FROM L1 CROSS JOIN L1 AS B), 
L3 AS (SELECT 1 AS c FROM L2 CROSS JOIN L2 AS B), 
L4 AS (SELECT 1 AS c FROM L3 CROSS JOIN L3 AS B), 
L5 AS (SELECT 1 AS c FROM L4 CROSS JOIN L4 AS B), 
Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY(SELECT NULL)) AS rownum FROM L5)
SELECT @startNum+rownum-1 AS n
FROM Nums
ORDER BY rownum OFFSET 0 ROWS FETCH FIRST @endNum-@startNum+1 ROWS ONLY;


--測試返回1到100
SELECT * FROM  dbo.GetNums(1,100)

5.數字輔助表使用情形

數字輔助表常常用於獲取連續時間點

  • 獲取某一天的24個小時
SELECT DATEADD(HH, number, '2023-02-20 00:00') AS OneDay
FROM master..spt_values
WHERE type='P' AND DATEDIFF(HH, DATEADD(HH, number, '2023-02-20 00:00'), '2023-02-20 23:00')>=0;

--結果
OneDay
-----------------------
2023-02-20 00:00:00.000
2023-02-20 01:00:00.000
2023-02-20 02:00:00.000
2023-02-20 03:00:00.000
……
2023-02-20 20:00:00.000
2023-02-20 21:00:00.000
2023-02-20 22:00:00.000
2023-02-20 23:00:00.000

(24 行受影響)
  • 獲取1994年1月1號到今天的每月的資料列：
SELECT CONVERT(VARCHAR(7), DATEADD(MONTH, number, '1994-01-01'), 23) AS MonthNo
FROM master..spt_values
WHERE type='p' AND number<=DATEDIFF(MONTH, '1994-01-01', GETDATE()); --小於指定日期到當前的所有月份

--結果：
MonthNo
-----------------
1994-01
1994-02
1994-03
1994-04
……
2022-12
2023-01
2023-02

(349 行受影響)
  • 獲取2022年1月1日對今天的每天的資料列
SELECT CONVERT(VARCHAR(100), DATEADD(DAY, number, '2022-01-01'), 23) AS DayNo
FROM master..spt_values
WHERE type='p' AND number<=DATEDIFF(DAY, '2022-01-01', GETDATE());

--結果
DayNo
------------------
2022-01-01
2022-01-02
2022-01-03
2022-01-04
2022-01-05
……
2023-02-15
2023-02-16
2023-02-17
2023-02-18
2023-02-19
2023-02-20

(416 行受影響)
  1. 取兩個字串中重複的字元
DECLARE @text1 VARCHAR(100) ='十年我們,十年前我們在一起';
DECLARE @text2 VARCHAR(100) ='十年他們,十年後我們又重聚在一起';
SELECT SUBSTRING(@text2, number, 1) AS value
FROM master..spt_values
WHERE type='p' AND number<=LEN(@text2)AND CHARINDEX(SUBSTRING(@text2, number, 1), @text1)>0;

--結果
value
-----
十
年
們
,
十
年
我
們
在
一
起

(11 行受影響)

6.參考

相關文章