ORDER BY 是 SQL 結果集排序的唯一標準語法。本文整理升冪/降冪、多欄位、運算式、別名、欄位位置、非選取欄位等六大實戰用法。
SELECT col1, col2 FROM tbl
ORDER BY sort_col1 [ASC|DESC], sort_col2 [ASC|DESC], ...;ASC (預設) 遞增，DESC 遞減。| 用法 | 範例 | 說明 |
|---|---|---|
| 非選取欄位排序 | SELECT name FROM emp ORDER BY salary DESC; | SELECT 清單未出現的欄位亦可排序 |
| 運算式排序 | SELECT qty, price, qty*price AS amount FROM sales ORDER BY qty*price DESC; | 直接用算術運算式 |
| 別名排序 | SELECT qty*price AS amount FROM sales ORDER BY amount; | 使用 SELECT 定義的別名 |
| 欄位位置排序 | SELECT dept_id, emp_id FROM emp ORDER BY 1, 2; | 1=第一欄，UNION 查詢欄位名不同時必須用位置 |
| 多欄位混合排序 | ORDER BY dept_id ASC, salary DESC; | 部門升冪、同部門薪資降冪 |
| NULL 排序控制 | ORDER BY col NULLS FIRST / LAST (Oracle/PostgreSQL) | SQL Server 預設 NULL 視為最小值 (ASC 時最前) |
-- 升冪 (預設)
SELECT * FROM employees ORDER BY hire_date;
-- 降冪
SELECT * FROM employees ORDER BY salary DESC;
-- 非選取欄位
SELECT emp_name FROM employees ORDER BY dept_id;
-- 運算式
SELECT product_id, unit_price, quantity, unit_price*quantity AS total
FROM order_details ORDER BY unit_price*quantity DESC;
-- 別名
SELECT emp_name, salary*12 AS annual_sal FROM employees ORDER BY annual_sal;
-- 位置 (配合 UNION)
SELECT dept_id AS d, emp_name FROM employees
UNION
SELECT dept_id, emp_name FROM retired_emp
ORDER BY 1, 2;Sort 運算子，大幅降低 CPU/記憶體。TOP N / LIMIT 搭配 ORDER BY 可只排序前 N 筆 (Top-N Sort)。
