T-SQL根据字段值返回多行

Question

我正在尝试在只允许使用T-SQL的系统上运行导出操作。我知道足够的PHP知识来制作一个foreach循环，但我不知道足够的T-SQL知识来为给定数量生成多个行。我需要一个结果来生成包含“1 of 4”这样的数据的项目列表。

例如，给定一个表：

orderid, product, quantity
1000,ball,3
1001,bike,4
1002,hat,2

如何获得类似以下选择查询结果的内容：

订单号，物品编号，总共物品数量，产品名称

1000，1，3，球 1000，2，3，球 1000，3，3，球 1001，1，4，自行车 1001，2，4，自行车 1001，3，4，自行车 1001，4，4，自行车 1002，1，2，帽子 1002，2，2，帽子

- noob

Answer 1

你可以借助辅助数字表来完成这个操作。

;WITH T(orderid, product, quantity) AS
(
select 1000,'ball',3 union all
select 1001,'bike',4 union all
select 1002,'hat',2
)

SELECT orderid, number as item_num, quantity as total_items, product
FROM T
JOIN master..spt_values on number> 0 and number <= quantity
where type='P'

注意：上面的代码使用了 master..spt_values 表 - 这只是为了演示目的，建议您使用这里的技术之一创建自己的计数表。

- Martin Smith

---

@Thomas - 是的。每个辅助数字表都会有一些限制。 @noob - 这只是为了演示目的，我建议你使用这里的技术之一创建自己的计数表https://dev59.com/anVD5IYBdhLWcg3wWKLc#2663232 - Martin Smith

+1，但为什么不是“1到数量之间的数字”？我的意思是，我想知道这是否有任何区别。 - Andriy M

@Andriy - 我已经考虑过进行那个编辑。我同意它更整洁，但希望原帖作者会使用永久记分表从 1..n，这样他们可以在实际查询中完全省去 number> 0 检查。 - Martin Smith

谢谢大家。我刚学了一点SQL，这个解决方案完美地解决了我的问题。 - noob

Answer 2

如果你使用的是SQL Server 2005或更高版本，那么你可以尝试使用递归CTE代替计数表。

;WITH CTE AS
(
    SELECT orderid, 1 item_num, product, quantity
    FROM YourTable
    UNION ALL
    SELECT orderid, item_num+1, product, quantity
    FROM CTE
    WHERE item_num < quantity
)
SELECT *
FROM CTE
OPTION (MAXRECURSION 0)

我没有使用数据库引擎的电脑来测试这个，所以请告诉我它的运行情况。

- Lamak

Answer 3

如果你知道任何产品的产品数量的最大值（并且它不是太大，比如说4），那么你可以：

• 创建一个名为Nums的辅助表，其中包含1个整数列n，行包含1,2,3,4

• 运行

   SELECT * from Your_table, Nums
   WHERE  Nums.n <= Your_table.quantity
  

- DVK

---

CROSS JOIN 语法会更好。 - ThomasMcLeod

没问题。在表列表中，用“CROSS JOIN”运算符替换“，”运算符。两者都可以生成笛卡尔积。 - ThomasMcLeod

@Thomas 有什么区别吗？ - DVK