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完了,今天星期五了,下等四点了马上,期待下班了,就今天不咋忙,哦耶,周末嗨皮,嘿嘿嘿~

posted @ 2013-06-21 16:00 medci(卡樂江) 阅读(...) 评论(...) 编辑 收藏