SQL：只选择一行具有相同值的表

Question

我对 sql 有点陌生，对于我的项目，我需要做一些数据库排序和过滤：

假设我的数据库如下所示：

 ==========================================
|  id       |       email        | name
==========================================
|   1       |  [e-mail]      | John
|   2       |  [e-mail]      | Peter
|   3       |  [e-mail]      | Steward
|   4       |  [e-mail]      | Ethan
|   5       |  [e-mail]      | Bob
|   6       |  [e-mail]      | Patrick
==========================================

我应该怎么做才能只返回具有相同电子邮件的最后一列：

 ==========================================
|  id       |       email        | name
==========================================
|   3       |  [e-mail]      | Steward
|   5       |  [e-mail]      | Bob
|   6       |  [e-mail]      | Patrick
==========================================

提前致谢！

原文由 Z3r0byte 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

SQL查询：

     SELECT * FROM test.test1  WHERE id IN (
  SELECT MAX(id) FROM test.test1 GROUP BY email
);

希望这能解决您的问题。谢谢。

原文由 Saibal Roy 发布，翻译遵循 CC BY-SA 3.0 许可协议

Answer 2

您可以使用以下查询来获取 MAX id 每个 email --- 的值：

 SELECT email, MAX(id)
FROM mytable
GROUP BY email

使用上述查询作为派生表，您可以获得整个记录：

 SELECT t1.*
FROM mytable AS t1
JOIN (
  SELECT email, MAX(id) AS id
  FROM mytable
  GROUP BY email
) AS t2 ON t1.id = t2.id

原文由 Giorgos Betsos 发布，翻译遵循 CC BY-SA 3.0 许可协议