按数量列出行

2021-09-10 00:00:00 sql tsql sql-server

我可能想多了.我有一个带有 Name 和 Ticket Quantity 列的简单表格.我想逐行输出每个购买数量的名称列表.请参阅下面的示例.

I'm probably over thinking this. I have a simple table with Name and Ticket Quantity columns. I want to output a row by row list of the names for each quantity purchased. See example below.

表格:

Name           Quantity
-----------------------
Bob            1
Joe            2
Sally          1

输出:

Bob
Joe
Joe
Sally

我如何在 TSQL 中实现这一点?

How could I achieve this in TSQL?

推荐答案

SETUP:

DECLARE @table TABLE (
    NAME VARCHAR(10),
    Quantity INT
    )

INSERT INTO @table
SELECT 'Bob',   1 UNION ALL
SELECT 'Joe',   2 UNION ALL
SELECT 'Sally', 1

递归 CTE

;WITH Members (
    NAME,
    Quantity
    )
AS (
    -- Base case
    SELECT NAME,
        Quantity
    FROM @table

    UNION ALL

    -- Recursive
    SELECT NAME,
        Members.Quantity - 1
    FROM Members
    WHERE Members.Quantity > 1
    )
SELECT NAME
FROM Members
OPTION (MAXRECURSION 0)
ORDER BY 1

结果:

Bob
Joe
Joe
Sally

<小时>

或者你可以(根据@Martin Smith 的建议):

Alternatively you could (per @Martin Smith's suggestion):

DECLARE @numbers TABLE (number INT)

INSERT INTO @numbers (number)
VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)

最后:

SELECT NAME
FROM @table t
INNER JOIN @numbers n ON n.number <= t.Quantity
ORDER BY 1

结果:

Bob
Joe
Joe
Sally

<小时>如果你真的喜欢递归 CTE(因为它们闻起来很香)，你可以用递归 CTE 构建你的数字表.您应该使用物理表，而不是您在此处看到的变量表 - 这样您就不必每次都构建它们.

And if you really like recursive CTE's (because they smell good), you could build your numbers table with a recursive CTE. You should be using physical tables and not variable tables as you see here - so that you don't have to build them every time.

;WITH Numbers (Value)
AS (
    -- Base case
    SELECT 32767 Value

    UNION ALL

    -- Recursive
    SELECT Numbers.Value - 1
    FROM Numbers
    WHERE Numbers.Value > 1
    )
INSERT INTO @numbers (number)
SELECT Value
FROM Numbers
OPTION (MAXRECURSION 32767)

相关文章