如何编写SQL Server查询，按表中列指定返回行数？

方法一：使用递归CTE #

递归CTE是实现这类行重复需求的常用方案，通过递归逻辑生成对应数量的行：

WITH RecursiveCTE AS (
    SELECT InvoiceID, CompanyName, RowsNeeded, 1 AS CurrentRow
    FROM dbo.Table1
    UNION ALL
    SELECT InvoiceID, CompanyName, RowsNeeded, CurrentRow + 1
    FROM RecursiveCTE
    WHERE CurrentRow < RowsNeeded
)
SELECT InvoiceID, CompanyName
FROM RecursiveCTE
ORDER BY InvoiceID;

方法二：使用数字辅助表 #

通过生成连续数字的辅助表，与原表关联来生成重复行，适合RowsNeeded值较大的场景：

-- 生成1到100的连续数字（可根据实际需求调整上限）
WITH Numbers AS (
    SELECT TOP (100) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Num
    FROM master..spt_values
)
SELECT t.InvoiceID, t.CompanyName
FROM dbo.Table1 t
INNER JOIN Numbers n 
    ON n.Num <= t.RowsNeeded
ORDER BY t.InvoiceID;

结果验证 #

以上两种方法执行后，都会返回预期的结果集：

'A1000','Company 1'
'A1000','Company 1'
'A1000','Company 1'
'A1001','Company 2'
'A1001','Company 2'

内容的提问来源于stack exchange，提问作者Donald Schroeder