mysql - 基于单个不同列选择唯一行 - MySQL

Question

我想选择具有不同电子邮件的行，请参见下面的示例表：

表名 = 用户

 +----+---------+-------------------+-------------+
   | id | title   | email             | commentname |
   +----+---------+-------------------+-------------+
   |  3 | test    | [e-mail]   | rob         |
   |  4 | i agree | [e-mail]   | rob         |
   |  5 | its ok  | [e-mail]   | rob         |
   |  6 | hey     | [e-mail]   | rob         |
   |  7 | nice!   | [e-mail] | simon       |
   |  8 | yeah    | [e-mail]  | john        |
   +----+---------+-------------------+-------------+

期望的结果是：

 +----+-------+-------------------+-------------+
   | id | title | email             | commentname |
   +----+-------+-------------------+-------------+
   |  5 | its ok| [e-mail]   | rob         |
   |  7 | nice! | [e-mail] | simon       |
   |  8 | yeah  | [e-mail]  | john        |
   +----+-------+-------------------+-------------+

不同的值应该是表示例中id = 6 的最新条目 所需的 SQL 是什么？

标签： mysql

Answer 1

如果您使用的是 MySQL 5.7 或更早版本，那么您可以将您的表加入到子查询中，该子查询会查找每封电子邮件的最新记录：

SELECT t1.id, t1.title, t1.email, t1.commentname
FROM yourTable t1
INNER JOIN
(
    SELECT email, MAX(id) AS latest_id
    FROM yourTable
    GROUP BY email
) t2
    ON t1.email = t2.email AND t1.id = t2.latest_id;

如果您使用的是 MySQL 8+，那么只需ROW_NUMBER在此处使用：

WITH cte AS (
    SELECT id, title, email, commentname,
        ROW_NUMBER() OVER (PARTITION BY email ORDER BY id DESC) rn
    FROM yourTable
)

SELECT id, title, email, commentname
FROM cte
WHERE rn = 1;

注意：您的预期输出可能有问题，并且id = 6记录是最新的[e-mail].