有時您會面對一個無法輕易用單一的 SQL 語句回答的複雜問題。

您可以嘗試通過使用多個內聯視圖或多個子查詢來一次性得出答案，但這可能會使您的 SQL 難以理解，而一些版本的 SQL，如 Hive SQL，並不允許多個子查詢。

第二種方法是將複雜性分解為幾個不同的步驟。其中一種方法是創建多個表格，每個表格存儲每個步驟的結果。這通常是一種不錯的策略，因為這樣做可以使調試變得更容易，並且更容易理解代碼的執行方式。

然而，這個方法的問題是您需要在完成分析後記得刪除這些表格；否則，這些表格將保留在數據庫中，從而造成數據庫管理問題。

創建多個表格的替代方法是在 SQL 中使用 WITH 子句。

語法

WITH 子句的基本語法如下：

WITH <query_name_1> AS (
SELECT 語句 1
)
主要 SELECT 語句

使用 WITH 子句的方式非常類似於創建表格。當您創建一個表格時，您會給它一個名稱。同樣地，當您使用 WITH 子句時，您也給它一個名稱，這個名稱在主 SQL 語句中的功能是跟表格一樣。

由於 WITH 不創建表格或視觀表，與 WITH 語句關聯的對象在主SQL語句執行後消失，並且不需要進行清理。

您也可以一次用多個 WITH 子句。語法如下：

WITH <query_name_1> AS (
SELECT 語句 1
), <query_name_2> AS (
SELECT 語句 2
),
..
<query_name_n> AS (
SELECT 語句 N
)
主要 SELECT 語句

若要擁有多個 WITH 子句，您不需要多次指定 WITH。相反，在完成第一個 WITH 子句後，添加一個逗號，然後您可以以<query_name>開頭，接著使用 AS 指定下一個子句。在最後一個 WITH 子句和主要 SQL 語句之間不需要逗號。

範例

我們在這頁的範例中使用以下的表格。

Table Store_Sales
 Store_Name  Sales 
 Los Angeles  15000 
 San Diego  2500 
 New York  3000 
 Boston  7000 

範例 1: 在 SELECT 語句上使用 WITH

假設我們想列出所有銷售額高於平均的店家。為了達到這個目標，我們可以使用以下的 WITH 語句：

WITH t1 AS (
SELECT AVG(Sales) AVG_SALES FROM Store_Sales
)
SELECT a1.* FROM Store_Sales a1, t1
WHERE a1.Sales > t1.AVG_SALES;

結果:

Store_Name Sales
Los Angeles 15000
Boston 7000

在這裡，WITH 語句計算平均銷售額（6,875），然後主要的 SQL 查詢只需找出所有銷售額列中的值大於此平均值的行。

使用內聯視觀表的等效 SQL 如下所示：

SELECT a1.* FROM Store_Sales a1,
(SELECT AVG(Sales) AVG_SALES FROM Store_Sales) t1
WHERE a1.Sales > t1.AVG_SALES;

同樣地，我們可以可以通過以下使用子查詢的 SQL 來獲得同樣的結果：

SELECT a1.* FROM Store_Sales a1
WHERE a1.Sales >
(SELECT AVG(Sales) AVG_SALES FROM Store_Sales);

請注意，由於 Hive SQL 並不支持子查詢的用法，所以子查詢版本不適用於 Hive SQL 上。

範例2： 在 CREATE TABL E語句上使用 WITH

我們也可以將 WITH 與 CREATE TABLE 語句一起使用。假設我們想使用前面示例中的 WITH 子句來創建一個表格，我們將輸入以下內容：

CREATE TABLE Above_Average_Sales
AS
WITH t1 AS (
SELECT AVG(Sales) AVG_SALES FROM Store_Sales
)
SELECT a1.* FROM Store_Sales a1, t1
WHERE a1.Sales > t1.AVG_SALES;

查詢中的 WITH 部分應該在 CREATE TABLE AS 之後。請不要將 WITH 子句放在開頭。以下查詢將會產生錯誤：

WITH t1 AS (
SELECT AVG(Sales) AVG_SALES FROM Store_Sales
)
CREATE TABLE Above_Average_Sales
AS
SELECT a1.* FROM Store_Sales a1, t1
WHERE a1.Sales > t1.AVG_SALES;

下一頁：SQL EXISTS

本頁最近於 2023年10月15日更新


Copyright © 2024   1keydata.com   版權所有