mysql - 如何避免 LEFT JOIN 查询中的重复记录？

Question

我有下表:

CREATE TABLE users (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(40) NOT NULL,
  PRIMARY KEY (id)
  );

id | name    
---|-------------
 1 | John
 2 | Franck
 3 | Peter
...

CREATE TABLE emails (
  iduser INT NOT NULL,
  email VARCHAR(40) NOT NULL,
  PRIMARY KEY (iduser, email),
  );
ALTER TABLE emails 
ADD FOREIGN KEY (iduser)
REFERENCES users (id) ON DELETE CASCADE;

iduser | email
-------|---------------
     1 | john@hotmail.com
     1 | john@other.com
     2 | franck@gmail.com
...

我需要获得以下结果:

id | Name   | Email
---|--------|----
 2 | Franck | franck@gmail.com (just one of the two)
 1 | John   | john@hotmail.com
 3 | Peter  |

使用

SELECT users.id, users.name, emails.email
  FROM users LEFT JOIN emails ON emails.iduser = users.id
  ORDER BY users.name

返回约翰的两行，但我只需要其中一行(而且我还需要没有匹配电子邮件的行)。如何通过单个查询来完成它？

Answer 1

假设您不关心返回哪封电子邮件。

SELECT users.id, users.name, max(emails.email)
FROM users 
 LEFT JOIN emails ON emails.iduser = users.id
GROUP by user.id, users.name
ORDER BY users.name