今天遇到一個奇怪的問題，項目突然要從mysql切換到sql server資料庫，包含order by 子句的嵌套子查詢報錯。 示例：select top 10 name,age,sex from ( select * from user order by id desc) temp; 在mysql數 ...
今天遇到一個奇怪的問題，項目突然要從mysql切換到sql server資料庫，包含order by 子句的嵌套子查詢報錯。
示例：select top 10 name,age,sex from ( select * from user order by id desc) temp;
在mysql資料庫沒有問題，但是sql server中報錯：
[Err] 42000 - [SQL Server]除非另外還指定了 TOP 或 FOR XML，否則，ORDER BY 子句在視圖、內聯函數、派生表、子查詢和公用表表達式中無效。
解決辦法：在子查詢配合TOP 100 PERCENT用
select top 10 name,age,sex from (select top 100 PERCENT* from tbl_user order by id DESC) temp
其中top 100 percent 的意思是：返回符合條件的全部記錄行，即所有符合條件的記錄
原理：ORDER BY子句的查詢不能用作表的表達式，其中表的表達式包括：視圖、內聯表值函數、子查詢、派生表和共用表達式。select+order by在視圖、子查詢中的返回值不是表，而且是游標，所以會報錯。
通過查看眾多大咖的博客（在此不一一列舉，非常佩服，寫的很詳細），下麵分析一下sql server執行失敗但是mysql執行成功原因：
sql：
(8)SELECT (9)DISTINCT (11)< Top Num> < select list>
(1)FROM [left_table]
(3) JOIN < right_table>
(2)ON < join_condition>
(4)WHERE < where_condition>
(5)GROUP BY
(6)WITH < CUBE | RollUP>
(7)HAVING < having_condition>
(10)ORDER BY < order_by_list>
執行過程邏輯：
（1）FROM：對FROM子句中的前兩個表執行笛卡爾積（Cartesian product)(交叉聯接），生成虛擬表VT1
（2）ON：對VT1應用ON篩選器。只有那些使< join_condition>為真的行才被插入VT2。
（3）OUTER(JOIN)：如 果指定了OUTER JOIN（相對於CROSS JOIN 或(INNER
JOIN),保留表（preserved
table：左外部聯接把左表標記為保留表，右外部聯接把右表標記為保留表，完全外部聯接把兩個表都標記為保留表）中未找到匹配的行將作為外部行添加到
VT2,生成VT3.如果FROM子句包含兩個以上的表，則對上一個聯接生成的結果表和下一個表重覆執行步驟1到步驟3，直到處理完所有的表為止。
（4）WHERE：對VT3應用WHERE篩選器。只有使< where_condition>為true的行才被插入VT4.
（5）GROUP BY：按GROUP BY子句中的列列表對VT4中的行分組，生成VT5.
CUBE|ROLLUP：把超組(Suppergroups)插入VT5,生成VT6.
（6）HAVING：對VT6應用HAVING篩選器。只有使< having_condition>為true的組才會被插入VT7. having篩選器是第一個也是為唯一一個應用到已分組數據的篩選器。
（7）SELECT：處理SELECT列表，產生VT8.
（8）DISTINCT：將重覆的行從VT8中移除，產生VT9. 如果應用了group by子句那麼distinct是多餘的，原因同樣在於，分組的時候是將列中唯一的值分成一組，同時只為每一組返回一行記錄，那麼所以的記錄都將是不相同的
（9）ORDER BY：將VT9中的行按ORDER BY 子句中的列列表排序，生成游標（VC10).此時返回的是一個游標，而不是虛擬表。sql是基於集合的理論的，集合不會預先對他的行排序，它只是成員的邏輯集合，成員的順序是無關緊要的。對錶進行排序的查詢可以返回一個對象，這個對象包含特定的物理順序的邏輯組織。這個對象就叫游標。正因為返回值是游標，那麼使用order by 子句查詢不能應用於表表達式。排序是很需要成本的，除非你必須要排序，否則最好不要指定order by，最後，在這一步中是第一個也是唯一一個可以使用select列表中別名的步驟。
（10）TOP：從VC10的開始處選擇指定數量或比例的行，生成表VT11,並返回調用者。
mysql:
(8)SELECT (9)DISTINCT < select list>
(1)FROM [left_table]
(3) JOIN < right_table>
(2)ON < join_condition>
(4)WHERE < where_condition>
(5)GROUP BY
(6)WITH < CUBE | RollUP>
(7)HAVING < having_condition>
(10)ORDER BY < order_by_list>
(11)LIMIT<limit_number>
1 下麵我們來具體分析一下查詢處理的每一個階段
2 FORM: 對FROM的左邊的表和右邊的表計算笛卡爾積。產生虛表VT1
3 ON: 對虛表VT1進行ON篩選，只有那些符合<join-condition>的行才會被記錄在虛表VT2中。
4 JOIN： 如果指定了OUTER JOIN（比如left join、 right join），那麼保留表中未匹配的行就會作為外部行添加到虛擬表VT2中，產生虛擬表VT3, rug from子句中包含兩個以上的表的話，那麼就會對上一個join連接產生的結果VT3和下一個表重覆執行步驟1~3這三個步驟，一直到處理完所有的表為止。
5 WHERE： 對虛擬表VT3進行WHERE條件過濾。只有符合<where-condition>的記錄才會被插入到虛擬表VT4中。
6 GROUP BY: 根據group by子句中的列，對VT4中的記錄進行分組操作，產生VT5.
7 CUBE | ROLLUP: 對錶VT5進行cube或者rollup操作，產生表VT6.
8 HAVING： 對虛擬表VT6應用having過濾，只有符合<having-condition>的記錄才會被 插入到虛擬表VT7中。
9 SELECT： 執行select操作，選擇指定的列，插入到虛擬表VT8中。
10 DISTINCT： 對VT8中的記錄進行去重。產生虛擬表VT9.
11 ORDER BY: 將虛擬表VT9中的記錄按照<order_by_list>進行排序操作，產生虛擬表VT10.
12 LIMIT：取出指定行的記錄，產生虛擬表VT11, 並將結果返回。
總結：兩種sql執行順序基本是一樣的。sql server中order by子句產生的是一個游標，而mysql的order by子句執行結果是一個虛擬表，所有同樣的sql語句執行結果不同。
