怎么产生重复记录？

Question

示例
ID NAME QTY
1  A    3
1  B    2
2  A    0
2  A    1如何通过一条SQL得出以下结果呢？
ID NAME QTY ROWNUM
1  A    3   1
1  A    3   2
1  A    3   3
1  B    2   1
1  B    2   2
2  A    1   1即根据QTY产生重复记录，产生的记录数　＝　QTY值，如果为０不产生记录，QTY数值大小不固定．

Answer 1

with a as
(select 1 ID, 'A' NAME,    3 QTY from dual
union
select 1 ID, 'B' NAME,    2 QTY from dual
union
select 2 ID, 'A' NAME,    0 QTY from dual
union
select 2 ID, 'A' NAME,    1 QTY from dual
)  select t.*,row_number() over(partition by QTY order by id)
  from  (
    select *  from a where QTY !=0
  ) t
  connect by  rownum<=QTY

Answer 2

  select t.*,row_number() over(partition by QTY order by id) 
  from  ( 
    select *  from a where QTY >0 
  ) t 
  connect by  rownum <=QTY

Answer 3

我现在是写成　　
SELECT ID, NAME,QTY,RN FROM TEST,
(SELECT ROWNUM RN FROM DUAL CONNECT BY ROWNUM<=(SELECT MAX(QTY) FROM TEST)) A
WHERE TEST.QTY<=A.RN这条SQL出来的结果是没有问题，但是当TEST表记录数较多，QTY大一点时，，会很慢很慢

Answer 4

        ID NAME        QTY         RN
---------- ---- ---------- ----------
         2 A             0          1
         2 A             1          1
         1 B             2          2
         2 A             0          2
         2 A             1          2
         1 A             3          3
         1 B             2          3
         2 A             0          3
         2 A             1          3你的sql语句出来的是这个结果，应该不是你想要的吧

Answer 5

-- 两种方法，10G下测试通过，试试看：
SQL> SELECT * FROM TEST_DD;        ID NAME        QTY
---------- ---- ----------
         1 A             3
         1 B             2
         2 A             0
         2 A             1SQL> SELECT TT.*, LEVEL
  2    FROM TEST_DD TT
  3   START WITH QTY <> 0
  4  CONNECT BY PRIOR QTY = QTY - 1
  5   ORDER BY 1, 2, 3, 4;        ID NAME        QTY      LEVEL
---------- ---- ---------- ----------
         1 A             3          1
         1 A             3          2
         1 A             3          3
         1 B             2          1
         1 B             2          2
         2 A             1          16 rows selectedSQL> SELECT *
  2    FROM TEST_DD T1,
  3         (SELECT ROWNUM RN
  4            FROM DUAL
  5          CONNECT BY ROWNUM < (SELECT SUM(QTY) SUM_QTY FROM TEST_DD)) T2
  6   WHERE QTY >= RN
  7   ORDER BY 1, 2, 3, 4;        ID NAME        QTY         RN
---------- ---- ---------- ----------
         1 A             3          1
         1 A             3          2
         1 A             3          3
         1 B             2          1
         1 B             2          2
         2 A             1          16 rows selected

Answer 6

with TEST as
(select 1 ID, 'A' NAME,    3 QTY from dual
union
select 2 ID, 'B' NAME,    2 QTY from dual
union
select 3 ID, 'A' NAME,    1 QTY from dual
union
select 4 ID, 'c' NAME,    0 QTY from dual
)
select ID, NAME, QTY, row_number() over(partition by QTY order by QTY) rw
  from (select a.*, rownum r1
          from (select a.*,
                       sum(QTY) over(order by QTY desc rows between unbounded preceding and current row) - QTY r
                  from test a
                 order by QTY desc) a
        connect by rownum - r <= qty)
 where r1 - qty <= r

Answer 7

楼主啊，给你提供个方案啊，通过一条SQL 就能查询出你要的结果啊，但是我没有时间给你具体写，给你思路吧，你看看可行不！
示例 
ID NAME QTY 
1  A    3 
1  B    2 
2  A    0 
2  A    1 
根据这个表编写个存储过程， 游标查询所有数据， 如果QTY为0，pass， 如果>0, insert into aaaa(临时表) QTY-1.select * from aaaa; //这条SQL就能查出你需要的数据。

Answer 8

TO Robin_Ares 你的SQL可以用，效率也很高，，，谢谢了

Answer 9

我执行怎么不行呢，报“用户数据中的 connect by 循环”错误，难道是我的数据库版本有问题（oracle 9i）？

Answer 10

-- 9i是抱“ORA-01436: CONNECT BY loop in user data”问题？那用下面的方法吧：
SQL> SELECT *
  2    FROM TEST_DD T1,
  3         (SELECT ROWNUM RN
  4            FROM ALL_OBJECTS
  5           WHERE ROWNUM <= (SELECT SUM(QTY) SUM_QTY FROM TEST_DD)) T2
  6   WHERE QTY >= RN
  7   ORDER BY 1, 2, 3, 4;        ID NAME        QTY         RN
---------- ---- ---------- ----------
         1 A             3          1
         1 A             3          2
         1 A             3          3
         1 B             2          1
         1 B             2          2
         2 A             1          16 rows selected

Answer 11

mantisXF 的是正解，效率也不错

Answer 12

谢谢十五楼的朋友，这个方法我也想到过。
但万一 qty >  all_objects 的记录总数，就有问题了。
 

Answer 13

呵呵，一般问题不大的。我现所知道的凑多列数问题就ALL_OBJECTS和CONNECT BY ROWNUM这两解决方案。
因为你的9i的数据库在运用CONNECT BY方面会有限制，10G以上版本的数据库就已经把CONNECT BY“做”得非常完善了。
如果本人上面写的3种方法在9i下都不行，那就只能考虑存储过程的方式了。
GOOD LUCK！～

Answer 14

SQL> with t as
  2  (select 1 ID, 'A' NAME,    3 QTY from dual 
  3  union 
  4  select 1 ID, 'B' NAME,    2 QTY from dual 
  5  union 
  6  select 2 ID, 'A' NAME,    0 QTY from dual 
  7  union 
  8  select 2 ID, 'A' NAME,    1 QTY from dual
  9  )
 10  select *
 11  from t t1, (select level l from dual connect by level <= (select max(QTY) from t)) t2
 12  where t1.qty >= t2.l
 13  order by t1.id, t1.name, t2.l;        ID N        QTY          L
---------- - ---------- ----------
         1 A          3          1
         1 A          3          2
         1 A          3          3
         1 B          2          1
         1 B          2          2
         2 A          1          1

Answer 15

学习一下，但感觉这种问题研究就行了，没必要实战吧
经常见到一个问题不是用语言硬解就是用sql硬解，还是两者结合比较好吧

Answer 16

  
  SELECT a.*, LEVEL
      FROM tablename a
     START WITH QTY <> 0
    CONNECT BY PRIOR QTY = QTY - 1
    ORDER BY id 

Answer 17

create table t2(id int,name varchar2(10),qty int)
insert into t2 
select 1,'A',3 from dual
union all
select 1,'B',2 from dual
union all
select 2,'A',0 from dual
union all
select 2,'A',1 from dual
create table temp(id int)
insert into temp 
select 1 from dual
union all
select 2 from dual
union all
select 3 from dual
union all
select 4 from dual
union all
select 5 from dual
union all
select 6 from dualselect * from 
(select distinct * from t2) a,temp b
where a.qty-b.id>=0
order by a.id,a.name,a.qty,b.id

Answer 18

create table t2(id int,name varchar2(10),qty int)
insert into t2 
select 1,'A',3 from dual
union all
select 1,'B',2 from dual
union all
select 2,'A',0 from dual
union all
select 2,'A',1 from dual
select id,name,qty,level from t2 a
start with qty<>0
connect by prior qty=qty-1
order by a.id,a.name,a.qty

Answer 19

SELECT * FROM 
(SELECT ID,NAME,QTY,ROW_NUMBER() OVER(PARTITION BY ID,NAME ORDER BY ID) RN FROM
(SELECT T.* FROM T,DBA_OBJECTS WHERE QTY!=0))
WHERE RN<=QTY结果:
    ID NAME QTY RN
1 A 3 1
1 A 3 2
1 A 3 3
1 B 2 1
1 B 2 2
2 A 1 1