一个高级SQL语句问题

zj511025 · 发表于 2006-8-28 12:00

假设有一张表，A和B字段都是NUMBER，
A          B
1          2
2          3
3          4
4
有这样一些数据
现在想用一条SQL语句，查询出这样的数据
1－》2－》3—》4
就是说，A和B的数据表示一种连接的关系，现在想通过A的一个值，去查询A所对应的B值，直到B为NULL为止，不知道这个SQL语句怎么写？请教高手！谢谢

mychary · 发表于 2006-8-28 13:00

connect by

淡若煙飛 · 发表于 2006-8-28 13:19

最初由 mychary 发布
[B]connect by [/B]

SQL> select * from t;

      A       B
---------- ----------
      1       2
      2       3
      3       4
      4

SQL>
SQL> SELECT SUBSTR(MAX(SYS_CONNECT_BY_PATH(A.A, '->')), 3) RESULT
  2 FROM T A
  3 START WITH A.A = 1
  4  CONNECT BY A.A = PRIOR B
  5  /

RESULT
--------------------
1->2->3->4

SQL>

淡若煙飛 · 发表于 2006-8-28 13:23

如果列的值不为数字型时，可以这样！

SQL> select * from t;

A  B
-- --
A  B
B  F
C  D
F  W
W

SQL>
SQL> SELECT SUBSTR(RESULT, 3) RESULT
  2 FROM (SELECT LENGTH(SYS_CONNECT_BY_PATH(A.A, '->')) LEN,
  3                SYS_CONNECT_BY_PATH(A.A, '->') RESULT
  4          FROM T A
  5          START WITH A.A = 'A'
  6       CONNECT BY A.A = PRIOR B
  7          ORDER BY LEN DESC)
  8 WHERE ROWNUM = 1
  9  /

RESULT
--------------------
A->B->F->W

SQL>

zhouwf0726 · 发表于 2006-8-28 13:25

CREATE TABLE TEST(COL1 NUMBER(18,0),COL2 NUMBER(18,0));

INSERT INTO TEST VALUES(1,2);
INSERT INTO TEST VALUES(2,3);
INSERT INTO TEST VALUES(3,4);
INSERT INTO TEST VALUES(4,NULL);

INSERT INTO TEST VALUES(5,6);
INSERT INTO TEST VALUES(6,7);
INSERT INTO TEST VALUES(7,8);
INSERT INTO TEST VALUES(8,NULL);

INSERT INTO TEST VALUES(9,10);
INSERT INTO TEST VALUES(10,NULL);

select max(col) from(
select SUBSTR(col,1,CASE WHEN INSTR(col,'->')>0 THEN INSTR(col,'->') - 1 ELSE LENGTH(col) END) FLAG,col from(
select ltrim(sys_connect_by_path(col1,'->'),'->') col from (
select col1,col2,CASE WHEN LAG(COL2,1,NULL) OVER(ORDER BY ROWNUM) IS NULL THEN 1 ELSE 0 END FLAG
from test
)
start with flag=1 connect by col1=prior col2
)
)
group by flag
;

MAX(COL)
--------------------------------------------------------------------------------
1->2->3->4
5->6->7->8
9->10

zj511025 · 发表于 2006-8-28 13:30

多些两位高手

[原创] 一个高级SQL语句问题