mysql - 如何使用 INNER JOIN 进行选择,给出相关但不相关的记录
问题描述
我有这两张桌子:
用户
+----+-------+
| id | name |
+----+-------+
| 1 | John |
+----+-------+
| 2 | Peter |
+----+-------+
| 3 | Lucas |
+----+-------+
礼物
+----+--------+----------+----------+
| id | boy_id | type | quantity |
+----+--------+----------+----------+
| 1 | 1 | clothing | 3 |
+----+--------+----------+----------+
| 2 | 2 | toy | 1 |
+----+--------+----------+----------+
| 3 | 2 | clothing | 2 |
+----+--------+----------+----------+
我试图查询“男孩收到的礼物”,如下所示:
+-------+----------+----------+
| name | type | quantity |
+-------+----------+----------+
| John | clothing | 3 |
+-------+----------+----------+
| Peter | toy | 1 |
+-------+----------+----------+
| Peter | clothing | 2 |
+-------+----------+----------+
| Lucas | "" | 0 |
+-------+----------+----------+
但我只知道如何通过这样的查询获取相关数据:
SELECT u.name, g.type, g.quantity FROM gifts g INNER JOIN users u ON g.boy_id = u.id
因此,由于卢卡斯不在礼品表中,我得到以下信息:
+-------+----------+----------+
| name | type | quantity |
+-------+----------+----------+
| John | clothing | 3 |
+-------+----------+----------+
| Peter | toy | 1 |
+-------+----------+----------+
| Peter | clothing | 2 |
+-------+----------+----------+
那么,如果男孩没有收到礼物,我如何在结果中包含彼得,用“”和零填写类型和数量?
解决方案
你可以left join
:
SELECT u.name, g.type, COALESCE(g.quantity, 0) quantity
FROM users u
LEFT JOIN gifts g ON g.boy_id = u.id
推荐阅读
- python-3.x - Micro Logix 1400 PLC 的 Pycomm3 write_tag(tag,value) 问题
- logging - Log4j2 停止/关闭路由附加程序中的 FileManager
- sql-server - 查询中使用的字符串始终使用 NVARCHAR 语法发送,即使基础列不是 unicode
- dictionary - Clojure:将具有关键字键的映射转换为具有等效字符串键的映射
- javascript - 如何使用映射或按 ID 过滤在嵌套数组中获取值
- flask - TemplateSyntaxError: Encountered unknown tag 'endblock'
- android - JSON (POJO) model response
- javascript - Hapi.js user authentication with mongodb issue
- python - 优化旋转和填充
- java - 我需要在每个新的输入流上调用 close() 吗?