Oracle笔记:WITH语句用法
程序员文章站
2022-06-30 12:36:13
在sql语句中经常使用到多表组合查询在多表组合查询中有时会用将一个查询结果集用括号括起来放在from语句后面当临时表用,如果一个组合查询用有很多将一个查询结果集用括号括起来放在from语句后面当临时表用,或者from语句多成嵌套,或者同一结果集多次出现的情况,建议使用Oracle with语句创建一个公共临时表。因为子查询在内存临时表中,避免了重复解析,所以执行效率会提高不少。临时表在一次查询结束自动清除。WITH语句的优点:SQL可读性增强。with子查询只执行一次,将结果存储在用户临时表空间...
在sql语句中经常使用到多表组合查询
在多表组合查询中有时会用将一个查询结果集用括号括起来放在from语句后面当临时表用,
如果一个组合查询用有很多将一个查询结果集用括号括起来放在from语句后面当临时表用,或者from语句多成嵌套,或者同一结果集多次出现的情况,
建议使用Oracle with语句创建一个公共临时表。因为子查询在内存临时表中,避免了重复解析,所以执行效率会提高不少。临时表在一次查询结束自动清除。
WITH语句的优点:
SQL可读性增强。
with子查询只执行一次,将结果存储在用户临时表空间中,可以引用多次,增强性能。
分层次查询,一步步筛选出结果集。
一般语法格式:
with
alias_name1 as (subquery1),
alias_name2 as (subQuery2),
……
alias_nameN as (subQueryN)
select col1,col2…… col3
from alias_name1,alias_name2……,alias_nameN
在一个表的基础上利用WITH语句创建多个临时表实现复杂的逻辑查询
WITH AMCH1 AS ( SELECT ACC_NUM, MAX (BUSINESS_DATE) AS MAX_BUSINESS_DATE
FROM ACC_MARGIN_CALL_HIST_TBL
GROUP BY ACC_NUM),
AMCH2
AS (SELECT T.ACC_NUM AS ACC_NUM,
T.BUSINESS_DATE AS MARGIN_CALL_SETTLE_DATE,
T.CALL_AMT_START_DATE AS MARGIN_CALL_START_DATE,
T.NUM_OF_DAYS_IN_PREC_DAYS AS NUM_OF_DAYS_IN_PREC_DAYS,
T.NUM_OF_LAST_LONG_CALL_DAYS
AS NUM_OF_LAST_LONG_CALL_DAYS,
NVL (
( SELECT COUNT (ACC_NUM)
FROM ACC_MARGIN_CALL_HIST_TBL A
WHERE A.ACC_NUM = T.ACC_NUM
AND A.BUSINESS_DATE BETWEEN (SELECT TO_CHAR (
ADD_MONTHS (
TO_DATE (
(SELECT bo_system_pkg.
getbusinessdate
FROM DUAL),
'yyyymmdd'),
-1),
'yyyymmdd')
FROM DUAL)
AND (SELECT bo_system_pkg.
getbusinessdate
FROM DUAL)
GROUP BY A.ACC_NUM),
0)
AS POOR_CREDIT_FLAG_COUNT
FROM ACC_MARGIN_CALL_HIST_TBL T, AMCH1
WHERE T.ACC_NUM = AMCH1.ACC_NUM
AND T.BUSINESS_DATE = AMCH1.MAX_BUSINESS_DATE),
AMCH3
AS (SELECT T.ACC_NUM AS ACC_NUM,
CASE
WHEN (AMCH2.POOR_CREDIT_FLAG_COUNT < 15
AND T.POOR_HISTORY_FLAG = 'N')
OR (AMCH2.POOR_CREDIT_FLAG_COUNT >= 15
AND T.POOR_HISTORY_FLAG = 'N')
THEN
NULL
ELSE --T.BUSINESS_DATE
( SELECT CASE
WHEN (MAX (BUSINESS_DATE) =
(SELECT bo_system_pkg.
get_pre_business_date (
bo_system_pkg.
getbusinessdate,
1,
'HK',
'H')
AS prev_dt
FROM DUAL))
THEN
(SELECT bo_system_pkg.
getbusinessdate
FROM DUAL)
ELSE
MAX (BUSINESS_DATE)
END
FROM ACC_MARGIN_CALL_HIST_TBL
WHERE POOR_HISTORY_FLAG = 'Y'
AND NUM_OF_DAYS_IN_PREC_DAYS = 29
AND NUM_OF_CALL_DAYS >= 91
AND ACC_NUM = AMCH2.ACC_NUM
GROUP BY ACC_NUM)
END
AS MARGIN_CALL_SETTLE_DATE,
CASE
WHEN (AMCH2.POOR_CREDIT_FLAG_COUNT < 15
AND T.POOR_HISTORY_FLAG = 'N')
OR (AMCH2.POOR_CREDIT_FLAG_COUNT >= 15
AND T.POOR_HISTORY_FLAG = 'N')
THEN
NULL
ELSE --T.CALL_AMT_START_DATE
( SELECT MAX (CALL_AMT_START_DATE)
FROM ACC_MARGIN_CALL_HIST_TBL
WHERE POOR_HISTORY_FLAG = 'Y'
AND NUM_OF_DAYS_IN_PREC_DAYS = 29
AND NUM_OF_CALL_DAYS >= 91
AND ACC_NUM = AMCH2.ACC_NUM
GROUP BY ACC_NUM)
END
AS MARGIN_CALL_START_DATE,
CASE
WHEN AMCH2.POOR_CREDIT_FLAG_COUNT >= 15
OR T.POOR_HISTORY_FLAG = 'Y'
THEN
'Y'
ELSE
'N'
END
AS POOR_CREDI,
AMCH2.POOR_CREDIT_FLAG_COUNT AS POOR_CREDIT_FLAG_COUNT
FROM ACC_MARGIN_CALL_HIST_TBL T, AMCH2
WHERE T.ACC_NUM = AMCH2.ACC_NUM
AND T.BUSINESS_DATE = AMCH2.MARGIN_CALL_SETTLE_DATE),
AMCH4
AS (SELECT G.NICK_NAME AS CLIENT_ACC_ID,
DECODE (A.ACC_TYPE, 'C', 'N', 'M', AMCH3.POOR_CREDI)
AS POOR_CREDIT,
0 AS TRANSFER_AMOUNT,
0 AS TRANSFER_RATIO,
DECODE (A.ACC_TYPE,
'C', 0,
'M', AMCH3.POOR_CREDIT_FLAG_COUNT)
AS POOR_CREDIT_FLAG_COUNT,
DECODE (A.ACC_TYPE,
'C', NULL,
'M', AMCH3.MARGIN_CALL_START_DATE)
AS MARGIN_CALL_START_DATE,
DECODE (A.ACC_TYPE,
'C', NULL,
'M', AMCH3.MARGIN_CALL_SETTLE_DATE)
AS MARGIN_CALL_SETTLE_DATE,
NULL AS EXEMPTION_END_DATE,
NULL AS EXEMPTION_REASON
FROM AMCH3,
( SELECT SUBSTR (ACC_NUM, 1, 10) AS ACC_NUM,
MAX (ACC_TYPE) AS ACC_TYPE,
MAX (ACC_GROUP) AS ACC_GROUP
FROM ACC_TBL
GROUP BY SUBSTR (ACC_NUM, 1, 10)
HAVING MAX (ACC_TYPE) IN ('C', 'M')) A,
(SELECT ACC_GROUP, NICK_NAME FROM ACC_GRP_TBL) G
WHERE AMCH3.ACC_NUM = A.ACC_NUM AND A.ACC_GROUP = G.ACC_GROUP)
SELECT V.CLIENT_ACC_CODE AS CLIENT_ACC_ID,
NVL (A.POOR_CREDIT, 'N') AS POOR_CREDIT,
NVL (A.TRANSFER_AMOUNT, 0) AS TRANSFER_AMOUNT,
NVL (A.TRANSFER_RATIO, 0) AS TRANSFER_RATIO,
NVL (A.POOR_CREDIT_FLAG_COUNT, 0) AS POOR_CREDIT_FLAG_COUNT,
A.MARGIN_CALL_START_DATE AS MARGIN_CALL_START_DATE,
A.MARGIN_CALL_SETTLE_DATE AS MARGIN_CALL_SETTLE_DATE,
NVL (A.EXEMPTION_END_DATE, 0) AS EXEMPTION_END_DATE,
NVL (A.EXEMPTION_REASON, 0) AS EXEMPTION_REASON
FROM EXP_CLIENT_ACC_CODE_V V,
( SELECT CLIENT_ACC_ID,
MAX (POOR_CREDIT) AS POOR_CREDIT,
MAX (TRANSFER_AMOUNT) AS TRANSFER_AMOUNT,
MAX (TRANSFER_RATIO) AS TRANSFER_RATIO,
MAX (POOR_CREDIT_FLAG_COUNT) AS POOR_CREDIT_FLAG_COUNT,
MAX (MARGIN_CALL_START_DATE) AS MARGIN_CALL_START_DATE,
MAX (MARGIN_CALL_SETTLE_DATE) AS MARGIN_CALL_SETTLE_DATE,
MAX (EXEMPTION_END_DATE) AS EXEMPTION_END_DATE,
MAX (EXEMPTION_REASON) AS EXEMPTION_REASON
FROM AMCH4
GROUP BY CLIENT_ACC_ID) A
WHERE V.CLIENT_ACC_CODE = A.CLIENT_ACC_ID(+);
本文地址:https://blog.csdn.net/qq_39827640/article/details/107634843
上一篇: SQL题解总结
下一篇: 全面扫描 ,护神兽生活本领