oracle 根据每行中的列值重复行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11201278/
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
Repeating rows based on column value in each row
提问by user991255
I've this table with the following data
我有这个表与以下数据
Job ?Quantity Status Repeat?
1 ? ?100 ? ? ?OK ? ? 2?
2 ? ?400 ? ? ?HOLD ? 0?
3 ? ?200 ? ? ?HOLD ? 1?
4 ? ?450 ? ? ?OK ? ? 3?
Based on the value in the Repeat column for each row, the row should be repeated again. For example for the Job 1, Repeat value is 2 so the Job 1 should repeat two more times.
根据每一行的重复列中的值，应再次重复该行。例如，对于作业 1，重复值为 2，因此作业 1 应再重复两次。
The resultant table should be as below
结果表应如下所示
Job ? ?Quantity Status Repeat?
1 ? ? ?100 ? ? ? OK ? ? 2?
1 ? ? ?100 ? ? ? OK ? ? 2?
1 ? ? ?100 ? ? ? OK ? ? 2?
2 ? ? ?400 ? ? ? HOLD ? 0?
3 ? ? ?200 ? ? ? HOLD ? 1?
3 ? ? ?200 ? ? ? HOLD ? 1?
4 ? ? ?450 ? ? ? OK ? ? 3?
4 ? ? ?450 ? ? ? OK ? ? 3?
4 ? ? ?450 ? ? ? OK ? ? 3?
4 ? ? ?450 ? ? ? OK ? ? 3?
Can someone please help me out with this query? am using oracle 10g
有人可以帮我解决这个查询吗？我正在使用 oracle 10g
回答by Andomar
You could use a recursive CTE:
您可以使用递归 CTE：
with cte(Job, Repeat, i) as
(
select Job
, Repeat
, 0
from YourTable
union all
select Job
, Repeat
, i + 1
from cte
where cte.i < cte.Repeat
)
select *
from cte
order by
Job
, i
回答by Florin Ghita
Supposing you won't generate more than 1000 rows per row:
假设您不会每行生成超过 1000 行：
with num as (select level as rnk from dual connect by level<=1000)
select Job, Quantity, Status, Repeat, rnk
from t join num on ( num.rnk <= repeat )
order by job, rnk;
Here is a test: http://sqlfiddle.com/#!4/4519f/12
这是一个测试：http: //sqlfiddle.com/#!4/4519f/12
UPDATE:As Jeffrey Kemp said, you can "detect" the maximum with a subquery:
更新：正如 Jeffrey Kemp 所说，您可以使用子查询“检测”最大值：
with num as (select level as rnk
from dual
connect by level<=(select max(repeat) from t)
)
select job, quantity, status, repeat, rnk
from t join num on ( num.rnk <= repeat )
order by job, rnk;
回答by Sumant
Instead of doing this manipulation with query you can get this data first in a data table and add rows to a new data table based on the value of repeat and then bind this new data table.
您可以先在数据表中获取此数据，然后根据重复的值将行添加到新数据表中，然后绑定此新数据表，而不是使用查询进行此操作。
