oracle 一次查询语句
select E3.ID AS ID,
E1.SUBSCRIBENAME AS SUBSCRIBENAME,
E5.ALEVELID AS ALEVELID,
E4.ISNOTIFY AS ISNOTIFY,
E4.EMAILFORMAT AS EMAILFORMAT,
E4.EMAILADDR AS EMAILADDR,
E1.ATYPEID AS ATYPEID,
E2.ASUBTYPEID
from
(
SELECT SUBSCRIBENAME,
TRANSLATE(LTRIM(text, '/'), '*/', '*,') ATYPEID
FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY SUBSCRIBENAME ORDER BY SUBSCRIBENAME, lvl DESC) rn,
SUBSCRIBENAME,
text
FROM (
SELECT SUBSCRIBENAME,
LEVEL lvl,
SYS_CONNECT_BY_PATH(ATYPEID, '/') text
FROM (
SELECT D1.SUBSCRIBENAME AS SUBSCRIBENAME,
D1.ATYPEID as ATYPEID,
ROW_NUMBER() OVER(
PARTITION BY D1.SUBSCRIBENAME ORDER BY D1.SUBSCRIBENAME, D1.ATYPEID
) x
FROM
(
select C1.Subscribename,
C2.ATYPEID
from alert_subscribe_config C1,
(
SELECT B1.ID AS ID,
B1.ATYPEID AS ATYPEID
FROM
(

SELECT ID,
max(substr(ATYPEID, 2)) ATYPEID
FROM (
SELECT ID, sys_connect_by_path(ATYPEID, ',') ATYPEID
FROM (
SELECT ID,
ATYPEID,
ID || rn rchild,
ID || (rn - 1) rfather
FROM (
SELECT　A2.ID AS ID,
A2.ATYPEID AS ATYPEID,
row_number() over(
PARTITION BY A2.ID ORDER BY A2.ATYPEID
) rn
FROM 
(
SELECT A.ID,  CASE  WHEN A.ATYPEID = 0 THEN '阀值告警'  WHEN A.ATYPEID = 2 THEN   '业务异动告警'  ELSE  '阀值告警'  END ATYPEID   FROM ALERT_SUBSCRIBE_SUB_CONFIG A
) A2
)
)
CONNECT BY PRIOR rchild = rfather
START WITH rfather LIKE '%0'
)
GROUP BY ID     
)B1
)C2
WHERE C1.SUB_ID = C2.ID
) D1

ORDER BY D1.SUBSCRIBENAME, D1.ATYPEID
) a
CONNECT BY SUBSCRIBENAME = PRIOR SUBSCRIBENAME
AND x - 1 = PRIOR x
)
)
WHERE rn = 1
ORDER BY SUBSCRIBENAME
)E1,
(
SELECT SUBSCRIBENAME,
TRANSLATE(LTRIM(text, '/'), '*/', '*,') ASUBTYPEID
FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY SUBSCRIBENAME ORDER BY SUBSCRIBENAME, lvl DESC) rn,
SUBSCRIBENAME,
text
FROM (
SELECT SUBSCRIBENAME,
LEVEL lvl,
SYS_CONNECT_BY_PATH(ASUBTYPEID, '/') text
FROM (
SELECT D1.SUBSCRIBENAME AS SUBSCRIBENAME,
D1.ASUBTYPEID as ASUBTYPEID,
ROW_NUMBER() OVER(
PARTITION BY D1.SUBSCRIBENAME ORDER BY D1.SUBSCRIBENAME, D1.ASUBTYPEID
) x
FROM
(
select C1.Subscribename,
C2.ASUBTYPEID
from alert_subscribe_config C1,
(
SELECT B2.ID AS ID,
B2.ASUBTYPEID AS ASUBTYPEID
FROM
(
SELECT ID,
max(substr(ASUBTYPEID, 2)) ASUBTYPEID
FROM (
SELECT ID, sys_connect_by_path(ASUBTYPEID, ',') ASUBTYPEID
FROM (
SELECT ID,
ASUBTYPEID,
ID || rn rchild,
ID || (rn - 1) rfather
FROM (
SELECT　A2.ID AS ID,
A2.ASUBTYPEID AS ASUBTYPEID,
row_number() over(
PARTITION BY A2.ID ORDER BY A2.ASUBTYPEID
) rn
FROM 
(
SELECT A.ID,  CASE  WHEN A.ASUBTYPEID = 0 THEN  '订购业务量指标超限'   WHEN A.ASUBTYPEID = 1 THEN  '取消订购业务量指标超限'   WHEN A.ASUBTYPEID = 2 THEN  '上行业务量指标超限' WHEN A.ASUBTYPEID = 3 THEN  '下行业务量指标超限' WHEN A.ASUBTYPEID = 4 THEN '点播消费金额指标超限'   WHEN A.ASUBTYPEID = 5 THEN  '上行业务量异动'  ELSE   '订购业务量指标超限'    END ASUBTYPEID  FROM ALERT_SUBSCRIBE_SUB_CONFIG A
) A2
)
)
CONNECT BY PRIOR rchild = rfather
START WITH rfather LIKE '%0'
)
GROUP BY ID     
)B2

)C2
WHERE C1.SUB_ID = C2.ID
) D1

ORDER BY D1.SUBSCRIBENAME, D1.ASUBTYPEID
) a
CONNECT BY SUBSCRIBENAME = PRIOR SUBSCRIBENAME
AND x - 1 = PRIOR x
)
)
WHERE rn = 1
ORDER BY SUBSCRIBENAME
)E2,
(
SELECT&nbs