選擇重複數字 x 次，然後遞增

我需要一個選擇查詢，它可以生成z從 1 開始的行數，重複x次數，然後遞增到下一個數字。

例如，重複 4 次的 10 行將導致以下結果：

| # |  
|---|  
| 1 |  
| 1 |  
| 1 |  
| 1 |  
| 2 |  
| 2 |  
| 2 |  
| 2 |  
| 3 |  
| 3 |

所以在給出的例子中 z=10 和 x=4。這些是儲存過程的參數。沒有限製或限制。如果使用者需要 20 億（不太可能），使用者可以輸入 20 億。

DECLARE @z INT=10, @x INT=4;
WITH q AS (SELECT 0 AS n UNION ALL SELECT n+1 FROM q)
SELECT TOP(@z) n/@x+1 FROM q OPTION(MAXRECURSION 0);

一種在數字表上使用簡單整數算術的解決方案。這個例子使用一種有效的方式來動態生成一個序列，實際上你可能會有一個永久的 Numbers 表。

DECLARE
   @z bigint = 10,
   @x integer = 4;

-- Itzik Ben-Gan's row generator
WITH
 L0   AS (SELECT 1 AS c UNION ALL SELECT 1),
 L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
 L2   AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
 L3   AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
 L4   AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
 L5   AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
 Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) AS n FROM L5)
-- Main logic
SELECT TOP (@z)
   # = ((N.n - 1) / @x) + 1
FROM Nums AS N
ORDER BY N.n;

輸出：

╔═══╗
║ # ║
╠═══╣
║ 1 ║
║ 1 ║
║ 1 ║
║ 1 ║
║ 2 ║
║ 2 ║
║ 2 ║
║ 2 ║
║ 3 ║
║ 3 ║
╚═══╝

執行計劃：

引用自：https://dba.stackexchange.com/questions/216227