Itzik Ben Gan 的數列產生器

用這個技巧完成以下需求

這邊有一個SQL的問題,不知道你是否可以幫忙解決,

我這有一個Table如下表示
ColumnA   ColumnB
A1              3
A2              2
A3              4

我相望可以Select出
A1   1
A1   2
A1   3
A2   1
A2   2
A3   1
A3   2
A3   3
A3   4

這樣的資料

我的解法

CREATE FUNCTION fnGets(@top BIGINT) RETURNS @t TABLE(SeqNo BIGINT) AS BEGIN  WITH Nbrs_3( n ) AS ( SELECT 3 UNION ALL SELECT 2 UNION ALL SELECT 1 UNION ALL SELECT 0 ),  Nbrs_2( n ) AS ( SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 ),  Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ),  Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ),  Nbrs ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 )  INSERT @t SELECT k  FROM ( SELECT ROW_NUMBER() OVER (ORDER BY n)  FROM Nbrs ) D ( k )  WHERE k <= @top ;  RETURN END go

with ddt(a, b) as ( select * from (values (‘a’, 2),(‘b’,32768),(‘c’,1234)) ddt (a, b) ) select a,f.*  from ddt cross apply fnGets(b) f ORDER by 1,2

果然有趣,稍微修改如下:

CREATE PROC spGets(@top BIGINT)
AS
WITH Nbrs_3( n ) AS ( SELECT 3 UNION ALL SELECT 2 UNION ALL SELECT 1 UNION ALL SELECT 0 ),
Nbrs_2( n ) AS ( SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 ), –4^2
Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ), –4^2^2
Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ), –4^2^2^2
Nbrs ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 )   –4^16 = 4294967296
SELECT k
FROM ( SELECT ROW_NUMBER() OVER (ORDER BY n)
FROM Nbrs ) D ( k )
WHERE k <= @top ;
GO
exec spGets 100

–4 顆 CPU 幾乎滿載
WITH Nbrs_3( n ) AS ( SELECT 3 UNION ALL SELECT 2 UNION ALL SELECT 1 UNION ALL SELECT 0 ),
Nbrs_2( n ) AS ( SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 ), –4^2
Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ), –4^2^2
Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ), –4^2^2^2
Nbrs ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 )   –4^16
SELECT COUNT_BIG(*) FROM Nbrs

小心,若擴大 scale,極耗資源

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 變更 )

Twitter picture

You are commenting using your Twitter account. Log Out / 變更 )

Facebook照片

You are commenting using your Facebook account. Log Out / 變更 )

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s

%d 位部落客按了讚: