SQL> select * from a;GROUPID PARENTID ---------- ---------- 1 2 1 3 1 4 1 5 2 6 36 行 已选择SQL> SQL> SELECT * 2 FROM (SELECT LEVEL Q, GROUPID, PARENTID 3 FROM A 4 START WITH GROUPID IN (2, 3, 5) 5 CONNECT BY GROUPID = PRIOR PARENTID) 6 WHERE Q = 1 7 / Q GROUPID PARENTID ---------- ---------- ---------- 1 2 1 1 3 1 1 5 2SQL>
如果groupid in (2,5,6),应该得到什么?
SQL> SELECT * FROM( 2 SELECT T.*, RANK() OVER(ORDER BY PARENTID ) RK FROM 3 (SELECT Q, GROUPID,PARENTID 4 FROM (SELECT LEVEL Q, GROUPID, PARENTID 5 FROM A 6 START WITH GROUPID IN (1,5) 7 CONNECT BY GROUPID = PRIOR PARENTID) 8 WHERE Q = 1) T 9 ) P WHERE P.RK=1 10 / Q GROUPID PARENTID RK ---------- ---------- ---------- ---------- 1 5 2 1SQL> SQL> SELECT * FROM( 2 SELECT T.*, RANK() OVER(ORDER BY PARENTID ) RK FROM 3 (SELECT Q, GROUPID,PARENTID 4 FROM (SELECT LEVEL Q, GROUPID, PARENTID 5 FROM A 6 START WITH GROUPID IN (2,3,5) 7 CONNECT BY GROUPID = PRIOR PARENTID) 8 WHERE Q = 1) T 9 ) P WHERE P.RK=1 10 / Q GROUPID PARENTID RK ---------- ---------- ---------- ---------- 1 2 1 1 1 3 1 1SQL>
稍做改动。要判断为parent为空的情况。要不然结果不正确。 改正如下: SQL> SELECT * FROM( 2 SELECT T.*, RANK() OVER(ORDER BY PARENTID ) RK FROM 3 (SELECT Q, GROUPID,PARENTID 4 FROM (SELECT LEVEL Q, GROUPID, nvl(PARENTID,'0') PARENTID 5 FROM A 6 START WITH GROUPID IN (1,5) 7 CONNECT BY GROUPID = PRIOR PARENTID) 8 WHERE Q = 1) T 9 ) P WHERE P.RK=1 10 / Q GROUPID PARENTID RK ---------- ---------- ---------- ---------- 1 1 0 1SQL> SQL> SELECT * FROM( 2 SELECT T.*, RANK() OVER(ORDER BY PARENTID ) RK FROM 3 (SELECT Q, GROUPID,PARENTID 4 FROM (SELECT LEVEL Q, GROUPID, nvl(PARENTID,'0') PARENTID 5 FROM A 6 START WITH GROUPID IN (2,3,5) 7 CONNECT BY GROUPID = PRIOR PARENTID) 8 WHERE Q = 1) T 9 ) P WHERE P.RK=1 10 / Q GROUPID PARENTID RK ---------- ---------- ---------- ---------- 1 2 1 1 1 3 1 1SQL>
稍做改动。要判断为parent为空的情况。要不然结果不正确。 改正如下: SQL> SELECT * FROM( 2 SELECT T.*, RANK() OVER(ORDER BY PARENTID ) RK FROM 3 (SELECT Q, GROUPID,PARENTID 4 FROM (SELECT LEVEL Q, GROUPID, nvl(PARENTID,'0') PARENTID 5 FROM A 6 START WITH GROUPID IN (1,5) 7 CONNECT BY GROUPID = PRIOR PARENTID) 8 WHERE Q = 1) T 9 ) P WHERE P.RK=1 10 / Q GROUPID PARENTID RK ---------- ---------- ---------- ---------- 1 1 0 1SQL> SQL> SELECT * FROM( 2 SELECT T.*, RANK() OVER(ORDER BY PARENTID ) RK FROM 3 (SELECT Q, GROUPID,PARENTID 4 FROM (SELECT LEVEL Q, GROUPID, nvl(PARENTID,'0') PARENTID 5 FROM A 6 START WITH GROUPID IN (2,3,5) 7 CONNECT BY GROUPID = PRIOR PARENTID) 8 WHERE Q = 1) T 9 ) P WHERE P.RK=1 10 / Q GROUPID PARENTID RK ---------- ---------- ---------- ---------- 1 2 1 1 1 3 1 1SQL>
---------- ----------
1
2 1
3 1
4 1
5 2
6 36 行 已选择SQL>
SQL> SELECT *
2 FROM (SELECT LEVEL Q, GROUPID, PARENTID
3 FROM A
4 START WITH GROUPID IN (2, 3, 5)
5 CONNECT BY GROUPID = PRIOR PARENTID)
6 WHERE Q = 1
7 / Q GROUPID PARENTID
---------- ---------- ----------
1 2 1
1 3 1
1 5 2SQL>
2 SELECT T.*, RANK() OVER(ORDER BY PARENTID ) RK FROM
3 (SELECT Q, GROUPID,PARENTID
4 FROM (SELECT LEVEL Q, GROUPID, PARENTID
5 FROM A
6 START WITH GROUPID IN (1,5)
7 CONNECT BY GROUPID = PRIOR PARENTID)
8 WHERE Q = 1) T
9 ) P WHERE P.RK=1
10 / Q GROUPID PARENTID RK
---------- ---------- ---------- ----------
1 5 2 1SQL>
SQL> SELECT * FROM(
2 SELECT T.*, RANK() OVER(ORDER BY PARENTID ) RK FROM
3 (SELECT Q, GROUPID,PARENTID
4 FROM (SELECT LEVEL Q, GROUPID, PARENTID
5 FROM A
6 START WITH GROUPID IN (2,3,5)
7 CONNECT BY GROUPID = PRIOR PARENTID)
8 WHERE Q = 1) T
9 ) P WHERE P.RK=1
10 / Q GROUPID PARENTID RK
---------- ---------- ---------- ----------
1 2 1 1
1 3 1 1SQL>
改正如下:
SQL> SELECT * FROM(
2 SELECT T.*, RANK() OVER(ORDER BY PARENTID ) RK FROM
3 (SELECT Q, GROUPID,PARENTID
4 FROM (SELECT LEVEL Q, GROUPID, nvl(PARENTID,'0') PARENTID
5 FROM A
6 START WITH GROUPID IN (1,5)
7 CONNECT BY GROUPID = PRIOR PARENTID)
8 WHERE Q = 1) T
9 ) P WHERE P.RK=1
10 / Q GROUPID PARENTID RK
---------- ---------- ---------- ----------
1 1 0 1SQL>
SQL> SELECT * FROM(
2 SELECT T.*, RANK() OVER(ORDER BY PARENTID ) RK FROM
3 (SELECT Q, GROUPID,PARENTID
4 FROM (SELECT LEVEL Q, GROUPID, nvl(PARENTID,'0') PARENTID
5 FROM A
6 START WITH GROUPID IN (2,3,5)
7 CONNECT BY GROUPID = PRIOR PARENTID)
8 WHERE Q = 1) T
9 ) P WHERE P.RK=1
10 / Q GROUPID PARENTID RK
---------- ---------- ---------- ----------
1 2 1 1
1 3 1 1SQL>
改正如下:
SQL> SELECT * FROM(
2 SELECT T.*, RANK() OVER(ORDER BY PARENTID ) RK FROM
3 (SELECT Q, GROUPID,PARENTID
4 FROM (SELECT LEVEL Q, GROUPID, nvl(PARENTID,'0') PARENTID
5 FROM A
6 START WITH GROUPID IN (1,5)
7 CONNECT BY GROUPID = PRIOR PARENTID)
8 WHERE Q = 1) T
9 ) P WHERE P.RK=1
10 / Q GROUPID PARENTID RK
---------- ---------- ---------- ----------
1 1 0 1SQL>
SQL> SELECT * FROM(
2 SELECT T.*, RANK() OVER(ORDER BY PARENTID ) RK FROM
3 (SELECT Q, GROUPID,PARENTID
4 FROM (SELECT LEVEL Q, GROUPID, nvl(PARENTID,'0') PARENTID
5 FROM A
6 START WITH GROUPID IN (2,3,5)
7 CONNECT BY GROUPID = PRIOR PARENTID)
8 WHERE Q = 1) T
9 ) P WHERE P.RK=1
10 / Q GROUPID PARENTID RK
---------- ---------- ---------- ----------
1 2 1 1
1 3 1 1SQL>