包含空值在MySQL SELECT结果
问题描述:
我有以下MySQL表:包含空值在MySQL SELECT结果
tbl_pet_owners:
+----+--------+----------+--------+--------------+
| id | name | pet | city | date_adopted |
+----+--------+----------+--------+--------------+
| 1 | jane | cat | Boston | 2017-07-11 |
| 2 | jane | dog | Boston | 2017-07-11 |
| 3 | jane | cat | Boston | 2017-06-11 |
| 4 | jack | cat | Boston | 2016-07-11 |
| 5 | jim | snake | Boston | 2017-07-11 |
| 6 | jim | goldfish | Boston | 2017-07-11 |
| 7 | joseph | cat | NYC | 2016-07-11 |
| 8 | sam | cat | NYC | 2017-07-11 |
| 9 | drew | dog | NYC | 2016-07-11 |
| 10 | jack | frog | Boston | 2017-07-19 |
+----+--------+----------+--------+--------------+
tbl_pet_types:
+----------+-------------+
| pet | type |
+----------+-------------+
| cat | mammal |
| dog | mammal |
| goldfish | fish |
| goldfish | seacreature |
| snake | reptile |
+----------+-------------+
我有下面的SELECT语句
SELECT DISTINCT owners.name, owners.pet, owners.city,
group_concat(DISTINCT types.type separator ', ') AS type
FROM tbl_pet_owners owners
INNER JOIN tbl_pet_types types ON owners.pet = types.pet
WHERE owners.city = 'Boston' OR owners.city = 'NYC'
GROUP BY owners.name, owners.pet
ORDER BY owners.city
..这将返回这样的结果:因为在tbl_pet_types青蛙没有进入
+--------+----------+--------+-------------------+
| name | pet | city | type |
+--------+----------+--------+-------------------+
| jack | cat | Boston | mammal |
| jane | cat | Boston | mammal |
| jane | dog | Boston | mammal |
| jim | goldfish | Boston | fish, seacreature |
| jim | snake | Boston | reptile |
| drew | dog | NYC | mammal |
| joseph | cat | NYC | mammal |
| sam | cat | NYC | mammal |
+--------+----------+--------+-------------------+
不幸的是,杰克的青蛙从结果中省略。我如何编辑我的查询以在结果中包含jack的青蛙(type = NULL)?
答
想象这个使用LEFT JOIN到tbl_pet_types而不是INNER JOIN。 INNER JOIN的功能是确保您只能看到两个表中匹配的记录。在这种特殊情况下,无论tbl_pet_owners数据是否与tbl_pet匹配,都需要所有tbl_pet_owners数据。
SELECT DISTINCT owners.name, owners.pet, owners.city,
group_concat(DISTINCT types.type separator ', ') AS type
FROM tbl_pet_owners owners
LEFT JOIN tbl_pet_types types ON owners.pet = types.pet
WHERE owners.city = 'Boston' OR owners.city = 'NYC'
GROUP BY owners.name, owners.pet
ORDER BY owners.city
+0
不需要在select子句中使用'DISTINCT',并且'owners.city'应该包含在'GROUP BY'列表中。 –
答
我可以建议以下查询,它类似于你最初的,但有一些修改。
SELECT
owners.name,
owners.pet,
owners.city,
GROUP_CONCAT(DISTINCT COALESCE(types.type, 'NA') separator ', ') AS type
FROM tbl_pet_owners owners
LEFT JOIN tbl_pet_types types
ON owners.pet = types.pet
WHERE owners.city = 'Boston' OR owners.city = 'NYC'
GROUP BY owners.name, owners.pet, owners.city
ORDER BY owners.city
你不需要在你的SELECT子句中使用DISTINCT
,因为GROUP BY
就已经达到你脑子里想的是什么。最主要的变化是切换到LEFT JOIN
。这应该防止记录被丢弃。
将你的'INNER JOIN'改为'LEFT OUTER JOIN'并让'呃撕裂。 'INNER JOIN'说“只在连接条件为真时返回结果”。 'LEFT OUTER JOIN'说:“返回第一个表中的所有结果,仅返回条件为真的左连接表中的结果” – JNevill
[INNER JOIN]与“OUTER JOIN”之间的区别是什么“?](https://*.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join) – JNevill
@JNevill我认为'WHERE'条款中的那些条件也是需要移到'ON'子句。 –