连接与聚合

-- 用户最近一笔订单金额
SELECT u.id, u.name, o.total
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.created_at = (
  SELECT MAX(created_at) FROM orders WHERE user_id = u.id
);

-- 统计每个用户订单数（含 0）
SELECT u.id, u.name, COUNT(o.id) AS order_cnt
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name
ORDER BY order_cnt DESC;

-- 查询购买过 iPhone 的用户
SELECT * FROM users u
WHERE EXISTS (
  SELECT 1
  FROM orders o JOIN order_items oi ON oi.order_id = o.id
  JOIN products p ON p.id = oi.product_id
  WHERE o.user_id = u.id AND p.name = 'iPhone 15'
);

-- 与 IN 对比（结果等价，性能依赖执行计划）
SELECT * FROM users WHERE id IN (
  SELECT user_id FROM orders
);

WITH recent_orders AS (
  SELECT o.*,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) rn
  FROM orders o
)
SELECT user_id, id AS recent_order_id, total
FROM recent_orders WHERE rn = 1;

WITH RECURSIVE date_span AS (
  SELECT DATE('2024-01-01') AS d
  UNION ALL
  SELECT DATE_ADD(d, INTERVAL 1 DAY)
  FROM date_span
  WHERE d < '2024-01-07'
)
SELECT d FROM date_span;

SELECT DATE(created_at) AS d, SUM(total) AS revenue
FROM orders
GROUP BY d
ORDER BY d;

SELECT status, COUNT(*) AS cnt, SUM(total) AS total_amount
FROM orders
GROUP BY status
HAVING SUM(total) > 10000;

SELECT user_id, status, SUM(total) AS amount
FROM orders
GROUP BY user_id, status WITH ROLLUP;

-- 计算每个用户的累计消费
SELECT user_id,
       id AS order_id,
       total,
       SUM(total) OVER (PARTITION BY user_id ORDER BY created_at) AS running_total,
       ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
FROM orders;

-- UNION 自动去重，UNION ALL 保留重复
SELECT email FROM newsletter
UNION
SELECT email FROM users;

-- INTERSECT/EXCEPT 可使用 INNER JOIN/LEFT JOIN + WHERE 模拟

SELECT
  DATE(created_at) AS d,
  SUM(CASE WHEN status = 'PAID' THEN total ELSE 0 END) AS paid_amount,
  SUM(CASE WHEN status = 'CANCELLED' THEN total ELSE 0 END) AS cancelled_amount
FROM orders
GROUP BY DATE(created_at);