MySQL - 加入两个没有重复的表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4819478/
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
MySQL - Joining two tables without duplicates?
提问by Wige
I have two tables that I am trying to join. One contains a list of customers, the other is a list of orders. I am trying to formulate a query that will allow me to select all of the customers listed in the table customerswho have at least one order in the table orders. However, I do not want to get duplicates for those customers who have multiple orders. Any suggestions how I can accomplish this?
我有两个要加入的表。一个包含客户列表，另一个是订单列表。我正在尝试制定一个查询，该查询将允许我选择表customer 中列出的所有客户，这些客户在表orders中至少有一个订单。但是，我不想为那些有多个订单的客户获得重复项。有什么建议我可以做到这一点吗？
I know this is probably a common issue, however I have no idea what this type of query would be called so that I could search for an answer. Any suggestions would be greatly appreciated. Thanks.
我知道这可能是一个常见问题，但是我不知道这种类型的查询会被称为什么，以便我可以搜索答案。任何建议将不胜感激。谢谢。
回答by Michael Kopinsky
It's much simpler than you may think:
它比您想象的要简单得多：
select distinct(customer_id) from orders;
select distinct(customer_id) from orders;
Edit: If you actually want to get the full info on the customer,
编辑：如果您确实想获得有关客户的完整信息，
select * from customers where customer_id in (select distinct(customer_id) from orders);
select * from customers where customer_id in (select distinct(customer_id) from orders);
回答by OMG Ponies
Use:
用：
SELECT c.*
FROM CUSTOMERS c
WHERE EXISTS (SELECT NULL
FROM ORDERS o
WHERE o.custeromid = c.id)
The IN clause is an alternative, but EXISTS works better for duplicates because it returns true on the first duplicate so it doesn't process the entire table.
IN 子句是一种替代方法，但 EXISTS 更适用于重复项，因为它在第一个重复项上返回 true，因此它不会处理整个表。
回答by Klaus Byskov Pedersen
select customers.id, customers.name, count(orders.id)
from customers
inner join orders on orders.customer_id = customers.Id
group by customers.id, customers.name
having count(orders.id) > 0
回答by orangepips
SELECT
c.id,
c.name
FROM
customer c
INNER JOIN order o ON o.customer_id = c.id
GROUP BY
c.id,
c.name
HAVING
COUNT(o.id) >= 1
Can't remember if HAVING or GROUP BY comes first.
不记得是 HAVING 还是 GROUP BY 排在第一位。
