1. 排序：ORDER BY

-- 基礎排序
SELECT * FROM products ORDER BY price DESC;

-- 多欄位排序
SELECT * FROM employees ORDER BY department_id ASC, hire_date DESC;

-- 運算式排序
SELECT *, (price * quantity) AS total FROM order_items ORDER BY total DESC;

-- NULL 排序控制 (標準 SQL，MySQL 8.0+ 支援)
SELECT * FROM t ORDER BY col ASC NULLS FIRST;  -- NULL 排前面
SELECT * FROM t ORDER BY col DESC NULLS LAST;  -- NULL 排後面

2. 分組：GROUP BY

-- 基礎分組：每組一行
SELECT department_id, COUNT(*) AS emp_count
FROM employees
GROUP BY department_id;

-- 多欄位分組
SELECT department_id, job_id, AVG(salary) AS avg_sal
FROM employees
GROUP BY department_id, job_id;

-- WITH ROLLUP：產生超級聚合行 (小計/總計)
SELECT department_id, job_id, COUNT(*), SUM(salary)
FROM employees
GROUP BY department_id, job_id WITH ROLLUP;
-- 結果包含：各部門職位統計 + 各部門小計 (job_id=NULL) + 總計 (department_id=NULL, job_id=NULL)

3. 分組前過濾：WHERE

-- 僅統計薪資 > 5000 的員工
SELECT department_id, COUNT(*), AVG(salary)
FROM employees
WHERE salary > 5000
GROUP BY department_id;

4. 分組後過濾：HAVING

-- 只顯示員工數 > 5 的部門
SELECT department_id, COUNT(*) AS cnt
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;

-- WHERE vs HAVING 核心差異
-- WHERE：分組前過濾行，不可用聚合函數
-- HAVING：分組後過濾組，可用聚合函數

5. 聚合函數速查

函數	說明	忽略 NULL
COUNT(*)	行數	否
COUNT(col)	非 NULL 個數	是
SUM(col)	求和	是
AVG(col)	平均	是
MIN/MAX(col)	最小/最大	是
GROUP_CONCAT(col [ORDER BY ...] [SEPARATOR ','])	字串聚合	是
STDDEV/STDDEV_POP/VARIANCE	標準差/方差	是
BIT_AND/BIT_OR/BIT_XOR	位運算聚合	是

6. 進階統計：ROLLUP / CUBE / GROUPING SETS

-- ROLLUP：層級小計 (MySQL 支援)
SELECT year, quarter, month, SUM(sales)
FROM sales_data
GROUP BY year, quarter, month WITH ROLLUP;
-- 產生：年季月明細 + 年季小計 + 年小計 + 總計

-- CUBE：所有維度組合小計 (MySQL 8.0 不直接支援，用 GROUPING SETS 模擬)
-- GROUPING SETS：指定精確聚合集合 (MySQL 8.0+)
SELECT year, quarter, month, SUM(sales)
FROM sales_data
GROUP BY GROUPING SETS ((year, quarter, month), (year, quarter), (year), ());

7. GROUPING() 函數識別超級聚合行

SELECT 
  GROUPING(year) AS is_year_total,
  GROUPING(quarter) AS is_quarter_total,
  year, quarter, month, SUM(sales)
FROM sales_data
GROUP BY year, quarter, month WITH ROLLUP;
-- is_xxx = 1 表示該行是該維度的小計/總計行

8. 完整查詢執行順序 (邏輯順序)

1. FROM / JOIN (確定資料來源)
2. WHERE (行過濾)
3. GROUP BY (分組)
4. 聚合函數計算
5. HAVING (組過濾)
6. SELECT (投影、別名)
7. DISTINCT (去重)
8. ORDER BY (排序)
9. LIMIT (限制行數)

別名：可在 ORDER BY、HAVING、GROUP BY (MySQL 擴展) 使用；不可在 WHERE、SELECT 同層其他表達式使用。