Oracle - 根据列值将单行拆分为多行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41589163/
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
Oracle - split single row into multiple rows based on column value
提问by SDR
I have a table like this
我有一张这样的桌子
parent_item child_item quantity
A B 2
A C 3
B E 1
B F 2
And would like to split this in multiple lines based on the quantity
并想根据数量将其拆分为多行
parent_item child_item quantity
A B 1
A B 1
A C 1
A C 1
A C 1
B E 1
B F 1
B F 1
The column quantity (1) is not really necessary.
I was able to generate something with the help of connect by / level, but for large tables it's very very slow.I'm not really familiar with connect by / level, but this seemed to work, although I can't really explain:
我能够在 / 级连接的帮助下生成一些东西，但是对于大表，它非常非常慢。我对 / 级连接不是很熟悉，但这似乎有效，尽管我无法真正解释：
select distinct parent_item, level LEVEL_TAG, child_item, level||quantity
FROM table
CONNECT BY quantity>=level
order by 1 asc;
I found similar questions, but in most cases topicstarter want's to split a delimited column value in multiple lines (Oracle - split single row into multiple rows)
我发现了类似的问题，但在大多数情况下，topicstarter 希望将分隔的列值拆分为多行（Oracle - 将单行拆分为多行）
What's the most performant method to solve this?
解决这个问题的最有效方法是什么？
Thanks
谢谢
回答by MT0
Use a recursive sub-query factoring clause:
使用递归子查询分解子句：
WITH split ( parent_item, child_item, lvl, quantity ) AS (
SELECT parent_item, child_item, 1, quantity
FROM your_table
UNION ALL
SELECT parent_item, child_item, lvl + 1, quantity
FROM split
WHERE lvl < quantity
)
SELECT parent_item, child_item, 1 As quantity
FROM split;
Or you can use a correlated hierarchical query:
或者您可以使用相关的分层查询：
SELECT t.parent_item, t.child_item, 1 AS quantity
FROM your_table t,
TABLE(
CAST(
MULTISET(
SELECT LEVEL
FROM DUAL
CONNECT BY LEVEL <= t.quantity
)
AS SYS.ODCINUMBERLIST
)
) l;
As for which is more performant - try benchmarking the different solutions as we cannot tell you what will be more performant on your system.
至于哪个性能更好 - 尝试对不同的解决方案进行基准测试，因为我们无法告诉您什么在您的系统上性能更高。
