ORACLE先找爹，再找儿子（层次查询的高级应用）

今天帮同事看一个报表，一个foreigner整的，看了两个小时那位素未谋面的前辈写的SQL,终于明白此报表是啥意思了。

说得通俗易懂点就是给出一个RecordID，根据此ID找出最顶级的爹，再根据此爹，找出所有的儿子、孙子......找到没有子为止。还用了10个子报表，

可能他喜欢子报表这个东西，后来被我改得没有子报表了。

一个表B1permit，一个表XAPP2REF（记录父子关系的），前辈写的是此两表个不停止的LEFT JOIN ,连接N多次，好吧，他也有节制的，只连接了五级。

但是性能那是相当的差。因为也没有需求，只是通过看SQL看他想要什么，后来大概的改了一下，400多行的SQL也瞬间短了很多，性能也上去了不少，

没详细看性能的差异，但是起码跑起来不会把报表工具卡SHI了。把SQL贴上来，其实很简单的说

---第一段找出最顶级的爹TMP_C

WITH TMP_C AS
(SELECT A.B1_ALT_ID, level CC
FROM B1PERMIT A, XAPP2REF B
WHERE A.SERV_PROV_CODE = B.SERV_PROV_CODE(+)
AND A.B1_PER_ID1 = B.B1_PER_ID1(+)
AND A.B1_PER_ID2 = B.B1_PER_ID2(+)
AND A.B1_PER_ID3 = B.B1_PER_ID3(+)
AND A.SERV_PROV_CODE = 'KINGCO'
START WITH A.B1_ALT_ID = 'L12SI003'
CONNECT BY A.B1_PER_Id3 = PRIOR B.B1_MASTER_ID3
AND A.B1_PER_ID1 = PRIOR B.B1_MASTER_ID1
AND A.B1_PER_ID2 = PRIOR B.B1_MASTER_ID2),
TMP_D AS  --第二段是根据最顶级的爹找下面所有 的孩子

(SELECT DISTINCT A.B1_ALT_ID B1_ALT_ID,

level CC,
A.B1_PER_ID1,
A.B1_PER_ID2,
A.B1_PER_ID3,
A.SERV_PROV_CODE,
B1_PER_GROUP,
B1_PER_TYPE,
B1_PER_SUB_TYPE,
B1_PER_CATEGORY,
B1_APPL_STATUS
FROM B1PERMIT A, XAPP2REF B
WHERE A.SERV_PROV_CODE = B.SERV_PROV_CODE(+)
AND A.B1_PER_ID1 = B.B1_MASTER_ID1(+)
AND A.B1_PER_ID2 = B.B1_MASTER_ID2(+)
AND A.B1_PER_ID3 = B.B1_MASTER_ID3(+)
AND A.SERV_PROV_CODE = 'KINGCO'
START WITH ((A.B1_ALT_ID IN
(SELECT B1_ALT_ID
FROM TMP_C
WHERE CC = (SELECT MAX(CC) LEV FROM TMP_C))) OR
(('{?RecNum}' IS NULL OR '{?RecNum}' = '') AND NOT EXISTS
(SELECT 'X'
FROM XAPP2REF TOP, XAPP2REF B
WHERE TOP.SERV_PROV_CODE = B.SERV_PROV_CODE
AND TOP.B1_PER_ID1 = B.B1_PER_ID1
AND TOP.B1_PER_ID2 = B.B1_PER_ID2
AND TOP.B1_PER_ID3 = B.B1_PER_ID3
AND TOP.REC_STATUS = B.REC_STATUS)))
CONNECT BY PRIOR B.B1_PER_Id3 = A.B1_PER_ID3
AND PRIOR B.B1_PER_ID1 = A.B1_PER_ID1
AND PRIOR B.B1_PER_ID2 = A.B1_PER_ID2)

--正文来了，因为去掉了其中的子报表，所以还有一些别的相关联的表，并且限制了一个level<7
SELECT B1_ALT_ID,
CC,
A.B1_PER_GROUP,
A.B1_PER_TYPE,
A.B1_PER_SUB_TYPE,
A.B1_PER_CATEGORY,
A.B1_APPL_STATUS,
B.TOTAL_FEE,
B.TOTAL_PAY,
B.BALANCE
FROM TMP_D A
INNER JOIN BPERMIT_DETAIL B
ON A.SERV_PROV_CODE = B.SERV_PROV_CODE
AND A.B1_PER_ID1 = B.B1_PER_ID1
AND A.B1_PER_ID2 = B.B1_PER_ID2
AND A.B1_PER_ID3 = B.B1_PER_ID3
WHERE CC < 7
ORDER BY CC, B1_ALT_ID

 

好了，PO完了，今天星期五了，下等四点了马上，期待下班了，就今天不咋忙，哦耶，周末嗨皮，嘿嘿嘿~