根据数量字段返回多个结果

Question

我有以下记录表：

Product     |    Colour  |   Quantity
---------------------------------------
Product A   |    Blue    |          1
Product A   |    Red     |          2
Product B   |    Green   |          1

如何编写查询以返回以下内容？：

Product     |    Colour
---------------------------
Product A   |    Blue
Product A   |    Red
Product A   |    Red
Product B   |    Green

Answer 1

WITH    numbers (rn) AS
        (
        SELECT  MAX(quantity)
        FROM    product
        UNION ALL
        SELECT  rn - 1
        FROM    numbers
        WHERE   rn > 1
        )
SELECT  p.*
FROM    product p
JOIN    numbers n
ON      p.quantity >= n.rn