【解惑】介紹三大資料庫的with語句的寫法及使用場景

WITH 子句通常被稱為 "Common Table Expressions"（CTE），俗稱記憶體臨時表，當使用 WITH 語句時，應註意具體的資料庫版本和支持情況。以下是對 MySQL、Microsoft SQL Server（MSSQL）和 Oracle 資料庫的 WITH 語句用法示例，以及在 WHERE 子句中添加分組關聯條件實現對比最大聚合值：

MySQL

在 MySQL5.7 中，可以使用子查詢和連接操作來實現 WITH 語句的效果，並結合 GROUP BY 子句實現分組關聯條件，但是性能較低，因為每行a.s_price都需要與子查詢max(s_price)聚合對比，結果集超過1萬行就慢了。以下是示例代碼：

SELECT a.col1, a.col2
FROM table1 a
INNER JOIN (
    SELECT item_type, MAX(s_price) AS m_price
    FROM table1
    GROUP BY item_type
) AS max_price ON a.item_type = max_price.item_type
WHERE a.s_price = max_price.m_price;

在MySQL8.0及以上版本，可以使用with語法實現上述功能，且性能較高：

WITH max_price AS (
    SELECT item_type, MAX(s_price) AS m_price
    FROM table1
    GROUP BY item_type
)
SELECT a.col1, a.col2
FROM table1 a
WHERE a.s_price = (SELECT m_price FROM max_price t1 WHERE t1.item_type = a.item_type);

Microsoft SQL Server (MSSQL)

在 MSSQL 中，可以使用 WITH 語句（也稱為公共表表達式）來在查詢中定義臨時的命名結果集，併在 WHERE 子句中添加分組關聯條件。以下是示例代碼：

WITH max_price AS (
    SELECT item_type, MAX(s_price) AS m_price
    FROM table1
    GROUP BY item_type
)
SELECT a.col1, a.col2
FROM table1 a
WHERE a.s_price = (SELECT m_price FROM max_price t1 WHERE t1.item_type = a.item_type);

此 WITH 語句在 MSSQL 2005 及以上版本中可用。

Oracle

在 Oracle 中，WITH 語句通常被稱為子查詢塊或子查詢事務，它使用 WITH 子句為查詢定義臨時命名的數據塊，併在 WHERE 子句中添加分組關聯條件。以下是示例代碼：

WITH max_price AS (
    SELECT item_type, MAX(s_price) AS m_price
    FROM table1
    GROUP BY item_type
)
SELECT a.col1, a.col2
FROM table1 a
WHERE a.s_price = (SELECT m_price FROM max_price t1 WHERE t1.item_type = a.item_type);

此 WITH 語句在 Oracle 9i 及以上版本中可用。

需要註意的是，這些示例是通用的語法示例，不能適用於所有情況。具體使用和限制還需要參考各個資料庫的官方文檔或進一步研究各個資料庫管理系統的特定語法和功能。