如何sql查询出连续号码段

onefours

mark

cj402444206

SELECT FPHM,MIN(KSHM),MAX(KSHM) FROM T HAVING MAX(KSHM)-MIN(KSHM)>=1 GROUP BY FPHM,KSHM-ROWNUM;

或者

SELECT fphm, MIN(kshm),MAX(kshm) FROM
  ( SELECT  fphm, kshm, kshm-ROW_NUMBER() OVER (PARTITION BY fphm ORDER BY kshm ) AS RN FROM T )
   GROUP BY fphm,RN;

基本思想就是：一组数减去一组连续的数,如果得到的值是定值,则减数也是一组连续的数。

[ 本帖最后由 cj402444206 于 2010-8-10 16:52 编辑 ]

lastwinner

原帖由 cj402444206 于 10-8-10 16:46 发表
SELECT FPHM,MIN(KSHM),MAX(KSHM) FROM T HAVING MAX(KSHM)-MIN(KSHM)>=1 GROUP BY FPHM,KSHM-ROWNUM;

或者

SELECT fphm, MIN(kshm),MAX(kshm) FROM
  ( SELECT  fphm, kshm, kshm-ROW_NUMBER() OVER (PARTITION BY fphm ORDER BY kshm ) AS RN FROM T )
   GROUP BY fphm,RN;

基本思想就是：一组数减去一组连续的数,如果得到的值是定值,则减数也是一组连续的数。

老帖就不要顶了
顶前也要看看前面的回帖是不是已经有类似的思路了

qq28289877

SQL2000 查询语句如何写？

sych_888

mark.........

zhangtuo2012

hmxxyy 发表于 2005-4-30 00:54
还真引出玉来了

SQL> select b.fphm,min(b.kshm),max(b.kshm)

lymmxz

学习了。。。真是不错

thurston1982

mark

szoraclefans

hmxxyy 发表于 2005-4-30 00:54
还真引出玉来了

SQL> select b.fphm,min(b.kshm),max(b.kshm)

这种方法太巧妙了
在学习的过程中，突然想到如果有重复数据会怎样？ 所以做了点小改动。其实就是在最里层加了一个group by

CREATE TABLE t_sequence (ID INT,NUM INT);

INSERT INTO t_sequence VALUES(1,1);
INSERT INTO t_sequence VALUES(1,2);
INSERT INTO t_sequence VALUES(1,3);
INSERT INTO t_sequence VALUES(1,5);
INSERT INTO t_sequence VALUES(1,6);
INSERT INTO t_sequence VALUES(1,8);
INSERT INTO t_sequence VALUES(2,1);
INSERT INTO t_sequence VALUES(2,3);
INSERT INTO t_sequence VALUES(3,4);
INSERT INTO t_sequence VALUES(3,4);
INSERT INTO t_sequence VALUES(3,5);

SELECT * FROM t_sequence;

SELECT ID, MIN(num),MAX(num) FROM (
SELECT ID,num, to_number(num-Rownum)cc  FROM
(SELECT * FROM t_sequence GROUP BY ID,num ORDER BY ID,num ) a) a
GROUP BY ID,cc ORDER BY ID

peerless_du

收藏！
牛人很多啊