MySQL 在 GROUPing BY 时压缩空字段

Question

如果我有一个像这样的 MySQL 表，其中有两行代表特定的电子邮件地址，每行都不完整，但一起形成完整的记录:

+-------+-------+------------------+
| fname | sname | email            |
+-------+-------+------------------+
| John  |       | john@smith.com   |
|       | Smith | john@smith.com   |
| Sue   | Jones | sue@jones.com    |
| Jim   |       | jim@jim.com      |
+-------+-------+------------------+

我可以使用 GROUP BY 运行以下查询，以返回不同的电子邮件地址并接收 John Smith 的 fname 或 sname:

SELECT *
FROM users
GROUP BY email

+-------+-------+------------------+
| fname | sname | email            |
+-------+-------+------------------+
| John  |       | john@smith.com   |
| Sue   | Jones | sue@jones.com    |
| Jim   |       | jim@jim.com      |
+-------+-------+------------------+

有什么方法可以合并行中的空白字段(类似于COALESCE在SELECTing时与null一起工作的方式)相反给我以下结果？

+-------+-------+------------------+
| fname | sname | email            |
+-------+-------+------------------+
| John  | Smith | john@smith.com   |
| Sue   | Jones | sue@jones.com    |
| Jim   |       | jim@jim.com      |
+-------+-------+------------------+

Answer 1

使用 max() 获取每封电子邮件的“最高”值

SELECT email, 
       max(fname) as fname, 
       max(sname) as sname
FROM users
GROUP BY email