如何基于行本身中的数量字段返回多个相同的行? [英] How can I return multiple identical rows based on a quantity field in the row itself?
问题描述
我正在使用oracle从购物应用中输出订单项.每个项目的数量字段都可能大于1,如果是,我想将该行返回N次.
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.
这就是我要谈论的桌子
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
这可能吗?我看到了SQL Server 2005的此答案,我正在寻找在oracle中几乎是完全一样的东西.不幸的是,建立专用号码表不是一种选择.
Is this possible? I saw this answer 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.
推荐答案
在此示例中,我最多使用15,但是您应该将其设置为9999或将支持的最大数量.
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.
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;
这篇关于如何基于行本身中的数量字段返回多个相同的行?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!