MySQL 在 JOIN 后选择 DISTINCT 值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1944350/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SELECT DISTINCT values after a JOIN
提问by Rupert Madden-Abbott
I have 3 tables:
我有3张桌子：
Vehicle: vehicle_id, vehicle_type
1, motorcycle
2, car
3, van
Owners: person_id, vehicle_id, date_bought
1, 1, 2009
1, 2, 2008
2, 3, 2009
2, 1, 2005
I want to display a list of all vehicle names. If the person_id = 1, date_boughtshould also be returned.
我想显示所有车辆名称的列表。如果person_id = 1,date_bought也应该返回。
So I thought I would start with this:
所以我想我会从这个开始：
SELECT * FROM vehicles
LEFT JOIN Owners USING (vehicle_id)
which returns this:
返回这个：
1, 1, motorcycle, 2009
1, 2, car, 2008
2, 3, van, 2009
2, 1, motorcycle, 2005
However, I now cannot narrow this down to the needed result. If I use DISTINCT(car_id), there is no change as I am already choosing distinct car ids before the JOIN; they are only non-distinct after the join. If I use WHERE person_id = 1, I remove the last 2 rows and all reference to the van is gone. If I use GROUP BY car_id, the first and last rows are combined but the date_boughtfor the motorcycle is chosen arbitrarily. What I want is this:
但是，我现在无法将其缩小到所需的结果。如果我使用DISTINCT(car_id)，则没有变化，因为我已经在 JOIN 之前选择了不同的汽车 ID；它们只有在加入后才不同。如果我使用WHERE person_id = 1，我会删除最后 2 行，并且所有对面包车的引用都消失了。如果我使用GROUP BY car_id，则第一行和最后一行组合在一起，但date_bought摩托车的 是任意选择的。我想要的是这个：
1, 1, motorcycle, 2009
1, 2, car, 2008
, 3, van,
I need to require a distinct car id but this happens before the JOIN and so has no effect at all. How can I get the uniqueness with the JOIN?
我需要一个不同的汽车 ID，但这发生在 JOIN 之前，因此根本没有任何影响。如何获得 JOIN 的唯一性？
回答by Robert Christie
You need to include the restriction on person id in your join and use an outer join. Outer joins are used when you want to return information even if there are no records in the table you're joining to. Try
您需要在加入中包含对人员 ID 的限制并使用外部联接。即使您要连接的表中没有记录，也可以在您想要返回信息时使用外部连接。尝试
SELECT person_id, vehicles.*
FROM vehicles
LEFT OUTER JOIN Owners on vehicles.vehicle_id = owners.vehicle_id
and person_id = 1
回答by OMG Ponies
This should return what you've listed as expected output:
这应该返回您列为预期输出的内容：
SELECT DISTINCT
o.person_id,
v.vehicle_id,
v.vehicle_type,
o.date_bought
FROM VEHICLES v
LEFT JOIN OWNERS o ON o.vehicle_id = v.vehicle_id
LEFT JOIN (SELECT t.vehicle_id,
MAX(t.date_bought) 'max_date_bought'
FROM OWNERS t
GROUP BY t.vehicle_id) x ON x.vehicle_id = o.vehicle_id
AND x.max_date_bought = o.date_bought
WHERE o.person_id IS NULL
OR o.person_id = 1
Be aware that because of the left join, the OWNERS columns will return NULL if there is no matching vehicle_idin the OWNERS table.
请注意，由于左连接，如果vehicle_idOWNERS 表中没有匹配项，OWNERS 列将返回 NULL 。
