多表 JOIN

第 13 课，共 21 课 · 第 3 部分 · 多张表 · 约 15 分钟

一个订单买了什么，要看三张表：orders 记着谁哪天下的单，order_items 记着每单买了哪件商品、几件、单价，products 才有商品的名字。这一课把好几张表接在一起，再在接好的结果上分组。

三张表

JOIN 可以一个接一个地写。下面这条查出 1 号客户每个订单里的商品：

1SELECT o.id AS order_id, o.order_date, p.name, oi.quantity2FROM orders AS o3JOIN order_items AS oi ON oi.order_id = o.id4JOIN products AS p ON p.id = oi.product_id5WHERE o.customer_id = 16ORDER BY o.id, p.id;

读的时候从上往下：先是 orders；第一个 JOIN 给每个订单配上它的明细，一行明细一行；第二个 JOIN 再给每行明细配上它的商品。每个 JOIN 都是拿「到目前为止的结果」去和下一张表配对，ON 写的是这一步怎么配。

点单步，看两个 JOIN 一步一步地把列加上去。

四张表

再接上 customers，就能看到客户的名字。下面是上海客户买过的东西，amount 是这一行花的钱：

1SELECT c.name, o.id AS order_id, p.name AS product,2    oi.quantity * oi.unit_price AS amount3FROM customers AS c4JOIN orders AS o ON o.customer_id = c.id5JOIN order_items AS oi ON oi.order_id = o.id6JOIN products AS p ON p.id = oi.product_id7WHERE c.city = '上海'8ORDER BY c.id, o.id, p.id;

c.name 和 p.name 都叫 name，所以给商品名起了别名 product。表多了，同名的列就多，列名前面都写上表的别名，不容易乱。

先 JOIN，再分组

现在算每个客户一共花了多少钱。这是最容易出错的地方，慢慢来。

先不分组，只看 JOIN 之后的行。下面只留 1 号林晓：

1SELECT c.name, o.id AS order_id, p.name AS product,2    oi.quantity * oi.unit_price AS amount3FROM customers AS c4JOIN orders AS o ON o.customer_id = c.id5JOIN order_items AS oi ON oi.order_id = o.id6JOIN products AS p ON p.id = oi.product_id7WHERE c.id = 18ORDER BY o.id, p.id;

林晓有 3 个订单，可这里有 6 行。JOIN 之后，一行是一件商品的明细，不是一个订单，也不是一个客户。她花的钱就是这 6 行 amount 加起来。

再去掉 WHERE，按客户分组：

1SELECT c.name, SUM(oi.quantity * oi.unit_price) AS spent2FROM customers AS c3JOIN orders AS o ON o.customer_id = c.id4JOIN order_items AS oi ON oi.order_id = o.id5GROUP BY c.id, c.name6ORDER BY spent DESC;

点单步，一步一步看：

1. FROM 和两个 JOIN：得到 18 行，每行一件商品的明细，前面带着客户和订单。
2. GROUP BY：这 18 行按客户分组。林晓那一组是上面那 6 行。
3. SELECT：每组算一个 SUM，变成一行。

GROUP BY 分的是 JOIN 之后的行，不是 customers 表本来的行。这条查询不需要商品名，所以没有接 products。GROUP BY 里写了 c.id，是因为可能有两个客户同名。没下过单的客户不在结果里；要他们也出现，用上一课的 LEFT JOIN。

数订单数的坑

在同一条查询里再数一下每人几单，大多数人会这样写：

1SELECT c.name, COUNT(*) AS orders2FROM customers AS c3JOIN orders AS o ON o.customer_id = c.id4JOIN order_items AS oi ON oi.order_id = o.id5WHERE c.id = 16GROUP BY c.id, c.name;

要数订单，就数「不同的订单编号有几个」：第 7 课的 COUNT(DISTINCT o.id)。DISTINCT 放在括号里，先去掉重复的值再数。把两种写法放在一起比：

1SELECT c.name, COUNT(*) AS item_rows, COUNT(DISTINCT o.id) AS orders2FROM customers AS c3JOIN orders AS o ON o.customer_id = c.id4JOIN order_items AS oi ON oi.order_id = o.id5GROUP BY c.id, c.name6ORDER BY c.id;

小技巧： 写 JOIN 加 GROUP BY 之前，先不分组跑一次，问自己「现在一行是什么」。一行是订单明细，COUNT(*) 数的就是明细，不是订单。

自己 JOIN 自己

员工表里的 manager_id 是上级的员工编号，上级也在这张表里。要把每个人和上级的名字放在一行，就让 employees 和自己 JOIN。同一张表用两次，起两个不同的别名：e 是员工，m 是上级：

1SELECT e.name, e.title, m.name AS manager2FROM employees AS e3JOIN employees AS m ON e.manager_id = m.id4ORDER BY e.id;

只有 5 行：1 号经理李华没有上级，manager_id 是 NULL，配不上任何人。想让她也出现，还是用 LEFT JOIN：

1SELECT e.name, e.title, m.name AS manager2FROM employees AS e3LEFT JOIN employees AS m ON e.manager_id = m.id4ORDER BY e.id;

练一练

写好查询，点检查。提示随便看，实在想不出来再看答案。

1SELECT oi.order_id, oi.quantity * oi.unit_price AS amount2FROM order_items AS oi;

1SELECT c.name, COUNT(*) AS orders, SUM(oi.quantity) AS items2FROM customers AS c3JOIN orders AS o ON o.customer_id = c.id4JOIN order_items AS oi ON oi.order_id = o.id5GROUP BY c.id, c.name6ORDER BY c.id;

1SELECT e.name, m.name AS manager2FROM employees AS e3JOIN employees AS m ON e.manager_id = m.id4ORDER BY e.id;

这一课学会了

• JOIN 可以一个接一个地写，每个都拿到目前为止的结果去配下一张表。
• 表多了，列名前写上表的别名，同名的列起别名。
• JOIN 加 GROUP BY：先 JOIN，再把 JOIN 之后的行分组。先不分组跑一次，看清「一行是什么」。
• 一行是明细时，COUNT(*) 数的是明细；数订单用 COUNT(DISTINCT o.id)。
• 一张表可以和自己 JOIN，起两个不同的别名；想留下配不上的行，用 LEFT JOIN。