问题描述
我可能想多了.我有一个带有 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)