SQL 常用查詢模板

-- 分組統計（按天、按渠道）
SELECT DATE(created_at) AS d, channel, COUNT(*) AS cnt, SUM(amount) AS total
FROM orders
WHERE created_at >= '2026-10-01'
GROUP BY d, channel
ORDER BY d DESC, total DESC;

-- 取每個用戶最新一條記錄（窗口函數）
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM events
) t WHERE rn = 1;

-- 分組後取前 3 名
SELECT * FROM (
  SELECT *, RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rk
  FROM employees
) t WHERE rk <= 3;

-- 找重複數據
SELECT email, COUNT(*) c FROM users GROUP BY email HAVING c > 1;