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 SUBSCRIBENAME, TRANSLATE (LTRIM (text, '/'), '*/', '*,') ID
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 (ID,'/') text
FROM (SELECT SUBSCRIBENAME, ID as ID,
ROW_NUMBER () OVER (PARTITION BY SUBSCRIBENAME ORDER BY SUBSCRIBENAME,ID) x
FROM ALERT_SUBSCRIBE_CONFIG
ORDER BY SUBSCRIBENAME, ID) a
CONNECT BY SUBSCRIBENAME = PRIOR SUBSCRIBENAME AND x - 1 = PRIOR x))
WHERE rn = 1
ORDER BY SUBSCRIBENAME
) E3,
(
select t.subscribename, t.isnotify,t.emailformat,t.emailaddr from alert_subscribe_config t
group by t.subscribename, t.isnotify,t.emailformat,t.emailaddr
) E4,
(
SELECT SUBSCRIBENAME, TRANSLATE (LTRIM (text, '/'), '*/', '*,') ALEVELID
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 (ALEVELID,'/') text
FROM (SELECT SUBSCRIBENAME, ALEVELID as ALEVELID,
ROW_NUMBER () OVER (PARTITION BY SUBSCRIBENAME ORDER BY SUBSCRIBENAME,ALEVELID) x
FROM ALERT_SUBSCRIBE_CONFIG
ORDER BY SUBSCRIBENAME, ALEVELID) a
CONNECT BY SUBSCRIBENAME = PRIOR SUBSCRIBENAME AND x - 1 = PRIOR x))
WHERE rn = 1
ORDER BY SUBSCRIBENAME
) E5
WHERE E1.SUBSCRIBENAME = E2.SUBSCRIBENAME AND E1.SUBSCRIBENAME = E3.SUBSCRIBENAME
AND E1.SUBSCRIBENAME = E4.SUBSCRIBENAME AND E1.SUBSCRIBENAME = E5.SUBSCRIBENAME
) x
