假设表名T,字段为ID 、A select ID,MIN(A),MAX(A) from T T1 START WITH NOT EXISTS(SELECT 1 FROM T WHERE A=T1.A-1 AND ID=T1.ID) CONNECT BY PRIOR A=A-1 AND PRIOR ID=ID GROUP BY CONNECT_BY_ROOT(A),ID
你的流水号是字符串类型,可能连接的时候需要to_number处理一下,具体自己改改吧
谢谢大侠的及时回复现在参考并修改字段后如下SQL select MIN(C_REF_NO),MAX(C_REF_NO) from TXIN_TRX_REFUSING T1 START WITH NOT EXISTS(SELECT 1 FROM TXIN_TRX_REFUSING WHERE to_number(C_REF_NO)=to_number(T1.C_REF_NO)-1 AND C_REF_NAME=T1.C_REF_NAME) CONNECT BY PRIOR to_number(C_REF_NO)=to_number(C_REF_NO)-1 AND PRIOR C_REF_NAME=C_REF_NAME GROUP BY CONNECT_BY_ROOT(C_REF_NO),C_REF_NAME显示的结果如下但是还想查询出对应的 C_REF_NAME字段,也可能还有一些流水号表里的其它字段,再次麻烦大侠,请指教
select ID,MIN(A),MAX(A) from T T1
START WITH NOT EXISTS(SELECT 1 FROM T WHERE A=T1.A-1 AND ID=T1.ID)
CONNECT BY PRIOR A=A-1 AND PRIOR ID=ID
GROUP BY CONNECT_BY_ROOT(A),ID
select MIN(C_REF_NO),MAX(C_REF_NO) from TXIN_TRX_REFUSING T1
START WITH NOT EXISTS(SELECT 1 FROM TXIN_TRX_REFUSING WHERE to_number(C_REF_NO)=to_number(T1.C_REF_NO)-1 AND C_REF_NAME=T1.C_REF_NAME)
CONNECT BY PRIOR to_number(C_REF_NO)=to_number(C_REF_NO)-1 AND PRIOR C_REF_NAME=C_REF_NAME
GROUP BY CONNECT_BY_ROOT(C_REF_NO),C_REF_NAME显示的结果如下但是还想查询出对应的 C_REF_NAME字段,也可能还有一些流水号表里的其它字段,再次麻烦大侠,请指教