Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONE

最近一來因工作上的事情比較鬧心，沒心事再研究SQLServer2005的體系結構；一來關於體系結構確實過於複雜，遠遠不如應用來的直接明瞭，所以暫時擱筆。

出於工作上的需要，對應用開發需要更多的瞭解，所以把心事暫時放到這方面。

先從最簡單的SQL入手來分析一下SQL的執行步驟，為什麼瞭解執行步驟，其實與JOIN後的ON條件和WHERE條件容易混淆有關係。

是先執行ON還是先執行WHERE，很大程度上會決定SQL的結果集正確與否。

CREATE TABLE Customers

(

CustomerID CHAR(5) NOT NULL PRIMARY KEY,

City VARCHAR(10) NOT NULL

);

CREATE TABLE Orders

(

OrderID INT NOT NULL PRIMARY KEY,

CustomerID CHAR(5) NULL REFERENCES Customers(CustomerID)

);

INSERT INTO Customers VALUES('FISSA','Madrid');

INSERT INTO Customers VALUES('FRNDO','Madrid');

INSERT INTO Customers VALUES('KRLOS','Madrid');

INSERT INTO Customers VALUES('MRPHS','Zion');

INSERT INTO Orders VALUES(1,'FRNDO');

INSERT INTO Orders VALUES(2,'FRNDO');

INSERT INTO Orders VALUES(3,'KRLOS');

INSERT INTO Orders VALUES(4,'KRLOS');

INSERT INTO Orders VALUES(5,'KRLOS');

INSERT INTO Orders VALUES(6,'MRPHS');

INSERT INTO Orders VALUES(7,NULL);

試看看以上兩個語句有什麼不同，你就會發現很有趣的現象。

SELECT C.CustomerID,COUNT(O.OrderID) AS NumOrders

FROM Customers C

LEFT OUTER JOIN Orders O

ON C.CustomerID=O.CustomerID

WHERE C.City='Madrid'

GROUP BY C.CustomerID

HAVING COUNT(O.OrderID)<3

ORDER BY NumOrders;

SELECT C.CustomerID,COUNT(O.OrderID) AS NumOrders

FROM Customers C

LEFT OUTER JOIN Orders O

ON C.CustomerID=O.CustomerID

AND C.City='Madrid'

GROUP BY C.CustomerID

HAVING COUNT(O.OrderID)<3

ORDER BY NumOrders;

--Step1，首先對FROM後面的表進行笛卡爾乘積，生成虛表STEP1

WITH STEP1

AS

(

SELECT C.CustomerID C_CustomerID,C.City C_City,O.OrderID O_OrderID,O.CustomerID O_CustomerID

FROM Customers C,Orders O

)

SELECT * FROM STEP1

--Step2，再次應用ON語句中的條件，如果沒有外關聯的話，這裡的ON和WHERE實際上是沒有什麼差別的，生成虛表STEP2

WITH STEP2

AS

(

SELECT C.CustomerID C_CustomerID,C.City C_City,O.OrderID O_OrderID,O.CustomerID O_CustomerID

FROM Customers C

JOIN Orders O

ON C.CustomerID=O.CustomerID

)

SELECT * FROM STEP2

--Step3，如果指定了OUTER JOIN，SQL會自動把STEP2表中未匹配的行作為外部行新增到STEP3中，此處找到了CustomerID=FISSA,City=Madrid，這個沒有訂單但又有相關名字的使用者

WITH STEP3

AS

(

SELECT C.CustomerID C_CustomerID,C.City C_City,O.OrderID O_OrderID,O.CustomerID O_CustomerID

FROM Customers C

LEFT OUTER JOIN Orders O

ON C.CustomerID=O.CustomerID

)

SELECT * FROM STEP3

--Step4，應用WHERE條件，過濾不符合條件的記錄

AS

(

SELECT C.CustomerID C_CustomerID,C.City C_City,O.OrderID O_OrderID,O.CustomerID O_CustomerID

FROM Customers C

LEFT OUTER JOIN Orders O

ON C.CustomerID=O.CustomerID

WHERE C.City='Madrid'

)

SELECT * FROM STEP4

--Step5，對以上的結果集進行分組

WITH STEP5

AS

(

SELECT C.CustomerID,COUNT(O.OrderID) AS NumOrders

FROM Customers C

LEFT OUTER JOIN Orders O

ON C.CustomerID=O.CustomerID

WHERE C.City='Madrid'

GROUP BY C.CustomerID

)

SELECT * FROM STEP5

--Step6，處理CUBE、ROLLUP之類的語句，此處無此需求

略

SELECT * FROM STEP6

--Step7處理Having篩選器，與WHERE條件有些類似

WITH STEP7

AS

(

SELECT C.CustomerID,COUNT(O.OrderID) AS NumOrders

FROM Customers C

LEFT OUTER JOIN Orders O

ON C.CustomerID=O.CustomerID

WHERE C.City='Madrid'

GROUP BY C.CustomerID

HAVING COUNT(O.OrderID)<3

)

SELECT * FROM STEP7

--Step8，處理SELECT列表，即別名轉換把COUNT(O.OrderID) 轉換成NumOrders

略

SELECT * FROM STEP8

--Step9，應用DISTINCT語句，此處無此需求

略

SELECT * FROM STEP9

--Step10，應用ORDER BY語句進行排序

SELECT C.CustomerID,COUNT(O.OrderID) AS NumOrders

FROM Customers C

LEFT OUTER JOIN Orders O

ON C.CustomerID=O.CustomerID

WHERE C.City='Madrid'

GROUP BY C.CustomerID

HAVING COUNT(O.OrderID)<3

ORDER BY NumOrders

--Step11，執行TOP選項，此處無此需求

略

SELECT * FROM STEP11

關於SQLServer2005的學習筆記——SQL查詢解析步驟

相關文章