指教SQL语句如何实现(号码段的合并)

Question

有一张数据表table1
表结构：xh,nsrsbh,fpzldm,fpdm,fphm 分别代表 序号，纳税人识别号，发票种类代码，发票代码，发票代码
XH   NSRSBH          FPZLDM  FPDM            FPHM
1 33020495423019X 21010 133020921103 00909291
2 33020495423019X 21010 133020921103 00909292
3 330203793010240 21250 133021022506 00035801
4 330203793010240 21250 133021022506 00035802
5 330203793010240 21250 133021022506 00035803
6 330203793010240 21250 133021022506 00035804
7 330203793010240 21250 133021022506 00035805
8 330203793010240 21250 133021022506 00035806
9 330203793010240 21250 133021022506 00035807
10 330203793010240 21250 133021022506 00035808
11 330203793010240 21250 133021022506 00035809
12 330203793010240 21250 133021022506 00035810
13 330205674732355 21250 133020922506 00450256
14 330205674732355 21250 133020922506 00450257
15 330205674732355 21250 133020922506 00450258
16 330205674732355 21250 133020922506 00450259
17 330205674732355 21250 133020922506 00450260
18 330205674732355 21250 133020922506 00450261
19 330205674732355 21250 133020922506 00450262
20 330205674732355 21250 133020922506 00450263
21 330205674732355 21250 133020922506 00450264
22 330205674732355 21250 133020922506 00450265
23 132924196905046168 28330 133021023303 02106431
24 132924196905046168 28330 133021023303 02106432
25 132924196905046168 28330 133021023303 02106433
26 132924196905046168 28330 133021023303 02106434
27 132924196905046168 28330 133021023303 02106435
28 132924196905046168 28330 133021023303 02106436
29 132924196905046168 28330 133021023303 02106437
30 132924196905046168 28330 133021023303 02106438
31 132924196905046168 28330 133021023303 02106439
32 132924196905046168 28330 133021023303 02106440
根据表table1要求生成另一张表table2,字段结构xh,nsrsbh,fpzldm,fpdm,fpqh,fpzh 分别代表 序号，纳税人识别号，发票种类代码，发票代码，发票起号，发票终号
序号，纳税人识别号，发票种类代码，  发票代码，      发票起号， 发票终号
1    33020495423019X   21010        133020921103    00909291   00909292
2      330203793010240   21250        133021022506    00035801    00035810
3     330205674732355   21250         133020922506    00450256    00450265
4    132924196905046168 28330       133021023303    02106431   02106440
不知用SQL语句如何实现（ORACLE的），请指教？

Answer 1

SELECT Row_Number()over(ORDER BY 1) 序号, 
NSRSBH 纳税人识别号, FPZLDM 发票种类代码, FPDM 发票代码, 
Min(FPHM) 发票起号,Max(FPHM)发票终号 
FROM table1
GROUP BY NSRSBH, FPZLDM, FPDM

Answer 2

ms sql 的，不知道oracle 的管不管用
--> 测试数据: #tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (XH int,NSRSBH varchar(18),FPZLDM int,FPDM bigint,FPHM varchar(8))
insert into #tb
select 1,'33020495423019X',21010,133020921103,'00909291' union all
select 2,'33020495423019X',21010,133020921103,'00909292' union all
select 3,'330203793010240',21250,133021022506,'00035801' union all
select 4,'330203793010240',21250,133021022506,'00035802' union all
select 5,'330203793010240',21250,133021022506,'00035803' union all
select 6,'330203793010240',21250,133021022506,'00035804' union all
select 7,'330203793010240',21250,133021022506,'00035805' union all
select 8,'330203793010240',21250,133021022506,'00035806' union all
select 9,'330203793010240',21250,133021022506,'00035807' union all
select 10,'330203793010240',21250,133021022506,'00035808' union all
select 11,'330203793010240',21250,133021022506,'00035809' union all
select 12,'330203793010240',21250,133021022506,'00035810' union all
select 13,'330205674732355',21250,133020922506,'00450256' union all
select 14,'330205674732355',21250,133020922506,'00450257' union all
select 15,'330205674732355',21250,133020922506,'00450258' union all
select 16,'330205674732355',21250,133020922506,'00450259' union all
select 17,'330205674732355',21250,133020922506,'00450260' union all
select 18,'330205674732355',21250,133020922506,'00450261' union all
select 19,'330205674732355',21250,133020922506,'00450262' union all
select 20,'330205674732355',21250,133020922506,'00450263' union all
select 21,'330205674732355',21250,133020922506,'00450264' union all
select 22,'330205674732355',21250,133020922506,'00450265' union all
select 23,'132924196905046168',28330,133021023303,'02106431' union all
select 24,'132924196905046168',28330,133021023303,'02106432' union all
select 25,'132924196905046168',28330,133021023303,'02106433' union all
select 26,'132924196905046168',28330,133021023303,'02106434' union all
select 27,'132924196905046168',28330,133021023303,'02106435' union all
select 28,'132924196905046168',28330,133021023303,'02106436' union all
select 29,'132924196905046168',28330,133021023303,'02106437' union all
select 30,'132924196905046168',28330,133021023303,'02106438' union all
select 31,'132924196905046168',28330,133021023303,'02106439' union all
select 32,'132924196905046168',28330,133021023303,'02106440'select row_number()over(order by getdate()) as 序号, 
a.NSRSBH,a.FPZLDM,a.FPDM,a.FPHM as 发票起号,min(b.FPHM) as 发票终号
from
(
select NSRSBH,FPZLDM,FPDM,FPHM from #tb t 
where not exists(select * from #tb where NSRSBH=t.NSRSBH and FPHM<t.FPHM)
)a,
(
select NSRSBH,FPZLDM,FPDM,FPHM from #tb t 
where not exists(select * from #tb where NSRSBH=t.NSRSBH and FPHM>t.FPHM)
)b
where a.NSRSBH=b.NSRSBH and a.FPHM<b.FPHM
group by a.NSRSBH,a.FPZLDM,a.FPDM,a.FPHM
 序号                   NSRSBH             FPZLDM      FPDM                 发票起号     发票终号
-------------------- ------------------ ----------- -------------------- -------- --------
1                    132924196905046168 28330       133021023303         02106431 02106440
2                    330203793010240    21250       133021022506         00035801 00035810
3                    33020495423019X    21010       133020921103         00909291 00909292
4                    330205674732355    21250       133020922506         00450256 00450265(4 行受影响)

Answer 3

--借用楼上的测试数据：    
WITH table1 AS(
select 1 XH,'33020495423019X'NSRSBH,21010 FPZLDM,133020921103 FPDM,'00909291' FPHM FROM dual union all
select 2,'33020495423019X',21010,133020921103,'00909292' FROM dual union all
select 3,'330203793010240',21250,133021022506,'00035801' FROM dual union all
select 4,'330203793010240',21250,133021022506,'00035802' FROM dual union all
select 5,'330203793010240',21250,133021022506,'00035803' FROM dual union all
select 6,'330203793010240',21250,133021022506,'00035804' FROM dual union all
select 7,'330203793010240',21250,133021022506,'00035805' FROM dual union all
select 8,'330203793010240',21250,133021022506,'00035806' FROM dual union all
select 9,'330203793010240',21250,133021022506,'00035807' FROM dual union all
select 10,'330203793010240',21250,133021022506,'00035808' FROM dual union all
select 11,'330203793010240',21250,133021022506,'00035809' FROM dual union all
select 12,'330203793010240',21250,133021022506,'00035810' FROM dual union all
select 13,'330205674732355',21250,133020922506,'00450256' FROM dual union all
select 14,'330205674732355',21250,133020922506,'00450257' FROM dual union all
select 15,'330205674732355',21250,133020922506,'00450258' FROM dual union all
select 16,'330205674732355',21250,133020922506,'00450259' FROM dual union all
select 17,'330205674732355',21250,133020922506,'00450260' FROM dual union all
select 18,'330205674732355',21250,133020922506,'00450261' FROM dual union all
select 19,'330205674732355',21250,133020922506,'00450262' FROM dual union all
select 20,'330205674732355',21250,133020922506,'00450263' FROM dual union all
select 21,'330205674732355',21250,133020922506,'00450264' FROM dual union all
select 22,'330205674732355',21250,133020922506,'00450265' FROM dual union all
select 23,'132924196905046168',28330,133021023303,'02106431' FROM dual union all
select 24,'132924196905046168',28330,133021023303,'02106432' FROM dual union all
select 25,'132924196905046168',28330,133021023303,'02106433' FROM dual union all
select 26,'132924196905046168',28330,133021023303,'02106434' FROM dual union all
select 27,'132924196905046168',28330,133021023303,'02106435' FROM dual union all
select 28,'132924196905046168',28330,133021023303,'02106436' FROM dual union all
select 29,'132924196905046168',28330,133021023303,'02106437' FROM dual union all
select 30,'132924196905046168',28330,133021023303,'02106438' FROM dual union all
select 31,'132924196905046168',28330,133021023303,'02106439' FROM dual union all
select 32,'132924196905046168',28330,133021023303,'02106440' FROM dual
)
SELECT Row_Number()over(ORDER BY FPZLDM,NSRSBH) 序号, 
NSRSBH 纳税人识别号, FPZLDM 发票种类代码, FPDM 发票代码, 
Min(FPHM) 发票起号,Max(FPHM)发票终号 
FROM table1
GROUP BY NSRSBH, FPZLDM, FPDM
--结果：
序号  纳税人识别号         发票种类代码   发票代码       发票起号   发票终号
------------------------------------------------------------------------------------
1     33020495423019X      21010          133020921103   00909291   00909292
2     330203793010240      21250          133021022506   00035801   00035810
3     330205674732355      21250          133020922506   00450256   00450265
4     132924196905046168   28330          133021023303   02106431   02106440

Answer 4

直接用min max会存在漏洞，对于某些数据是不适用的。
适用start with效率也是个问题。哈，我再想想。

Answer 5

他是要发票的起号和终号，
应该是最小发票号和最大发票号
如果发票号中存在断号情况，用start with 就不行了。

Answer 6

SQL> select rownum,a.nsrsbh,a.fpzldm,a.fpdm,a.fphm,
  2         (select max(c.fphm) from table1 c
  3           where a.nsrsbh = c.nsrsbh  and a.fpzldm = c.fpzldm and a.fpdm = c.fpdm
  4             and (c.fphm - a.fphm + 1) =
  5                 (select count(*) from table1 d
  6                   where c.nsrsbh = d.nsrsbh and c.fpzldm = d.fpzldm and c.fpdm = d.fpdm and d.fphm between a.fphm and c.fphm))
  7    from table1 a
  8   where not exists (select 1 from table1 b
  9           where a.nsrsbh = b.nsrsbh and a.fpzldm = b.fpzldm  and a.fpdm = b.fpdm and a.fphm = b.fphm + 1);
 
    ROWNUM NSRSBH                 FPZLDM       FPDM FPHM     (SELECTMAX(C.FPHM)FROMTABLE1CW
---------- ------------------ ---------- ---------- -------- ------------------------------
         1 330203793010240         21250 1330210225 00035801 00035810
         2 330205674732355         21250 1330209225 00450256 00450265
         3 132924196905046168      28330 1330210233 02106431 02106440
         4 33020495423019X         21010 1330209211 00909291 00909292
 
SQL> 

Answer 7

start with 的可以限定connect by 的条件为prior fphm=fphm-1避免跳号被合成一条数据。

Answer 8

下面是两种写法的区别，楼主根据自己需要选用吧。
SQL> select * from table1;
 
        XH NSRSBH                 FPZLDM       FPDM FPHM
---------- ------------------ ---------- ---------- --------
         1 33020495423019X         21010 1330209211 00909291
         2 33020495423019X         21010 1330209211 00909292
         3 330203793010240         21250 1330210225 00035801
         4 330203793010240         21250 1330210225 00035802
         5 330203793010240         21250 1330210225 00035803
         6 330203793010240         21250 1330210225 00035804
         7 330203793010240         21250 1330210225 00035805
         8 330203793010240         21250 1330210225 00035806
         9 330203793010240         21250 1330210225 00035807
        10 330203793010240         21250 1330210225 00035808
        11 330203793010240         21250 1330210225 00035809
        12 330203793010240         21250 1330210225 00035810
        13 330205674732355         21250 1330209225 00450256
        14 330205674732355         21250 1330209225 00450257
        15 330205674732355         21250 1330209225 00450258
        16 330205674732355         21250 1330209225 00450259
        17 330205674732355         21250 1330209225 00450260
        18 330205674732355         21250 1330209225 00450261
        19 330205674732355         21250 1330209225 00450262
        20 330205674732355         21250 1330209225 00450263
 
        XH NSRSBH                 FPZLDM       FPDM FPHM
---------- ------------------ ---------- ---------- --------
        21 330205674732355         21250 1330209225 00450264
        22 330205674732355         21250 1330209225 00450265
        23 132924196905046168      28330 1330210233 02106431
        24 132924196905046168      28330 1330210233 02106432
        25 132924196905046168      28330 1330210233 02106433
        26 132924196905046168      28330 1330210233 02106434
        27 132924196905046168      28330 1330210233 02106435
        28 132924196905046168      28330 1330210233 02106436
        29 132924196905046168      28330 1330210233 02106437
        30 132924196905046168      28330 1330210233 02106438
        31 132924196905046168      28330 1330210233 02106439
        32 132924196905046168      28330 1330210233 02106440
        32 132924196905046168      28330 1330210233 02106442 --新增数据
 
33 rows selected
 
SQL> 
SQL> select rownum,a.nsrsbh,a.fpzldm,a.fpdm,a.fphm,
  2         (select max(c.fphm) from table1 c
  3           where a.nsrsbh = c.nsrsbh  and a.fpzldm = c.fpzldm and a.fpdm = c.fpdm
  4             and (c.fphm - a.fphm + 1) =
  5                 (select count(*) from table1 d
  6                   where c.nsrsbh = d.nsrsbh and c.fpzldm = d.fpzldm and c.fpdm = d.fpdm and d.fphm between a.fphm and c.fphm))
  7    from table1 a
  8   where not exists (select 1 from table1 b
  9           where a.nsrsbh = b.nsrsbh and a.fpzldm = b.fpzldm  and a.fpdm = b.fpdm and a.fphm = b.fphm + 1);
 
    ROWNUM NSRSBH                 FPZLDM       FPDM FPHM     (SELECTMAX(C.FPHM)FROMTABLE1CW
---------- ------------------ ---------- ---------- -------- ------------------------------
         1 330203793010240         21250 1330210225 00035801 00035810
         2 330205674732355         21250 1330209225 00450256 00450265
         3 132924196905046168      28330 1330210233 02106431 02106440
         4 33020495423019X         21010 1330209211 00909291 00909292
         5 132924196905046168      28330 1330210233 02106442 02106442
 
SQL> 
SQL> SELECT Row_Number()over(ORDER BY FPZLDM,NSRSBH) 序号,
  2  NSRSBH 纳税人识别号, FPZLDM 发票种类代码, FPDM 发票代码,
  3  Min(FPHM) 发票起号,Max(FPHM)发票终号
  4  FROM table1
  5  GROUP BY NSRSBH, FPZLDM, FPDM
  6  ;
 
        序号 纳税人识别号             发票种类代码       发票代码 发票起号 发票终号
---------- ------------------ ------------ ---------- -------- --------
         1 33020495423019X           21010 1330209211 00909291 00909292
         2 330203793010240           21250 1330210225 00035801 00035810
         3 330205674732355           21250 1330209225 00450256 00450265
         4 132924196905046168        28330 1330210233 02106431 02106442
 
SQL> 
--可以建个联合索引来提高查询效率
create index idx_table1_1 on table1(nsrsbh,fpzldm,fpdm);

Answer 9

如果表一中FPHM(发票号码)存在号码中断现象,
XH NSRSBH         FPZLDM   FPDM      FPHM1 33020495423019X 21010 133020921103 00909291
2 33020495423019X 21010 133020921103 00909292
3 33020495423019X 21010 133020921103 00909295
3 33020495423019X 21010 133020921103 00909298
4 33020495423019X 21010 133020921103 00909299
.......
生成
序号，纳税人识别号，发票种类代码， 发票代码， 发票起号， 发票终号
1 33020495423019X 21010 133020921103 00909291 00909292
2 33020495423019X 21010 133020921103 00909295 00909295
3 33020495423019X 21010 133020921103 00909298 00909299
又如何实现?

Answer 10

SELECT Row_Number()over(ORDER BY FPZLDM,NSRSBH) 序号, 
NSRSBH 纳税人识别号, FPZLDM 发票种类代码, FPDM 发票代码, 
Min(FPHM) 发票起号,Max(FPHM)发票终号 
FROM(
SELECT xh,NSRSBH ,FPZLDM ,FPDM ,FPHM,FPHM-ROWNUM rn FROM table1
)
GROUP BY NSRSBH, FPZLDM, FPDM,rn

Answer 11

select nsrsbh,fpzldm,fpdm,min(fphm) fpqh,max(fphm) fpzh,
row_number() over(order by nsrsbh,fpzldm,fpdm) xh
from tb
group by nsrsbh,fpzldm,fpdm

Answer 12

的加个标识
--
with tb AS(
select 1 XH,'33020495423019X'NSRSBH,'21010' FPZLDM,'133020921103' FPDM,'00909291' FPHM FROM dual union all
select 2,'33020495423019X','21010','133020921103','00909292' FROM dual union all
select 3,'33020495423019X','21010','133020921103','00909295'FROM dual union all
select 3,'33020495423019X','21010','133020921103','00909298'FROM dual union all
select 4,'33020495423019X','21010','133020921103','00909299'FROM dual
)
select nsrsbh,fpzldm,fpdm,min(FPHM) fpqh,max(FPHM) fpzh,row_number() over(order by 1) xh
from (select nsrsbh,fpzldm,fpdm,FPHM,FPHM-rownum flag
from tb)
group by nsrsbh,fpzldm,fpdm,flag--运行
SQL> 
SQL> with tb AS(
  2  select 1 XH,'33020495423019X'NSRSBH,'21010' FPZLDM,'133020921103' FPDM,'00909291' FPHM FROM dual union all
  3  select 2,'33020495423019X','21010','133020921103','00909292' FROM dual union all
  4  select 3,'33020495423019X','21010','133020921103','00909295'FROM dual union all
  5  select 3,'33020495423019X','21010','133020921103','00909298'FROM dual union all
  6  select 4,'33020495423019X','21010','133020921103','00909299'FROM dual
  7  )
  8  select nsrsbh,fpzldm,fpdm,min(FPHM) fpqh,max(FPHM) fpzh,row_number() over(order by 1) xh
  9  from (select nsrsbh,fpzldm,fpdm,FPHM,FPHM-rownum flag
 10  from tb)
 11  group by nsrsbh,fpzldm,fpdm,flag
 12  
SQL> /
 
NSRSBH          FPZLDM FPDM         FPQH     FPZH             XH
--------------- ------ ------------ -------- -------- ----------
33020495423019X 21010  133020921103 00909295 00909295          1
33020495423019X 21010  133020921103 00909291 00909292          2
33020495423019X 21010  133020921103 00909298 00909299          3