oracle 如何根据行本身的数量字段返回多个相同的行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3664903/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I return multiple identical rows based on a quantity field in the row itself?
提问by user126715
I'm using oracle to output line items in from a shopping app. Each item has a quantity field that may be greater than 1 and if it is, I'd like to return that row N times.
我正在使用 oracle 从购物应用程序中输出订单项。每个项目都有一个可能大于 1 的数量字段，如果是，我想返回该行 N 次。
Here's what I'm talking about for a table
这就是我在谈论一张桌子
product_id, quanity
1, 3,
2, 5
And I'm looking a query that would return
我正在寻找一个会返回的查询
1,3
1,3
1,3
2,5
2,5
2,5
2,5
2,5
Is this possible? I saw thisanswer for SQL Server 2005 and I'm looking for almost the exact thing in oracle. Building a dedicated numbers table is unfortunately not an option.
这可能吗？我在SQL Server 2005 中看到了这个答案，我正在寻找几乎与 oracle 完全相同的内容。不幸的是，构建专用的数字表不是一种选择。
回答by Gary Myers
I've used 15 as a maximum for the example, but you should set it to 9999 or whatever the maximum quantity you will support.
我在示例中使用了 15 作为最大值，但您应该将其设置为 9999 或任何您将支持的最大数量。
create table t (product_id number, quantity number);
insert into t values (1,3);
insert into t values (2,5);
select t.*
from t
join (select rownum rn from dual connect by level < 15) a
on a.rn <= t.quantity
order by 1;
回答by Martin Mares
First create sample data:
首先创建示例数据：
create table my_table (product_id number , quantity number);
insert into my_table(product_id, quantity) values(1,3);
insert into my_table(product_id, quantity) values(2,5);
And now run this SQL:
现在运行这个 SQL：
SELECT product_id, quantity
FROM my_table tproducts
,( SELECT LEVEL AS lvl
FROM dual
CONNECT BY LEVEL <= (SELECT MAX(quantity) FROM my_table)) tbl_sub
WHERE tbl_sub.lvl BETWEEN 1 AND tproducts.quantity
ORDER BY product_id, lvl;
PRODUCT_ID QUANTITY
---------- ----------
1 3
1 3
1 3
2 5
2 5
2 5
2 5
2 5
This question is propably same as this: how to calc ranges in oracle
这个问题可能与此相同：如何在 oracle 中计算范围
Update solution, for Oracle 9i:
更新解决方案，适用于 Oracle 9i：
You can use pipelined_function() like this:
您可以像这样使用 pipelined_function()：
CREATE TYPE SampleType AS OBJECT
(
product_id number,
quantity varchar2(2000)
)
/
CREATE TYPE SampleTypeSet AS TABLE OF SampleType
/
CREATE OR REPLACE FUNCTION GET_DATA RETURN SampleTypeSet
PIPELINED
IS
l_one_row SampleType := SampleType(NULL, NULL);
BEGIN
FOR cur_data IN (SELECT product_id, quantity FROM my_table ORDER BY product_id) LOOP
FOR i IN 1..cur_data.quantity LOOP
l_one_row.product_id := cur_data.product_id;
l_one_row.quantity := cur_data.quantity;
PIPE ROW(l_one_row);
END LOOP;
END LOOP;
RETURN;
END GET_DATA;
/
Now you can do this:
现在你可以这样做：
SELECT * FROM TABLE(GET_DATA());
Or this:
或这个：
CREATE OR REPLACE VIEW VIEW_ALL_DATA AS SELECT * FROM TABLE(GET_DATA());
SELECT * FROM VIEW_ALL_DATA;
Both with same results.
两者结果相同。
(Based on my article pipelined function)
（基于我的文章流水线函数）
