转载

快速生成基数的辅助表

想生一张26个字母的表,怎样实现。

可以查看到以前的方法《SQL循环26个字母插入到一个表中https://www.cnblogs.com/insus/archive/2010/12/23/1914346.html

如今可以使用CTE来快速生成:

;WITH alphabet AS
(
    SELECT 65 AS capital
    UNION ALL
    SELECT capital + 1 FROM alphabet WHERE capital < 90
)
SELECT CHAR(capital) FROM alphabet
Source Code

 

结果如下:

 

如想生成数字或其它呢?

以前的办法《使用CTE生成辅助表(数字或时间)等https://www.cnblogs.com/insus/p/10891965.html

现在解决方案:

 

;WITH Digits AS
(
    SELECT 0 AS digit
    UNION ALL
    SELECT digit + 1 FROM Digits where digit < 9
)
SELECT [digit] FROM Digits
Source Code

 

扩展,如果想生成包括10000之内的数字表呢?

 

 

;WITH Digits AS
(
    SELECT 0 AS digit
    UNION ALL
    SELECT digit + 1 FROM Digits where digit < 9
)

SELECT kilobit.[digit] * 1000 + hundreds.[digit] * 100 + decade.[digit] * 10 +  unit.[digit] + 1 FROM Digits AS unit
CROSS JOIN  Digits AS decade
CROSS JOIN Digits AS hundreds
CROSS JOIN Digits AS kilobit
Source Code

 

正文到此结束
本文目录