第一次看到with as 這種類似于python中讀文件的寫法還是挺疑惑的，其實它是CTE，功能和子查詢很類似但又有不同點，在實際應用場景中具有著獨特作用。

子查詢

子查詢是在主查詢中的嵌套查詢，可以出現在SELECT、FROM、WHERE等子句中。子查詢可以是標量子查詢、行子查詢或表子查詢。

優點：

?? ???? ?簡單的查詢結構，對于小規模查詢可以很方便地使用。
?? ???? ?適用于一次性使用的臨時計算。

缺點：

?? ???? ?可讀性差：嵌套查詢可能使SQL語句變得難以理解，特別是當嵌套層次較深時。
?? ???? ?不能復用：子查詢只能在定義它的查詢中使用，無法在其他地方重用。

CTE（Common Table Expressions）

CTE是在SQL語句的開頭使用WITH關鍵字定義的臨時結果集，隨后可以在主查詢中引用這個結果集。CTE在某些數據庫系統中也被稱為”公用表表達式”。
優點：

?? ???? ?可讀性好：將復雜的查詢分解為多個易于理解的部分。
?? ???? ?復用性強：同一個CTE可以在主查詢中多次引用，提高查詢的效率。
?? ???? ?遞歸查詢：CTE支持遞歸查詢，這是子查詢無法做到的。

缺點：

?? ???? ?對于簡單的查詢可能顯得冗余，不如子查詢簡潔。
?? ???? ?性能上不一定優于子查詢，具體視情況而定，需要根據具體數據庫和查詢場景測試性能。

適用場景

子查詢適用于：

?? ???? ?簡單查詢或一次性使用的臨時計算。
?? ???? ?嵌套在WHERE、FROM或SELECT子句中時。

CTE適用于：

?? ???? ?復雜查詢，將復雜查詢分解為多個易于理解的部分。
?? ???? ?需要在查詢中多次引用同一結果集時。
?? ???? ?遞歸查詢，處理層次結構數據（如組織結構圖、樹形結構等）。

示例

假設我們有一個employees表，我們想要找出每個部門工資最高的員工：

-- 使用子查詢
SELECT department_id, employee_id, salary
FROM employees e1
WHERE salary = (SELECT MAX(salary)FROM employees e2WHERE e2.department_id = e1.department_id
);-- 使用CTE
WITH MaxSalaries AS (SELECT department_id, MAX(salary) AS max_salaryFROM employeesGROUP BY department_id
)
SELECT e.department_id, e.employee_id, e.salary
FROM employees e
JOIN MaxSalaries ms ON e.department_id = ms.department_id AND e.salary = ms.max_salary;

在這個例子中，使用CTE顯得更清晰，因為這將最大工資的計算與主查詢分離開來，使得整個查詢結構更易于理解和維護。