快捷導(dǎo)航

SqlServer 2005 T-SQL Query 學(xué)習(xí)筆記（4）

更新時間：2010年02月19日 15:00:53 作者：

作者他很喜歡建立數(shù)字輔助表（即是1-N的數(shù)字按順序組成的表），關(guān)于如何建立這些輔助表，然后他給了一些例子，這些例子很有代表性。

比如，我要建立一個1，000，000行的數(shù)字表：

CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);
DECLARE @max AS INT, @rc AS INT;
SET @max = 1000000;
SET @rc = 1;

INSERT INTO Nums VALUES(1);
WHILE @rc * 2 <= @max
BEGIN
INSERT INTO dbo.Nums SELECT n + @rc FROM dbo.Nums;
SET @rc = @rc * 2;
END

INSERT INTO dbo.Nums
SELECT n + @rc FROM dbo.Nums WHERE n + @rc <= @max;

這種方式非常巧妙，它并不是一個一個的循環(huán)插入，而是一次插入很多行，{1}，{2}，{3，4}，{5，6，7，8}。。。

為什么這樣會快呢？

是因為它節(jié)省了跟比較其他可用解決方案進(jìn)行比較和記錄這些日志的時間。

然后，作者給了一個CTE的遞歸的解決方案:

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 n FROM Nums
OPTION(MAXRECURSION 0);--為了移除默認(rèn)100的遞歸限制

有個更優(yōu)的CTE的解決方案，就是先生成很多行，然后用ROW_NUMBER進(jìn)行計算，再選擇ROW_NUMBER這列的值就可以了。

復(fù)制代碼代碼如下:

DECLARE @n AS BIGINT; 
SET @n = 1000000; 

WITH Base AS 
( 
SELECT 1 AS n 
UNION ALL 
SELECT n + 1 FROM Base WHERE n < CEILING(SQRT(@n)) 
), 
Expand AS 
( 
SELECT 1 AS c 
FROM Base AS B1, Base AS B2 
), 
Nums AS 
( 
SELECT ROW_NUMBER() OVER(ORDER BY c) AS n 
FROM Expand 
) 
SELECT n FROM Nums WHERE n <= @n 
OPTION(MAXRECURSION 0); 

利用笛卡爾積進(jìn)行不斷的累加，達(dá)到了22n行。

最后，作者給出了一個函數(shù)，用于生成這樣的數(shù)字表：

復(fù)制代碼代碼如下:

CREATE FUNCTION dbo.fn_nums(@n AS BIGINT) RETURNS TABLE 
AS 
RETURN 
WITH 
L0 AS(SELECT 1 AS c UNION ALL SELECT 1), 
L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B), 
L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B), 
L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B), 
L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B), 
L5 AS(SELECT 1 AS c FROM L4 AS A, L4 AS B), 
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5) 
SELECT n FROM Nums WHERE n <= @n; 
GO 

您可能感興趣的文章: