如何在MySQL中从一个到多个相关表中选择唯一行? [英] How to select unique rows from one to many relationed tables in MySQL?

问题描述

作为MySQL-Noob，我正在努力解决以下任务:假设3个表:location groceries和person具有以下属性:

As MySQL-Noob I am struggeling to solve the following task: Assuming 3 tables: location groceries and person with the following properties:

1. 每个表都有一个主要的自动递增的整数id和一个varchar列.
2. 表中的条目都是唯一的.
3. groceries中的每个条目都具有person中一个条目的外键.
4. groceries中可能有不止一个条目共享相同的外键.
5. (3)和(4)也适用于person和location

1. Every table has a primary autoincremented integer id and a varchar column.
2. The entrys in the tables are all unique.
3. Every entry in groceries has a foreign key of an entry in person.
4. It is possible that more then one entry from groceries share the same foreign key.
5. (3) and (4) apply to person and location, too

所以我们有一对多的关系.我该如何选择每个(groceries_product, person_name, location_name)三元组，而person_name不会再出现一次?

So we have a many to one to many relation. How can I select every triple (groceries_product, person_name, location_name) where person_name does not occur more then once?

示例:

tables:  groceries                  | person      | location
------------------------------------ ------------- -------------------------
columns: id  product      person_id | id  name    | id  name  person_id
------------------------------------ ------------- -------------------------
         1   hamburger    1         | 1   Peter   | 1   home  1
         2   cheeseburger 1         | 2   Tom     | 2   work  1
         3   carot        1         |             | 3   zoo   2 
         4   potatoe      1         |             |
         5   mango        2         |             |

您可以创建Peter出现的所有三元组都是不相关的.我只想要三元组(芒果，汤姆，动物园)，因为汤姆确实在所有可能性中只出现一次.我希望我的问题可以理解. :-)

All the triples you can create in which Peter occures are irrelevant. I want only triples like (mango, Tom, zoo), because Tom does occure only once in all possibilities. I hope my question ist understandable. :-)

推荐答案

我认为您必须进行子选择才能获得结果:

I think you have to do a subselect to get your result:

SELECT groceries.product, person.name, location.name
FROM person
LEFT JOIN groceries ON person.id = groceries.person_id
LEFT JOIN location ON person.id = location.person_id
WHERE person.id
IN (
SELECT person.id
FROM person
LEFT JOIN groceries ON person.id = groceries.person_id
LEFT JOIN location ON person.id = location.person_id
GROUP BY person.id
HAVING count( person.id ) =1
)

这篇关于如何在MySQL中从一个到多个相关表中选择唯一行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！