MySql select语句获取每个client_id的最新行

Question

嗨，我有一个mysql select语句的问题，我想不通了，

表client_directory_data

id整型，验证整型，client_id整型，创建时间戳，描述长文本

select * from client_directory_data where verified =1 order by created desc

但这会为每个client_id选择多行

我需要做的是选择每个具有验证=1的client_id，但只获得每个client_id的最新行，我希望这是有意义的。

Answer 1

这是有道理的，也是一个经典的问题。

假设使用the most recent row is the one with highest id，您可以使用：

SELECT *
FROM client_directory_data c
LEFT JOIN client_directory_data d ON c.client_id = d.client_id AND d.verified = 1 AND d.id > c.id
WHERE d.id IS NULL
    AND c.verified = 1;

您可以对此查询模式here进行解释。

Answer 2

这是我一直面临的问题。幸运的是，这里有一个很好的小技巧：

SELECT
client_id,
SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY created DESC),",",1) AS `id`
FROM client_directory_data
WHERE verified = 1
GROUP BY client_id

如果您想要整个行，您可以像这样连接到它：

SELECT
*
FROM (
  SELECT
  client_id,
  SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY created DESC),",",1) AS `id`
  FROM client_directory_data
  WHERE verified = 1
  GROUP BY client_id
) ids
JOIN client_directory_data USING (id);

当然，如果您无论如何都要按索引字段排序(因此您无论如何都可以有效地联接)，那么最好使用MAX(id) AS id，尽管它实际上对性能的影响很小。使用MAX()的主要原因实际上是为了使代码更简单。它还避免了字段包含逗号(您可以使用组连接的不同分隔符)或达到最大GROUP_CONCAT长度(可以使用SET group_concat_max_len = xxx;扩展，但无论如何都只会导致警告)时可能遇到的陷阱。

我可以理解为什么直觉上看起来它会有性能问题，但它实际上是我为这些查询找到的最佳性能方法-特别是在大型表上。

下面是我从目前可用的一些较大的表中获得的一些基准测试，这些表比较了这个线程中的三种方法。

查询A：(~5000条记录，~900条结果，非索引字段)

• GROUP_CONCAT方法: 0.0100秒
• 最大方法: 0.102秒
• 左连接方法: 0.0082秒

查询B：(~300,000条记录，~95,000条结果)

• GROUP_CONCAT方法: 1.8618秒
• 最大方法: 1.7904秒
• 左连接方法: 6.4649秒

查询C：(~300,000条记录，~7条结果)

• GROUP_CONCAT方法: 0.103秒
• 最大方法: 0.0102秒
• LEFT JOIN方法：(4小时后厌烦)

查询D：(~500,000条记录，分组字段~5,000个不同值)

• 分组方式: 0.1355秒
• 最大方式: 0.0429秒
• 左连接方式：( 10分钟后我感到厌烦)

Answer 3

将id设置为表client_directory_data的主键