SQL Server 對子查詢 (衍生表) 有嚴格要求:每個衍生表必須有別名,且若外層引用內層計算欄位 (如彙總函數),該計算欄位也必須有別名。Oracle 對此較寬鬆。
-- 錯誤:缺少衍生表別名
SELECT * FROM (SELECT com_id FROM company);
-- 正確:外層加別名 'a' (內層可加可不加)
SELECT * FROM (SELECT com_id FROM company) a;
-- 或
SELECT * FROM (SELECT com_id FROM company b) a;
-- 錯誤 1:外層有別名,但 COUNT() 無別名
SELECT * FROM (SELECT com_id, COUNT(com_id) FROM company GROUP BY com_id) a;
-- Msg 8155: 選取清單中未指定資料行名稱的運算式...
-- 錯誤 2:內層表別名 b,外層 a,COUNT() 仍無別名
SELECT * FROM (SELECT com_id, COUNT(com_id) FROM company b GROUP BY com_id) a;
-- 正確:彙總函數結果欄位必須取別名 'c'
SELECT * FROM (SELECT com_id, COUNT(com_id) AS c FROM company b GROUP BY com_id) a;
(SELECT ...) AS alias 或 (SELECT ...) alias。COUNT(*) AS cnt, col1 + col2 AS total。company b) 非強制,但建議加上以利閱讀與避免模糊。
-- 標準寫法:CTE 替代巢狀衍生表,可讀性更高
WITH cte AS (
SELECT com_id, COUNT(*) AS cnt FROM company GROUP BY com_id
)
SELECT * FROM cte WHERE cnt > 10;
使用 WITH (CTE) 可避免多層巢狀別名混淆,並支援遞迴查詢。
SQL Server 子查詢報錯 90% 因「衍生表缺別名」或「運算式欄位缺別名」。養成每個 FROM 子查詢加別名、每個計算欄位加 AS 別名的習慣,即可徹底避開此類語法錯誤。