一般資料表運算式(Common Table Expressions, CTE) with as 的使用

 

一、基本範例

WITH londoncustomers
     AS (SELECT *
         FROM   customers
         WHERE  country = 'UK')
SELECT *
FROM   londoncustomers
WHERE  city = 'London'; 

說明：

1、範例中 table londoncustomers 裡面放著國籍為 UK 的顧客，

然後再篩選出住在 London 顧客。

2、with as 的功用是，將一查詢句子做成一張 table，以便讓其他 select 句子查詢。

 

二、多 table 範例

WITH londoncustomers
     AS (SELECT *
         FROM   customers
         WHERE  country = 'UK'),
     londoncustomers2
     AS (SELECT *
         FROM   customers
         WHERE  country = 'UK')
SELECT *
FROM   londoncustomers a,
       londoncustomers2 b
WHERE  a.city = 'London';