按数量列出行
我可能想多了.我有一个带有 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)
相关文章