SQL148 返回产品名称和每一项产品的总订单数

描述

Products表为产品信息表含有字段prod_id产品id、prod_name产品名称

prod_id	prod_name
a0001	egg
a0002	sockets
a0013	coffee
a0003	cola
a0023	soda

OrderItems表为订单信息表含有字段order_num订单号和产品id prod_id

prod_id	order_num
a0001	a105
a0002	a1100
a0002	a200
a0013	a1121
a0003	a10
a0003	a19
a0003	a5

【问题】

使用 OUTER JOIN 联结 Products 表和 OrderItems 表，返回产品名称（prod_name）和每一项产品的总订单数（不是订单号），并按产品名称升序排序。

【示例结果】

返回产品名称prod_name和订单号订单数orders

prod_name	orders
coffee	1
cola	3
egg	1
sockets	2
soda	0

【示例解析】

返回产品和产品对应的实际支付的订单数，但是无实际订单的产品soda也返回，最后根据产品名称升序排序。

SELECT P.prod_name, IFNULL(T.orders, 0) AS orders 
FROM Products P 
LEFT JOIN (
    SELECT prod_id, COUNT(*) AS orders 
    FROM OrderItems 
    GROUP BY prod_id
) T ON T.prod_id = P.prod_id
ORDER BY P.prod_name;

 关键代码解释

1. 处理 NULL：IFNULL(T.orders, 0)

在使用 LEFT JOIN 时，如果右表（这里是子查询 T）中没有匹配的记录，对应字段（如 T.orders）的值就会是 NULL。
但在业务展示中，我们通常希望“没有订单”显示为 0，而不是 NULL 或空值。

IFNULL() 是 MySQL 特有的函数，用于判断并替换 NULL 值：

IFNULL(expression, replacement)

• 如果 expression 不是 NULL，返回该表达式的值；
• 如果 expression 是 NULL，则返回 replacement。

✅ 应用示例：

IFNULL(T.orders, 0)

当 T.orders 为 NULL（即产品从未被下单）时，返回 0，实现“零订单显示为 0”的需求。

⚠️ 注意：IFNULL 仅适用于 MySQL 数据库。在其他数据库系统（如 PostgreSQL、SQL Server、Oracle）中不支持。

---

2. 更通用的替代方案：COALESCE(T.orders, 0)

如果你使用的是其他数据库，或希望写出兼容性更强的标准 SQL，应使用 COALESCE() 函数。

COALESCE(value1, value2, ..., valueN)

• 返回参数列表中第一个非 NULL 的值。
• 至少需要两个参数。
• 是 SQL 标准函数，几乎在所有关系型数据库中都支持（MySQL、PostgreSQL、SQL Server、Oracle、SQLite 等）。

✅ 应用示例：

COALESCE(T.orders, 0)

含义与 IFNULL(T.orders, 0) 完全相同：如果 T.orders 是 NULL，就返回 0。

✅ 优势：

• 跨数据库兼容性好
• 功能更强大（可接受多个参数，例如 COALESCE(col1, col2, 0) 表示优先取 col1，若为 NULL 则取 col2，都为 NULL 才返回 0）

✅ 推荐使用场景：

• 你不确定数据库类型
• 项目要求使用标准 SQL
• 需要更灵活的 NULL 处理逻辑

---

✅ 总结对比

函数	语法	数据库支持	是否标准 SQL	推荐场景
IFNULL	IFNULL(expr, default)	仅 MySQL	❌ 否	MySQL 项目，追求简洁
COALESCE	COALESCE(expr1, expr2, ...)	所有主流数据库	✅ 是	通用项目、跨数据库、标准 SQL

易错点 

📝 为什么统计订单要“先聚合，再连接”？

❌ 错误做法：直接连接

SELECT P.prod_name, COUNT(*) 
FROM Products P 
LEFT JOIN OrderItems O ON P.prod_id = O.prod_id
GROUP BY P.prod_name;

问题：

• 逻辑混乱：COUNT(*) 数的是连接后的行数，看似正确，实则侥幸。

• 性能差：先产生大量重复数据，再分组统计。

• 难扩展：一旦要加条件或字段，容易出错。

---

✅ 正确做法：先聚合，再连接

SELECT P.prod_name, COALESCE(T.orders, 0) AS orders
FROM Products P
LEFT JOIN (
    SELECT prod_id, COUNT(*) AS orders 
    FROM OrderItems 
    GROUP BY prod_id
) T ON P.prod_id = T.prod_id;

优势：

优点	说明
✅ 逻辑清晰	子查询专注“统计”，主查询专注“拼接”
✅ 性能更好	聚合在小表上完成，连接更高效
✅ 避免重复	不会因多订单产生重复行
✅ 易维护	修改统计条件只需动子查询

---

💡 类比理解

• OrderItems 是几千张小票。

• 你想知道“每个产品卖了多少”。

❌ 别把所有小票和产品表连起来数！
✅ 先让系统出个《销量统计表》，再贴到产品表旁边。

这个“统计表”就是子查询。

---

✅ 核心原则

“聚合在前，连接在后”
先用子查询生成统计结果，再用 LEFT JOIN 关联主表，
配合 COALESCE 处理 NULL，确保未销售产品显示为 0。