对不起，主题不准确。更精确的描述在这里：
考虑示例表：
市：
cityId | cityName
----------------------
51 | NY
52 | Chicago
53 | SanFrancisco
人：
peopleId | peopleName |
-----------------------
21 | John |
22 | Emma |
23 | Frank |
24 | George |
25 | Albert |
产品：
goodsId | good | peopleId | cityId
------------------------------------
1 | bread | 21 | 51
2 | steel | 22 | 51
3 | onion | 23 | 0
4 | chair | 22 | 52
5 | knife | 22 | 0
输入数据：
cityId：51
peopleId：（21，22，23，25）
预期结果：
peopleId | peopleName | cityId | cityName | goodId | good
-----------------------------------------------------------
21 | John | 51 | NY | 1 | bread
22 | Emma | 51 | NY | 2 | steel
23 | Frank | 0 | --- | 3 | onion
25 | Albert | --- | --- | --- | ----
22 | Emma | 0 | --- | 5 | knife
即使几乎是空的数据，仍然会显示一些行（此处为peopleId 25）。这里发生了什么？
我想选择在输入数据（21、22、23、25）和cityId = 51中具有people.peopleId的所有goods.good。但是，并非商品表中的每一行都指定了goods.cityId，也不是每个商品。 peopleId在商品表中。我希望我的结果有条件依赖：
如果在商品表中存在peopleId，并且此行也有我正在寻找的cityId，请打印它。
否则，如果goods表中存在peopleId，但是goods.cityId = 0-选择它。
否则，如果在商品表中不存在peopleId-仅打印peopleId和peopleName，其余字段将保留为空。
我已经做了这个：
SELECT DISTINCT people.peopleId, people.peopleName,
CASE
WHEN goods.cityId = 51
THEN (SELECT goods.good FROM goods WHERE goods.zalozenie = 51 AND goods.peopleId = people.peopleId)
ELSE
CASE
WHEN goods.cityId = 0
THEN (SELECT goods.good FROM goods WHERE goods.cityId = 0 AND goods.peopleId = people.peopleId)
ELSE -1
END
END AS goods FROM people LEFT JOIN goods ON people.peopleId = goods.peopleId WHERE people.peopleId IN ( 21, 22, 23, 25 )
这几乎可以正常工作，但结果显示：
peopleId | peopleName | cityId | cityName | goodId | good
-----------------------------------------------------------
21 | John | 51 | NY | 1 | bread
22 | Emma | 51 | NY | 2 | steel
23 | Frank | 0 | --- | 3 | onion
25 | Albert | --- | --- | --- | ----
22 | Emma | 0 | --- | 5 | knife
不需要的行是最后一行-如果已经有cityId> 0的peopleId = 22，则只应选择第二行并忽略最后一行。
希望你能帮我！
卡尔雷格
最佳答案
我认为您正在尝试执行以下操作：
SELECT p.peopleId, p.peopleName, c.cityId, c.cityName, g.goodId, g.good
FROM people p left join goods g
on p.peopleId = g.peopleId
left join city c
on c.cityId = g.cityId
WHERE
(g.goodId is null or g.cityId = 51 or (g.cityId = 0 and NOT EXISTS (SELECT * FROM goods g2 WHERE g2.peopleId=p.peopleId and g2.cityId>0)))
and p.peopleId IN (21, 22, 23, 25);
看到这个sql fiddle来尝试一下。无论如何，希望这是一个起点。