mysql - 我如何攻击这个 mySQL 查询？

Question

我需要合并两个表。

第一个表是 People(SSN PRIMARY KEY, fname, lname)。

另一个表是 Cities(name, cityID)，其中 name = "fname lname"即 2 列 People 之间用空格连接。一个人可能与多个城市相关联，一个城市可能有不止一个人(很多 - 许多)。

我想将两者组合成一个表 PeopleCities(SSN, cityID)。如果找不到某个城市的人，我需要该城市 ID 的 SSN 为 0。我的经验主要是使用 sqlite 而不是 mySQL，所以我对我的查询不是很有信心。

这是我的查询:

SELECT ISNULL(People.SSN, 0), Cities.cityID
FROM People 
FULL OUTER JOIN Cities
ON (Cities.name = CONCAT_WS(" ", People.fname, " ", People.lname) FROM People);

Answer 1

您可以使用UNION ALL，首先获取匹配的人/城市对，然后获取不匹配的:

select p.ssn, coalesce(c.cityid, 0)
from people p left join
     cities c
     on c.name = concat_ws(' ', p.name, p.lname)
union all
select distinct 0, c.cityid
from cities c
where not exists (select 1 from people p where c.name = concat_ws(' ', p.name, p.lname);