什么是WITH AS 陳述句
WITH AS相當于虛擬視圖， WITH AS也叫做分解子查詢或者片段子查詢，
定義一個SQL片段， 該片段會被后面的SQL陳述句用到，可以近似看作一個可用的臨時視圖，
WITH AS優點
增加了SQL的可讀性，如果構造多個片段子查詢，結構會更加清晰，
一次構建，多次使用，達到減少讀的目標，
SYS_TEMP_XXXX便是運行程序中構造的統計結果臨時表，
WITH AS的語法
WITH 臨時視圖名 AS (SELECT查詢)
SELECT .. FROM 臨時視圖名;
-- 單個表臨時視圖
WITH E AS (SELECT * FROM employees)
SELECT * FROM E;
-- 連接多個表的臨時視圖
WITH
e as (SELECT * FROM EMPLOYEES),
d as (SELECT * FROM DEPARTMENTS)
SELECT e.LAST_NAME, d.department_name
FROM e inner join d
ON e.department_id = d.department_id;
-- 聯合多個查詢結果
WITH
e1 as (SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = 100),
e2 as (SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = 110)
SELECT * from e1
UNION ALL
SELECT * FROM e2;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/531940.html
標籤：其他