T-SQL——数字辅助表

技术分享 3年前 (2023-02-22) 0 999+

志铭-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 行受影响)

取两个字符串中重复的字符

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 行受影响)