T-SQL——数字辅助表
目录
shanzm-2023年2月20日
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 行受影响)
- 取两个字符串中重复的字符
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 行受影响)
