行数放大演示
行数放大演示
SELECT COUNT(*) AS joined_rows,
COUNT(DISTINCT o.id) AS real_orders
FROM orders o
JOIN order_items i ON i.order_id = o.id;先聚合再连接
先聚合再连接
SELECT o.id, o.amount, t.item_count
FROM orders o
JOIN (
SELECT order_id, COUNT(*) AS item_count
FROM order_items
GROUP BY order_id
) t ON t.order_id = o.id;| 计数修正 | COUNT(DISTINCT 主键) 或先聚合再连接 |
|---|---|
| 金额类 | 主表金额会被明细行重复累加，SUM 前确认粒度 |
| 手段 | 子查询、CTE、窗口函数都可以控制粒度 |