SQL Server 查詢技巧精選：LIKE 模式、TOP、UNION、COMPUTE、別名

1. 字元模式比對：LIKE

-- 第二字元為 t 或 a
SELECT * FROM employees 
WHERE firstname LIKE '_[t,a]%';

_ 單一字元、% 任意長度、[...] 字元集合、[^...] 排除集合。

2. 欄位別名

SELECT '名字' = firstname, '姓氏' = lastname FROM employees;
SELECT firstname AS '名字', lastname AS '姓氏' FROM employees;

3. TOP 限制筆數

-- 前 2 筆
SELECT TOP 2 firstname, lastname FROM employees WHERE firstname LIKE '_[t,a]%';

-- 前 70%
SELECT TOP 70 PERCENT firstname, lastname FROM employees WHERE firstname LIKE '_[t,a]%';

4. 集合運算：UNION / UNION ALL

SELECT * FROM employees WHERE title = 'Sales Manager'
UNION
SELECT * FROM employees WHERE address IS NOT NULL;

-- 保留重複（效能較好）
SELECT * FROM employees WHERE title = 'Sales Manager'
UNION ALL
SELECT * FROM employees WHERE address IS NOT NULL;

⚠️ 包含 text/ntext/image 欄位時 UNION 會報錯，需用 UNION ALL。

5. 分組小計：COMPUTE / COMPUTE BY

SELECT sex, sclass, score 
FROM student 
ORDER BY sex 
COMPUTE SUM(score) BY sex;

-- 總計
SELECT sex, sclass, score 
FROM student 
COMPUTE SUM(score);

• COMPUTE BY 需搭配 ORDER BY，且 BY 欄位必須出現在 ORDER BY 中
• 產生多個結果集：明細列 + 各組小計列