mysql - 为每个 'quantity' 选择一行

Question

表:

Article | Quantity | pricePerUnit | order_id | article_id
--------|----------|--------------------------------------
     14 |        2 |        10.0  |        1 |    1
     X1 |        1 |         5.0  |        1 |    2

预期输出:

Article | Quantity | pricePerUnit | order_id
--------|----------|------------------------
     14 |        1 |        10.0  |     1
     14 |        1 |        10.0  |     1
     X1 |        1 |         5.0  |     1

什么是快速 SELECT 来为每篇文章的每个数量填充 1 行结果集？

抱歉，我没有尝试任何事情，我不确定这是否可行。自加入..不会有帮助，分组功能，..

也许按 order_id、article_id、数量分组 不知何故..

Answer 1

更新:对于三个的最大数量，只需这样做

SELECT Article, 1 Quantity, pricePerUnit, order_id
  FROM articles a JOIN 
(
   SELECT 1 AS n UNION ALL 
   SELECT 2 UNION ALL 
   SELECT 3
) n
    ON n.n <= a.Quantity
 ORDER BY order_id, Article

这里是SQLFiddle 演示

<小时/>

原答案:你可以尝试

SELECT Article, 1 Quantity, pricePerUnit, order_id
  FROM articles a JOIN 
(
   SELECT a.N + b.N * 10 + 1 n
     FROM 
    (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
   ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
) n
    ON n.n <= a.Quantity
 ORDER BY order_id, Article

内部选择返回 100 行，这意味着您可以将数量最多取消为 100。如果您需要更多，请相应地进行更新。

这里是SQLFiddle 演示

<小时/>

鉴于它是用于报告的，并且您有创建新表的必要权利，因此最好用可以以相同方式创建的计数(数字)表替换内部选择:

CREATE TABLE tally (n int not null auto_increment primary key);
INSERT INTO tally
SELECT a.N + b.N * 10 + 1 n
  FROM 
(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
ORDER BY n;

然后你的查询将如下所示

SELECT Article, 1 Quantity, pricePerUnit, order_id
  FROM articles a JOIN tally n
    ON n.n <= a.Quantity
 ORDER BY order_id, Article

这里是SQLFiddle 演示