示例
示例
SELECT u.id, u.name,
(SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_cnt,
(SELECT MAX(o.created_at) FROM orders o WHERE o.user_id = u.id) AS last_order
FROM users u
WHERE u.status = 1;JOIN 改写
JOIN 改写
SELECT u.id, u.name, t.cnt, t.last_order
FROM users u
LEFT JOIN (
SELECT user_id, COUNT(*) AS cnt, MAX(created_at) AS last_order
FROM orders
GROUP BY user_id
) t ON t.user_id = u.id
WHERE u.status = 1;| 求值 | 逻辑上每行执行一次子查询 |
|---|---|
| 改写 | LEFT JOIN + GROUP BY 一次算完 |
| 空值 | 无匹配时子查询返回 NULL，可 COALESCE 兜底 |