请教一个比较复杂的sql查询语句

Question

SELECT (ID, ADDR, TELE, NUM
         SELECT ID,
                ADDR,
                TELE,
                NUM,
                ROW_NUMBER() OVER(PARTITION BY NUM ORDER BY ID) RN
           FROM TAB1) T
          WHERE T.RN <= 2

Answer 1

好像上面的写错了SELECT ID, ADDR, TELE, NUM FROM(
         SELECT ID,
                ADDR,
                TELE,
                NUM,
                ROW_NUMBER() OVER(PARTITION BY NUM ORDER BY ID) RN
           FROM TAB1) T
          WHERE T.RN <= 2

Answer 2

建表
create table TAB1
(
  NETPROXYID   VARCHAR2(255) not null,
  ADDR         VARCHAR2(255),
  TEL          VARCHAR2(255),
  SERIALNUMBER VARCHAR2(255)
)
插数据
insert into TAB1 (NETPROXYID, ADDR, TEL, SERIALNUMBER)
values ('H49', '叶A 沙头大道140号', '13XXX828071', 'HH003270611CN');
insert into TAB1 (NETPROXYID, ADDR, TEL, SERIALNUMBER)
values ('H21', '叶A 沙头大道140号', '13XXX828071', 'HH003270611CN');
insert into TAB1 (NETPROXYID, ADDR, TEL, SERIALNUMBER)
values ('H47', '吴A 志达', '23882238', 'HH003270316CN');
insert into TAB1 (NETPROXYID, ADDR, TEL, SERIALNUMBER)
values ('H76', '胡A 敬龙布艺城', '23360313', 'HH003269905CN');
insert into TAB1 (NETPROXYID, ADDR, TEL, SERIALNUMBER)
values ('H97', '叶A 沙头大道140号', '13XXX828071', 'HH003270611CN');
insert into TAB1 (NETPROXYID, ADDR, TEL, SERIALNUMBER)
values ('H00', '叶A 沙头大道140号', '13XXX828071', 'HH003270611CN');
insert into TAB1 (NETPROXYID, ADDR, TEL, SERIALNUMBER)
values ('H73', '苏A 晖楼强虹公司', '13XXX930094', 'HH003273768CN');
insert into TAB1 (NETPROXYID, ADDR, TEL, SERIALNUMBER)
values ('H92', '苏A 晖楼强虹公司', '13XXX930094', 'HH003273768CN');
insert into TAB1 (NETPROXYID, ADDR, TEL, SERIALNUMBER)
values ('H48', '苏A 晖楼强虹公司', '13XXX930094', 'HH003273768CN');
commit;
查询
SQL> SELECT T.*, T.ROWID FROM TAB1 T;
 
NETPROXYID                                                                       ADDR                                                                             TEL                                                                              SERIALNUMBER                                                                     ROWID
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------
H49                                                                              叶A 沙头大道140号                                                                13XXX828071                                                                      HH003270611CN                                                                    AAAUFbAAEAAC+AWAAA
H21                                                                              叶A 沙头大道140号                                                                13XXX828071                                                                      HH003270611CN                                                                    AAAUFbAAEAAC+AWAAB
H47                                                                              吴A 志达                                                                         23882238                                                                         HH003270316CN                                                                    AAAUFbAAEAAC+AWAAC
H76                                                                              胡A 敬龙布艺城                                                                   23360313                                                                         HH003269905CN                                                                    AAAUFbAAEAAC+AWAAD
H97                                                                              叶A 沙头大道140号                                                                13XXX828071                                                                      HH003270611CN                                                                    AAAUFbAAEAAC+AWAAE
H00                                                                              叶A 沙头大道140号                                                                13XXX828071                                                                      HH003270611CN                                                                    AAAUFbAAEAAC+AWAAF
H73                                                                              苏A 晖楼强虹公司                                                                 13XXX930094                                                                      HH003273768CN                                                                    AAAUFbAAEAAC+AWAAG
H92                                                                              苏A 晖楼强虹公司                                                                 13XXX930094                                                                      HH003273768CN                                                                    AAAUFbAAEAAC+AWAAH
H48                                                                              苏A 晖楼强虹公司                                                                 13XXX930094                                                                      HH003273768CN                                                                    AAAUFbAAEAAC+AWAAI
 
9 rows selected需求查询
SELECT NETPROXYID, ADDR, TEL, SERIALNUMBER
  FROM (SELECT NETPROXYID,
               ADDR,
               TEL,
               SERIALNUMBER,
               RANK() OVER(PARTITION BY TEL ORDER BY NETPROXYID) RN
          FROM TAB1) T
 WHERE T.RN <= 2
 ORDER BY NETPROXYID
SQL> SELECT NETPROXYID, ADDR, TEL, SERIALNUMBER
  2    FROM (SELECT NETPROXYID,
  3                 ADDR,
  4                 TEL,
  5                 SERIALNUMBER,
  6                 RANK() OVER(PARTITION BY TEL ORDER BY NETPROXYID) RN
  7            FROM TAB1) T
  8   WHERE T.RN <= 2
  9   ORDER BY NETPROXYID
 10  /
 
NETPROXYID                                                                       ADDR                                                                             TEL                                                                              SERIALNUMBER
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
H00                                                                              叶A 沙头大道140号                                                                13XXX828071                                                                      HH003270611CN
H21                                                                              叶A 沙头大道140号                                                                13XXX828071                                                                      HH003270611CN
H47                                                                              吴A 志达                                                                         23882238                                                                         HH003270316CN
H48                                                                              苏A 晖楼强虹公司                                                                 13XXX930094                                                                      HH003273768CN
H73                                                                              苏A 晖楼强虹公司                                                                 13XXX930094                                                                      HH003273768CN
H76                                                                              胡A 敬龙布艺城                                                                   23360313                                                                         HH003269905CN
 
6 rows selected
 
SQL> 

Answer 3

SELECT NETPROXYID, ADDR, TEL, SERIALNUMBER
  FROM (SELECT NETPROXYID,
               ADDR,
               TEL,
               SERIALNUMBER,
               RANK() OVER(PARTITION BY TEL ORDER BY NETPROXYID) RN
          FROM TAB1) T
 WHERE T.RN <= 2
 ORDER BY NETPROXYID

Answer 4

===================================================
SQL> create table test(
  2   id varchar2(5),
  3   name varchar2(30),
  4   address varchar2(100),
  5   telephone varchar2(100),
  6   vnumber varchar2(100));
 
Table createdSQL>  insert into test(id,name,address,telephone,vnumber)
  2      select 'H76','胡A','敬龙布艺城','23360313','HH003269905CN' from dual union all
  3      select 'H47','吴A','志达','23882238','HH003270316CN' from dual union all
  4      select 'H21','叶A','沙头大道140号','13XXXX828071','HH003270611CN' from dual union all
  5      select 'H49','叶A','沙头大道140号','13XXXX828071','HH003270611CN' from dual union all
  6      select 'H97','叶A','沙头大道140号','13XXXX828071','HH003270611CN' from dual union all
  7      select 'H00','叶A','沙头大道140号','13XXXX828071','HH003270611CN' from dual union all
  8      select 'H73','苏A','晖楼强虹公司','13XXXX930094','HH003273768CN' from dual union all
  9      select 'H92','苏A','晖楼强虹公司','13XXXX930094','HH003273768CN' from dual union all
 10    select 'H48','苏A','晖楼强虹公司','13XXXX930094','HH003273768CN' from dual
 11  ;
 
9 rows inserted
 
SQL> select * from test;
 
ID    NAME                           ADDRESS                                                                          TELEPHONE                                                                        VNUMBER
----- ------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
H76   胡A                            敬龙布艺城                                                                       23360313                                                                         HH003269905CN
H47   吴A                            志达                                                                             23882238                                                                         HH003270316CN
H21   叶A                            沙头大道140号                                                                    13XXXX828071                                                                     HH003270611CN
H49   叶A                            沙头大道140号                                                                    13XXXX828071                                                                     HH003270611CN
H97   叶A                            沙头大道140号                                                                    13XXXX828071                                                                     HH003270611CN
H00   叶A                            沙头大道140号                                                                    13XXXX828071                                                                     HH003270611CN
H73   苏A                            晖楼强虹公司                                                                     13XXXX930094                                                                     HH003273768CN
H92   苏A                            晖楼强虹公司                                                                     13XXXX930094                                                                     HH003273768CN
H48   苏A                            晖楼强虹公司                                                                     13XXXX930094                                                                     HH003273768CN
 
9 rows selectedSQL> select 
           id,name,address,telephone,vnumber 
       from (
               select 
                     id,name,address,telephone,vnumber,rank() over(partition by telephone order by id) en 
                 from addbatchtest
            ) t  
       where t.en<=2 order by id;
 
ID    NAME                           ADDRESS                                                                          TELEPHONE                                                                        VNUMBER
----- ------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
H00   叶A                            沙头大道140号                                                                    13XXXX828071                                                                     HH003270611CN
H21   叶A                            沙头大道140号                                                                    13XXXX828071                                                                     HH003270611CN
H47   吴A                            志达                                                                             23882238                                                                         HH003270316CN
H48   苏A                            晖楼强虹公司                                                                     13XXXX930094                                                                     HH003273768CN
H73   苏A                            晖楼强虹公司                                                                     13XXXX930094                                                                     HH003273768CN
H76   胡A                            敬龙布艺城                                                                       23360313                                                                         HH003269905CN
 
6 rows selected

Answer 5

上面有个小问题，更正一下
=================================================== 
SQL> create table test( 
  2  id varchar2(5), 
  3  name varchar2(30), 
  4  address varchar2(100), 
  5  telephone varchar2(100), 
  6  vnumber varchar2(100)); Table created 
SQL>  insert into test(id,name,address,telephone,vnumber) 
  2      select 'H76','胡A','敬龙布艺城','23360313','HH003269905CN' from dual union all 
  3      select 'H47','吴A','志达','23882238','HH003270316CN' from dual union all 
  4      select 'H21','叶A','沙头大道140号','13XXXX828071','HH003270611CN' from dual union all 
  5      select 'H49','叶A','沙头大道140号','13XXXX828071','HH003270611CN' from dual union all 
  6      select 'H97','叶A','沙头大道140号','13XXXX828071','HH003270611CN' from dual union all 
  7      select 'H00','叶A','沙头大道140号','13XXXX828071','HH003270611CN' from dual union all 
  8      select 'H73','苏A','晖楼强虹公司','13XXXX930094','HH003273768CN' from dual union all 
  9      select 'H92','苏A','晖楼强虹公司','13XXXX930094','HH003273768CN' from dual union all 
10    select 'H48','苏A','晖楼强虹公司','13XXXX930094','HH003273768CN' from dual 
11  ; 9 rows inserted SQL> select * from test; ID    NAME                          ADDRESS                                                                          TELEPHONE                                                                        VNUMBER 
----- ------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- 
H76  胡A                            敬龙布艺城                                                                      23360313                                                                        HH003269905CN 
H47  吴A                            志达                                                                            23882238                                                                        HH003270316CN 
H21  叶A                            沙头大道140号                                                                    13XXXX828071                                                                    HH003270611CN 
H49  叶A                            沙头大道140号                                                                    13XXXX828071                                                                    HH003270611CN 
H97  叶A                            沙头大道140号                                                                    13XXXX828071                                                                    HH003270611CN 
H00  叶A                            沙头大道140号                                                                    13XXXX828071                                                                    HH003270611CN 
H73  苏A                            晖楼强虹公司                                                                    13XXXX930094                                                                    HH003273768CN 
H92  苏A                            晖楼强虹公司                                                                    13XXXX930094                                                                    HH003273768CN 
H48  苏A                            晖楼强虹公司                                                                    13XXXX930094                                                                    HH003273768CN 9 rows selected SQL> select 
          id,name,address,telephone,vnumber 
      from ( 
              select 
                    id,name,address,telephone,vnumber,rank() over(partition by telephone order by id) en 
                from test 
            ) t  
      where t.en <=2 order by id; ID    NAME                          ADDRESS                                                                          TELEPHONE                                                                        VNUMBER 
----- ------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- 
H00  叶A                            沙头大道140号                                                                    13XXXX828071                                                                    HH003270611CN 
H21  叶A                            沙头大道140号                                                                    13XXXX828071                                                                    HH003270611CN 
H47  吴A                            志达                                                                            23882238                                                                        HH003270316CN 
H48  苏A                            晖楼强虹公司                                                                    13XXXX930094                                                                    HH003273768CN 
H73  苏A                            晖楼强虹公司                                                                    13XXXX930094                                                                    HH003273768CN 
H76  胡A                            敬龙布艺城                                                                      23360313                                                                        HH003269905CN 6 rows selected