五餅二魚工作室

SQL Server 從 2005 版開始，就有支援 CTE ( common table expression )，他不僅可以幫忙簡化 SQL 的寫法，讓原本可能一堆的 SubQuery 變成有一致的寫法，還可以搭配 INSERT, UPDATE, DELETE 這些 DML 來使用；或者是把 CTE 拿來配合 Recursive Queries 上的使用，可以讓我們整個 SQL 語法更為精簡也更有效率。

CTE 的基本語法會是這樣 :

這樣可能會看不大懂，我們實際來舉個簡單的範例，應該看起來就比較清楚了

WITH OrdersTable( OrderID, CustomerID, EmployeeID ) as
(
  Select OrderID, CustomerID, EmployeeID 
  from dbo.Orders
), 
OrderDetailsTable( OrderID, TotalPrice ) as 
(
  Select OrderID, SUM(Price) 
  from dbo.[Order Details]
  group by  OrderID
)
Select * 
From OrdersTable A 
inner join OrderDetailsTable B  on A.OrderID = B.OrderID 

在這個範例當中，我們示範了一次把原本的兩個 Table 先各自處理，接著最後再 Join 一起。如果採用傳統的做法，可能就會一開始就 Join 一起，看起來似乎比較簡單，但是如果這當中有不少 Aggregate 這類的處理，或者是接續的多段的組合時，那使用 CTE 就會相段的簡單多了。剛開始在學習的時候，從文獻上來看 CTE 是 ANSI SQL-99 Standard 的規範之一，因此也很好奇的寫了一小段的 Sample 在 Oracle 10 和 11 上面測試一下，看來似乎都是可以通用的，就沒有花太多時間在這個上面了。

前幾天幫忙朋友處理一個問題，他的狀況如果採用 subquery 的方式，SQL 就很難下的出來。了解他的需求之後，於是我就很自然的介紹他採用 CTE，於是朋友就很快的寫出原本非常複雜的 SQL 指令，還可以一塊一塊的測試增加，看起來似乎又是個簡單任務。

然而事情就又發生了，朋友主要是要利用一組 SQL 挑出一些很特殊的條件資料，將這些資料存到另外一個 Table 內，因此我就建議他把原本找出來的資料，在前面再加上 INSERT INTO 的指令，應該就可以解決了，以上述的例子來說，就可以寫成類似以下的語法：

WITH OrdersTable( OrderID, CustomerID, EmployeeID ) as
(
  Select OrderID, CustomerID, EmployeeID 
  from dbo.Orders
)
INSERT INTO SalesTable
  SELECT * FROM OrdersTable

因為手邊剛好沒有 Oracle 的環境，因此就把範例給朋友，請他自行測試了一下，但這樣的寫法在 Oracle 就不行了。原來在 Oracle 的世界中，如果要下 DML 的指令時，則他們會要是命令初始就要有那個保留字，因此就要把原本的語法改成：

INSERT INTO SalesTable
  WITH OrdersTable( OrderID, CustomerID, EmployeeID ) as
     (
        SELECT   OrderID, CustomerID, EmployeeID 
        FROM dbo.Orders
      )
    )
    SELECT  * FROM OrdersTable