我希望选择具有不同电子邮件的行,请参见下面的示例表:
表名=用户
+----+---------+-------------------+-------------+
| id | title | email | commentname |
+----+---------+-------------------+-------------+
| 3 | test | rob@hotmail.com | rob |
| 4 | i agree | rob@hotmail.com | rob |
| 5 | its ok | rob@hotmail.com | rob |
| 6 | hey | rob@hotmail.com | rob |
| 7 | nice! | simon@hotmail.com | simon |
| 8 | yeah | john@hotmail.com | john |
+----+---------+-------------------+-------------+预期的结果将是:
+----+-------+-------------------+-------------+
| id | title | email | commentname |
+----+-------+-------------------+-------------+
| 5 | its ok| rob@hotmail.com | rob |
| 7 | nice! | simon@hotmail.com | simon |
| 8 | yeah | john@hotmail.com | john |
+----+-------+-------------------+-------------+不同的值应该是表示例id = 6中的最新条目,所需的SQL是什么?
发布于 2019-01-18 10:40:02
如果您使用的是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记录是rob@hotmail.com的最新记录。
发布于 2019-01-18 10:41:52
您可以在下面使用关联子查询
select * from table1 a
where id in (select max(id) from table1 b where a.email=b.email group by b.email)发布于 2019-08-03 15:54:01
如果id是唯一的或主键,您可以使用这个键:
select * from Users where id in (select max(id) from Users group by commentname)上面的一个将提高您的数据库性能,因为相关子查询是因为子查询使用外部查询的信息,而子查询对外部query.So中的每一行执行一次,如果'id‘是唯一的,我建议您使用我的答案。
https://stackoverflow.com/questions/54252163
复制相似问题