純 SQL 繪製楊輝三角：遞歸 CTE 與動態 SQL 的極客實現

靈感來源

在 CSDN 見到有人用 SQL 畫楊輝三角，覺得頗具趣味。決定不借助臨時表，直接用組合數公式 C(n,m) = n! / [m!(n-m)!] 配合遞歸 CTE 與動態 SQL 純算出每一行每一列的值並對齊輸出。

核心思路

1. 用 sys.all_columns 的 ROW_NUMBER() 生成連續自然數序列（替代遞歸 CTE 生成數字表）。
2. 利用 CROSS APPLY + FOR XML PATH('') 動態拼接每行的 SELECT 列表，按組合數公式計算每個位置的值。
3. 用 STUFF 去除首字符，QUOTENAME 自動加方括號別名，確保列名合法。
4. 最後 UNION ALL 合併所有行，EXEC(@sql) 一次性輸出結果集。

完整腳本（SQL Server 2012+ 測試通過）

USE tempdb;
GO
SET NOCOUNT ON;

DECLARE @rows INT = 10;          -- 目標行數，可自行調整
DECLARE @cols INT = @rows * 2 - 1; -- 顯示寬度：第 n 行佔 2n-1 列，居中對齊
DECLARE @sql NVARCHAR(MAX) = '';

;WITH cte_n AS (
    -- 生成 1 ~ 2*@rows 的自然數
    SELECT r FROM (
        SELECT ROW_NUMBER() OVER (ORDER BY a.object_id) AS r
        FROM sys.all_columns a
    ) x WHERE r <= @rows * 2
),
cte_1 AS (
    SELECT n.r, b.data_lse
    FROM cte_n n
    CROSS APPLY (
        SELECT 'SELECT ' + STUFF((
            SELECT ',RTRIM(' + ISNULL(F1.v + '/((' + F2.v + ')*' + F3.v + ')', '''''') + ') AS ' +
                   QUOTENAME(ISNULL(NULLIF((m.r + (@rows - n.r) + (m.r - 1) * 1) % @cols, 0), @cols))
            FROM cte_n m
            OUTER APPLY (
                SELECT STUFF((SELECT '*' + RTRIM(i.r) FROM cte_n i WHERE i.r <= ISNULL(NULLIF(n.r - 1, 0), 1) FOR XML PATH('')), 1, 1, '') AS v
            ) F1
            OUTER APPLY (
                SELECT STUFF((SELECT '*' + RTRIM(i.r) FROM cte_n i WHERE i.r <= ISNULL(NULLIF(m.r - 1, 0), 1) FOR XML PATH('')), 1, 1, '') AS v
            ) F2
            OUTER APPLY (
                SELECT STUFF((SELECT '*' + RTRIM(i.r) FROM cte_n i WHERE i.r <= ISNULL(NULLIF(n.r - m.r, 0), 1) FOR XML PATH('')), 1, 1, '') AS v
            ) F3
            WHERE m.r < @rows * 2
            ORDER BY ISNULL(NULLIF((m.r + (@rows - n.r) + (m.r - 1) * 1) % @cols, 0), @cols) ASC
            FOR XML PATH('')
        ), 1, 1, '') AS data_lse
    ) b
    WHERE n.r <= @rows
)
SELECT @sql = ISNULL(@sql + ' UNION ALL ', '') + data_lse FROM cte_1;

EXEC(@sql);

執行效果

已知限制與優化方向

• 階乘溢出：@rows > 13 時 n! 超過 BIGINT 範圍導致算術溢出。
• 優化思路：
  • 用 DECIMAL(38,0) 或 FLOAT 延長可計算行數；
  • 改用遞歸關係 C(n,m) = C(n-1,m-1) + C(n-1,m) 避開階乘，配合遞歸 CTE 可支援百行以上；
  • 或引入 CLR 聚合函數處理大整數運算。

結語

這個腳本展示了 SQL Server 在集合運算、動態 SQL、XML 序列化等高級特性上的表現力。雖然生產環境鮮用，但作為 T-SQL 技巧練翰與面試話題極佳。有興趣可嘗試用遞歸 CTE 實現「無階乘版」楊輝三角，突破行數限制。