不知如何编写这样复杂ORACLE的SQL语句？

Question

第一张表:
XH NSRSBH            NSRMC                   FP_DM         RQ        FS     FPQH      FPZH     LRR_DM
1  330282691361736 宁波睿顿园林工具有限公司  133020921906 2010/11/12 25  00419076 00419100 13302822935由FPQH=‘00419076’开始；FPZH=‘00419100’结束，查询生成25条第二张表样子的记录
第二张表:    
XH NSRSBH             NSRMC                       FP_DM         RQ       FPHM      LRR_DM
1 330282691361736 宁波睿顿园林工具有限公司 133020921906 2010/11/12  00419076 13302822935
2 330282691361736 宁波睿顿园林工具有限公司 133020921906 2010/11/12  00419077 13302822935
3 330282691361736 宁波睿顿园林工具有限公司 133020921906 2010/11/12  00419078 13302822935
依此类推……
25 330282691361736 宁波睿顿园林工具有限公司 133020921906 2010/11/12  00419100 13302822935
再进一步的话,表一中有N条记录,表二中要有SUM(FS)条记录.
不知如何编写ORACLE的SQL语句？（最好只用一条复杂select语句达到目的）

Answer 1

1,
select rownum, XH, NSRSBH, NSRMC, FP_DM, RQ, FPHM, LRR_DM 
from table1
where FPQH >= '00419076'
and   FPZH <= '00419100'；2, 没明白这句话的意思：再进一步的话,表一中有N条记录,表二中要有SUM(FS)条记录

Answer 2

select XH ,NSRSBH, NSRMC, FP_DM, RQ, FS+1 fs, FPQH, FPZH, LRR_DM
(select rownum-1 rn from dual connect by rownum <=(select max(fs) from table1))a,
table1 b
where a.rn<b.fs

Answer 3

按你的想法实现，即使写个存储过程都不是很简单，如果只写一条sql语句的实现的话，你的问题趋近于无解。因为你想自动生成FPHM，如果想生成FPHM，需要转化为数字，可是FPHM是 字符型的，会丢掉0

Answer 4

select XH ,NSRSBH, NSRMC, FP_DM, RQ, lpad(FPQH+rn,8,'0'), FPZH, LRR_DM
(select rownum-1 rn from dual connect by rownum <=(select max(fs) from table1))a,
table1 b
where a.rn<b.fs

Answer 5

SQL> select * from table1;
 
        XH     NSRSBH NSRMC                                                                                 FP_DM RQ                                                                                       FS FPQH                                                                             FPZH                                                                                 LRR_DM
---------- ---------- -------------------------------------------------------------------------------- ---------- -------------------------------------------------------------------------------- ---------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------
         1 3302826913 宁波睿顿园林工具有限公司                                                         1330209219 2010/11/12                                                                               25 00419076                                                                         00419100                                                                         1330282293
 
SQL> 

Answer 6

SQL> select a.rn+1 XH ,NSRSBH, NSRMC, FP_DM, RQ, lpad(FPQH+rn,8,'0'), FPZH, LRR_DM from
  2  (select rownum-1 rn from dual connect by rownum <=(select max(fs) from table1))a,
  3  table1 b
  4  where a.rn<b.fs
  5  ;
 
        XH     NSRSBH NSRMC                                                                                 FP_DM RQ                                                                               LPAD(FPQH+RN,8,'0') FPZH                                                                                 LRR_DM
---------- ---------- -------------------------------------------------------------------------------- ---------- -------------------------------------------------------------------------------- ------------------- -------------------------------------------------------------------------------- ----------
         1 3302826913 宁波睿顿园林工具有限公司                                                         1330209219 2010/11/12                                                                       00419076            00419100                                                                         1330282293
         2 3302826913 宁波睿顿园林工具有限公司                                                         1330209219 2010/11/12                                                                       00419077            00419100                                                                         1330282293
         3 3302826913 宁波睿顿园林工具有限公司                                                         1330209219 2010/11/12                                                                       00419078            00419100                                                                         1330282293
         4 3302826913 宁波睿顿园林工具有限公司                                                         1330209219 2010/11/12                                                                       00419079            00419100                                                                         1330282293
         5 3302826913 宁波睿顿园林工具有限公司                                                         1330209219 2010/11/12                                                                       00419080            00419100                                                                         1330282293
         6 3302826913 宁波睿顿园林工具有限公司                                                         1330209219 2010/11/12                                                                       00419081            00419100                                                                         1330282293
         7 3302826913 宁波睿顿园林工具有限公司                                                         1330209219 2010/11/12                                                                       00419082            00419100                                                                         1330282293
         8 3302826913 宁波睿顿园林工具有限公司                                                         1330209219 2010/11/12                                                                       00419083            00419100                                                                         1330282293
         9 3302826913 宁波睿顿园林工具有限公司                                                         1330209219 2010/11/12                                                                       00419084            00419100                                                                         1330282293
        10 3302826913 宁波睿顿园林工具有限公司                                                         1330209219 2010/11/12                                                                       00419085            00419100                                                                         1330282293
        11 3302826913 宁波睿顿园林工具有限公司                                                         1330209219 2010/11/12                                                                       00419086            00419100                                                                         1330282293
        12 3302826913 宁波睿顿园林工具有限公司                                                         1330209219 2010/11/12                                                                       00419087            00419100                                                                         1330282293
        13 3302826913 宁波睿顿园林工具有限公司                                                         1330209219 2010/11/12                                                                       00419088            00419100                                                                         1330282293
        14 3302826913 宁波睿顿园林工具有限公司                                                         1330209219 2010/11/12                                                                       00419089            00419100                                                                         1330282293
        15 3302826913 宁波睿顿园林工具有限公司                                                         1330209219 2010/11/12                                                                       00419090            00419100                                                                         1330282293
        16 3302826913 宁波睿顿园林工具有限公司                                                         1330209219 2010/11/12                                                                       00419091            00419100                                                                         1330282293
        17 3302826913 宁波睿顿园林工具有限公司                                                         1330209219 2010/11/12                                                                       00419092            00419100                                                                         1330282293
        18 3302826913 宁波睿顿园林工具有限公司                                                         1330209219 2010/11/12                                                                       00419093            00419100                                                                         1330282293
        19 3302826913 宁波睿顿园林工具有限公司                                                         1330209219 2010/11/12                                                                       00419094            00419100                                                                         1330282293
        20 3302826913 宁波睿顿园林工具有限公司                                                         1330209219 2010/11/12                                                                       00419095            00419100                                                                         1330282293
 
        XH     NSRSBH NSRMC                                                                                 FP_DM RQ                                                                               LPAD(FPQH+RN,8,'0') FPZH                                                                                 LRR_DM
---------- ---------- -------------------------------------------------------------------------------- ---------- -------------------------------------------------------------------------------- ------------------- -------------------------------------------------------------------------------- ----------
        21 3302826913 宁波睿顿园林工具有限公司                                                         1330209219 2010/11/12                                                                       00419096            00419100                                                                         1330282293
        22 3302826913 宁波睿顿园林工具有限公司                                                         1330209219 2010/11/12                                                                       00419097            00419100                                                                         1330282293
        23 3302826913 宁波睿顿园林工具有限公司                                                         1330209219 2010/11/12                                                                       00419098            00419100                                                                         1330282293
        24 3302826913 宁波睿顿园林工具有限公司                                                         1330209219 2010/11/12                                                                       00419099            00419100                                                                         1330282293
        25 3302826913 宁波睿顿园林工具有限公司                                                         1330209219 2010/11/12                                                                       00419100            00419100                                                                         1330282293
 
25 rows selected
 
SQL> 

Answer 7

select rownum XH ,NSRSBH, NSRMC, FP_DM, RQ, lpad(FPQH+rn,8,'0'), FPZH, LRR_DM
from (select rownum-1 rn ,NSRSBH from dual,(select NSRSBH,fs from table1) c
connect by rownum <=c.fs) a,
table1 b
where a.NSRSBH=b.NSRSBH;
你运行下这个应该行吧，但是用类似这样的方法实现了，感觉也没什么意义啊

Answer 8

再建个sequence吧。XH改成从sequence取.即xh取sequence.nextval

Answer 9

sequence要有写入（create,altert等）权限，我只有读的权限，只能使用select

Answer 10

哦，那用分析函数把。
select row_number()over(order by b.rowid,lpad(FPQH+rn,8,'0')) XH ,NSRSBH, NSRMC, FP_DM, RQ, lpad(FPQH+rn,8,'0'), FPZH, LRR_DM from
    (select rownum-1 rn from dual connect by rownum <=(select max(fs) from table1))a,
    table1 b
    where a.rn<b.fs

Answer 11

select rownum XH ,NSRSBH, NSRMC, FP_DM, RQ, FPHM, LRR_DM
from (select  lpad(FPQH+level-1,8,'0') FPHM ,NSRSBH from table1  connect by level <=FPZH-FPQH+1 order by FPQH) a,
table1 b
where a.NSRSBH=b.NSRSBH;
你再试试这个，我在我机器上试的例子都通过了啊

Answer 12

我先用:select nsrdzdah,fpzl_dm,fp_dm,fs,fpqh,fpzh from fp_yj   where  nsr_swjg_dm='13302822100'  and to_char(rq,'yyyymmdd')='20101110' and substr(fpzl_dm,-1,1)='0' and fpzl_dm not like '1%' and fpzl_dm<> '20920' and  fp_yjjg_dm='10'  
 
序号     NSRDZDAH                 FPZL_DM   FP_DM        FS  FPQH
 
       FPZH 
1 330210000004201377 21190 133020921906 25 00410926 00410950
2 330209000004004605 21190 133020921906 5 00423476 00423480
3 330209000002979133 21190 133020921906 25 00438401 00438425
4 330206000000372091 21250 133020922506 25 00541641 00541665
5 330207000001390216 21250 133021022506 25 00089026 00089050
6 330209000002863659 21190 133020921906 25 00408051 00408075
7 330209000003078489 27310 133021023104 25 00422151 00422175
8 330200000000031119 21010 133020921103 30 00332256 00332285
9 330200000000039615 21190 133020921906 25 00425801 00425825
10 330200000000022929 21190 133020921906 100 00442651 00442750
11 330200000000014328 21190 133020921906 25 00397876 00397900
12 330200000000051452 21190 133020921906 100 00443151 00443250
13 330200000000015099 21190 133020921906 50 01147001 01147050
用你的程序后:select nsrdzdah,fpzl_dm,fp_dm,fs,lpad(fpqh+rownum-1,8,'0') from dual,(select nsrdzdah,fpzl_dm,fp_dm,fs,fpqh,fpzh from fp_yj   where  nsr_swjg_dm='13302822100'  and to_char(rq,'yyyymmdd')='20101110' and substr(fpzl_dm,-1,1)='0' and fpzl_dm not like '1%' and fpzl_dm<> '20920' and  fp_yjjg_dm='10'  ) c  connect by  rownum<=c.fs   结果是:
       NSRDZDAH  
                              FPZL_DM  
                                         FP_DM  
                                                       FS  
序号                                                        LPAD(FPQH+ROWNUM-1,8,'0')
1 330210000004201377 21190 133020921906 25 00410926
2 330210000004201377 21190 133020921906 25 00410927
3 330210000004201377 21190 133020921906 25 00410928
4 330210000004201377 21190 133020921906 25 00410929
5 330210000004201377 21190 133020921906 25 00410930
6 330210000004201377 21190 133020921906 25 00410931
7 330210000004201377 21190 133020921906 25 00410932
8 330210000004201377 21190 133020921906 25 00410933
9 330210000004201377 21190 133020921906 25 00410934
10 330210000004201377 21190 133020921906 25 00410935
11 330210000004201377 21190 133020921906 25 00410936
12 330210000004201377 21190 133020921906 25 00410937
13 330210000004201377 21190 133020921906 25 00410938
14 330210000004201377 21190 133020921906 25 00410939
15 330210000004201377 21190 133020921906 25 00410940
16 330210000004201377 21190 133020921906 25 00410941
17 330210000004201377 21190 133020921906 25 00410942
18 330210000004201377 21190 133020921906 25 00410943
19 330210000004201377 21190 133020921906 25 00410944
20 330210000004201377 21190 133020921906 25 00410945
21 330210000004201377 21190 133020921906 25 00410946
22 330210000004201377 21190 133020921906 25 00410947
23 330210000004201377 21190 133020921906 25 00410948
24 330210000004201377 21190 133020921906 25 00410949
25 330210000004201377 21190 133020921906 25 00410950
26 330200000000031119 21010 133020921103 30 00332281
27 330200000000031119 21010 133020921103 30 00332282
28 330200000000031119 21010 133020921103 30 00332283
29 330200000000031119 21010 133020921103 30 00332284
30 330200000000031119 21010 133020921103 30 00332285
31 330200000000022929 21190 133020921906 100 00442681
32 330200000000022929 21190 133020921906 100 00442682
33 330200000000022929 21190 133020921906 100 00442683
34 330200000000022929 21190 133020921906 100 00442684
35 330200000000022929 21190 133020921906 100 00442685
36 330200000000022929 21190 133020921906 100 00442686
37 330200000000022929 21190 133020921906 100 00442687
38 330200000000022929 21190 133020921906 100 00442688
39 330200000000022929 21190 133020921906 100 00442689
40 330200000000022929 21190 133020921906 100 00442690
41 330200000000022929 21190 133020921906 100 00442691
42 330200000000022929 21190 133020921906 100 00442692
43 330200000000022929 21190 133020921906 100 00442693
44 330200000000022929 21190 133020921906 100 00442694
45 330200000000022929 21190 133020921906 100 00442695
46 330200000000022929 21190 133020921906 100 00442696
47 330200000000022929 21190 133020921906 100 00442697
48 330200000000022929 21190 133020921906 100 00442698
49 330200000000022929 21190 133020921906 100 00442699
50 330200000000022929 21190 133020921906 100 00442700
51 330200000000022929 21190 133020921906 100 00442701
52 330200000000022929 21190 133020921906 100 00442702
53 330200000000022929 21190 133020921906 100 00442703
54 330200000000022929 21190 133020921906 100 00442704
55 330200000000022929 21190 133020921906 100 00442705
56 330200000000022929 21190 133020921906 100 00442706
57 330200000000022929 21190 133020921906 100 00442707
58 330200000000022929 21190 133020921906 100 00442708
59 330200000000022929 21190 133020921906 100 00442709
60 330200000000022929 21190 133020921906 100 00442710
61 330200000000022929 21190 133020921906 100 00442711
62 330200000000022929 21190 133020921906 100 00442712
63 330200000000022929 21190 133020921906 100 00442713
64 330200000000022929 21190 133020921906 100 00442714
65 330200000000022929 21190 133020921906 100 00442715
66 330200000000022929 21190 133020921906 100 00442716
67 330200000000022929 21190 133020921906 100 00442717
68 330200000000022929 21190 133020921906 100 00442718
69 330200000000022929 21190 133020921906 100 00442719
70 330200000000022929 21190 133020921906 100 00442720
71 330200000000022929 21190 133020921906 100 00442721
72 330200000000022929 21190 133020921906 100 00442722
73 330200000000022929 21190 133020921906 100 00442723
74 330200000000022929 21190 133020921906 100 00442724
75 330200000000022929 21190 133020921906 100 00442725
76 330200000000022929 21190 133020921906 100 00442726
77 330200000000022929 21190 133020921906 100 00442727
78 330200000000022929 21190 133020921906 100 00442728
79 330200000000022929 21190 133020921906 100 00442729
80 330200000000022929 21190 133020921906 100 00442730
81 330200000000022929 21190 133020921906 100 00442731
82 330200000000022929 21190 133020921906 100 00442732
83 330200000000022929 21190 133020921906 100 00442733
84 330200000000022929 21190 133020921906 100 00442734
85 330200000000022929 21190 133020921906 100 00442735
86 330200000000022929 21190 133020921906 100 00442736
87 330200000000022929 21190 133020921906 100 00442737
88 330200000000022929 21190 133020921906 100 00442738
89 330200000000022929 21190 133020921906 100 00442739
90 330200000000022929 21190 133020921906 100 00442740
91 330200000000022929 21190 133020921906 100 00442741
92 330200000000022929 21190 133020921906 100 00442742
93 330200000000022929 21190 133020921906 100 00442743
94 330200000000022929 21190 133020921906 100 00442744
95 330200000000022929 21190 133020921906 100 00442745
96 330200000000022929 21190 133020921906 100 00442746
97 330200000000022929 21190 133020921906 100 00442747
98 330200000000022929 21190 133020921906 100 00442748
99 330200000000022929 21190 133020921906 100 00442749
100 330200000000022929 21190 133020921906 100 00442750
101 330209000004004605 21190 133020921906 5 00423576
102 330209000002979133 21190 133020921906 25 00438502
103 330206000000372091 21250 133020922506 25 00541743
104 330207000001390216 21250 133021022506 25 00089129
105 330209000002863659 21190 133020921906 25 00408155
106 330209000003078489 27310 133021023104 25 00422256
107 330200000000031119 21010 133020921103 30 00332362
108 330200000000039615 21190 133020921906 25 00425908
109 330200000000022929 21190 133020921906 100 00442759
110 330200000000014328 21190 133020921906 25 00397985
111 330200000000051452 21190 133020921906 100 00443261
112 330200000000015099 21190 133020921906 50 01147112
要求记录数不对,应是sum(FS)=485条,你的是112条,只有第一条对应是对的,不知为啥,请指教!

Answer 13

select row_number()over(order by b.rowid,lpad(FPQH+rn,8,'0')) XH ,NSRSBH, NSRMC, FP_DM, RQ, lpad(FPQH+rn,8,'0'), FPZH, LRR_DM from
    (select rownum-1 rn from dual connect by rownum <=(select max(fs) from table1))a,
    table1 b
    where a.rn<b.fsmax(fs)取表中最大的记录数，不符合要求sum(FS)=485条，我想可能不行；
select rownum XH ,NSRSBH, NSRMC, FP_DM, RQ, FPHM, LRR_DM
from (select  lpad(FPQH+level-1,8,'0') FPHM ,NSRSBH from table1  connect by level <=FPZH-FPQH+1 order by FPQH) a,
table1 b
where a.NSRSBH=b.NSRSBH;这条查询试了N次都导致内存溢出！

Answer 14

倒，条件里不是有 a.rn<b.fs，不要想，运行下再说。

Answer 15

照你的意思编写的SQL
select row_number()over(order by b.rowid, lpad(b.FPQH+rn,8,'0') ) xh ,b.nsrdzdah, b.fpzl_dm,b.fp_dm, lpad(b.FPQH+rn,8,'0') fphm, b. LRR_DM from (select rownum-1 rn from dual connect by rownum <=(select max(fs)  from fp_yj where nsr_swjg_dm='13302822100' and to_char(rq,'yyyymmdd')='20101110' and substr(fpzl_dm,-1,1)='0' and fpzl_dm not like '1%' and fpzl_dm<> '20920' and fp_yjjg_dm='10') ) a,
     (select nsrdzdah,fpzl_dm,fp_dm,fs,fpqh,fpzh,lrr_dm from fp_yj where nsr_swjg_dm='13302822100' and to_char(rq,'yyyymmdd')='20101110'  and substr(fpzl_dm,-1,1)='0' and fpzl_dm not like '1%' and fpzl_dm<> '20920' and fp_yjjg_dm='10' ) b   where a.rn<b.fs  运行后出现数据库操作出现错误：uncategorized SQLException，ORA-01473: cannot have subqueries in CONNECT BY clause，我分析一下，原因在这一句：【select rownum-1 rn from dual connect by rownum <=(select max(fs)  from fp_yj where nsr_swjg_dm='13302822100' and to_char(rq,'yyyymmdd')='20101110' and substr(fpzl_dm,-1,1)='0' and fpzl_dm not like '1%' and fpzl_dm<> '20920' and fp_yjjg_dm='10'】,不知为啥?

Answer 16

贴下表结构,并且写两条数据.你的sql在我这里没报错.
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 
Connected as scott
 
SQL> 
SQL> create table fp_yj
  2  (nsrdzdah varchar2(100),
  3  fpzl_dm varchar2(100),
  4  fp_dm varchar2(100),
  5  fs number,
  6  fpqh varchar2(100),
  7  fpzh varchar2(100),
  8  lrr_dm varchar2(100),
  9  fp_yjjg_dm varchar2(100),
 10  rq date,
 11  nsr_swjg_dm varchar2(100)
 12  );
 
Table created
 
SQL> 
SQL> select row_number() over(order by b.rowid, lpad(b.FPQH + rn, 8, '0')) xh,
  2         b.nsrdzdah,
  3         b.fpzl_dm,
  4         b.fp_dm,
  5         lpad(b.FPQH + rn, 8, '0') fphm,
  6         b. LRR_DM
  7    from (select rownum - 1 rn
  8            from dual
  9          connect by rownum <= (select max(fs)
 10                                  from fp_yj
 11                                 where nsr_swjg_dm = '13302822100'
 12                                   and to_char(rq, 'yyyymmdd') = '20101110'
 13                                   and substr(fpzl_dm, -1, 1) = '0'
 14                                   and fpzl_dm not like '1%'
 15                                   and fpzl_dm <> '20920'
 16                                   and fp_yjjg_dm = '10')) a,
 17         (select nsrdzdah, fpzl_dm, fp_dm, fs, fpqh, fpzh, lrr_dm
 18            from fp_yj
 19           where nsr_swjg_dm = '13302822100'
 20             and to_char(rq, 'yyyymmdd') = '20101110'
 21             and substr(fpzl_dm, -1, 1) = '0'
 22             and fpzl_dm not like '1%'
 23             and fpzl_dm <> '20920'
 24             and fp_yjjg_dm = '10') b
 25   where a.rn < b.fs
 26  ;
 
        XH NSRDZDAH                                                                         FPZL_DM                                                                          FP_DM                                                                            FPHM             LRR_DM
---------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------- --------------------------------------------------------------------------------
 
SQL> 

Answer 17

楼主，在水一方在6楼的答案已经是正解了，我这里测试都通过了，我用的是三条记录做的测试，你把我的测试代码复制过去测试下吧。WITH TB AS (
SELECT 1 XH, 3 PXH, '330282691361736' NSRSBH, '宁波睿顿园林工具有限公司' NSRMC, '133020921906' FP_DM, to_date('2010/11/12','YYYY/MM/DD') RQ, 25 FS, '00419076' FPQH, '00419100' FPZH, '13302822935' LRR_DM FROM DUAL UNION ALL
SELECT 2 XH, 1 , '330282691361737' NSRSBH, '宁波大华汽车工具有限公司' NSRMC, '133020921907' FP_DM, to_date('2010/11/12','YYYY/MM/DD') RQ, 5 FS, '00519076' FPQH, '00519080' FPZH, '13302822936' LRR_DM FROM DUAL UNION ALL
SELECT 3 XH, 2 , '330282691361738' NSRSBH, '宁波地下管道工具有限公司' NSRMC, '133020921908' FP_DM, to_date('2010/11/12','YYYY/MM/DD') RQ, 7 FS, '00619076' FPQH, '00619082' FPZH, '13302822937' LRR_DM FROM DUAL
)
select XH ,NSRSBH, NSRMC, FP_DM, RQ, lpad(FPQH+rn,8,'0'), FPZH, LRR_DM
FROM (select rownum-1 rn from dual connect by rownum <=(select max(fs) from TB))a,
TB b
where a.rn<b.fs

Answer 18

WITH TB AS (
SELECT 1 XH, 3 PXH, '330282691361736' NSRSBH, '宁波睿顿园林工具有限公司' NSRMC, '133020921906' FP_DM, to_date('2010/11/12','YYYY/MM/DD') RQ, 25 FS, '00419076' FPQH, '00419100' FPZH, '13302822935' LRR_DM FROM DUAL UNION ALL
SELECT 2 XH, 1 , '330282691361737' NSRSBH, '宁波大华汽车工具有限公司' NSRMC, '133020921907' FP_DM, to_date('2010/11/12','YYYY/MM/DD') RQ, 5 FS, '00519076' FPQH, '00519080' FPZH, '13302822936' LRR_DM FROM DUAL UNION ALL
SELECT 3 XH, 2 , '330282691361738' NSRSBH, '宁波地下管道工具有限公司' NSRMC, '133020921908' FP_DM, to_date('2010/11/12','YYYY/MM/DD') RQ, 7 FS, '00619076' FPQH, '00619082' FPZH, '13302822937' LRR_DM FROM DUAL
)
select XH ,NSRSBH, NSRMC, FP_DM, RQ, lpad(FPQH+rn,8,'0'), FPZH, LRR_DM
FROM (select rownum-1 rn from dual connect by rownum <=(select max(fs) from TB))a,
TB b
where a.rn<b.fs也是出现错误:ORA-01473:cannot have subqueries in CONNECT BY clause,有没有别的方法?

Answer 19

是不是数据库版本的问题呢？这个代码在我这里完全不需要修改，没有任何错误的，我这里的数据库是oracle 10g。
据我所知是没有别的办法，单行转多行都是使用connect by子句的。

Answer 20

select row_number()over(order by b.rowid, lpad(b.FPQH+rn,8,'0') ) xh ,b.nsrdzdah, b.fpzl_dm,b.fp_dm, lpad(b.FPQH+rn,8,'0') fphm, b. LRR_DM from (select rownum-1 rn from (select max(fs) gap from fp_yj gap where nsr_swjg_dm='13302822100' and to_char(rq,'yyyymmdd')='20101110' and substr(fpzl_dm,-1,1)='0' and fpzl_dm not like '1%' and fpzl_dm<> '20920' and fp_yjjg_dm='10')  connect by rownum<=gap) a,
     (select nsrdzdah,fpzl_dm,fp_dm,fs,fpqh,fpzh,lrr_dm from fp_yj where nsr_swjg_dm='13302822100' and to_char(rq,'yyyymmdd')='20101110'  and substr(fpzl_dm,-1,1)='0' and fpzl_dm not like '1%' and fpzl_dm<> '20920' and fp_yjjg_dm='10' ) b   where a.rn<b.fs 
你看一下,以上SQL调试成功!与你的有差别!

Answer 21

现在又有一个问题:
第一张表,表名table1,内容如下
XH NSRDZDAH(纳税人电子档案号)       FP_DM(发票代码)             fphm(发票号码)          1 330210000004201377  133020921906 00410926
2 330210000004201377  133020921906 00410927
3 330210000004201377  133020921906 00410928
4 330210000004201377  133020921906 00410929
5 330210000004201377  133020921906 00410930
6 330210000004201377 133020921906  00410931
7 330210000004201377 133020921906  00410932
8 330210000004201377 133020921906  00410933
9 330210000004201377 133020921906  00410934
10 330210000004201377 133020921906 00410935
11 330210000004201377 133020921906 00410936
12 330210000004201377 133020921906 00410937
13 330210000004201377 133020921906 00410938
14 330210000004201377 133020921906 00410939
15 330210000004201377 133020921906 00410940
16 330210000004201377 133020921906 00410941
17 330210000004201377 133020921906 00410942
18 330210000004201377 133020921906 00410943
19 330210000004201377 133020921906 00410944
20 330210000004201377 133020921906 00410945
第二一张表,表名table2,字段一样,内容不一样;
要求编写在table1存在的fp_dm,fphm 而table2不存在的fp_dm,fphm

Answer 22

select fp_dm, fphm from table1 
minus
select fp_dm, fphm from table2;或者
select * 
from table1 
where (fp_dm, fphm) not in (select fp_dm, fphm from table2) ;