根据每行中的列值重复行 [英] Repeating rows based on column value in each row

问题描述

我将这张表包含以下数据

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 

基于每一行的重复"列中的值，应再次重复该行.例如，对于作业1"，重复"值为2，因此作业1"应重复两次.

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.

结果表应如下

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 

有人可以帮我解决这个问题吗? 正在使用oracle 10g

Can someone please help me out with this query? am using oracle 10g

推荐答案

假设每行生成的行数不超过1000:

Supposing you won't generate more than 1000 rows per row:

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;

这是一个测试: http://sqlfiddle.com/#!4/4519f/12

更新:正如Jeffrey Kemp所说，您可以通过子查询检测"最大值:

UPDATE: As Jeffrey Kemp said, you can "detect" the maximum with a subquery:

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;

这篇关于根据每行中的列值重复行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！