如何根据MySQL中的条件对记录进行分组（group_by）

I have users table, each user have multiple records. I need to fetch the latest inserted records of each user. How is it possible using MySQL

我有用户表，每个用户都有多条记录。我需要获取每个用户的最新插入记录。怎么可能使用MySQL

users table

用户表

id      name   value    updated_at

1       abc    123      2011-02-03
2       xyz    qwe      2011-04-03
3       abc    asd      2011-08-08
4       xyz    poi      2011-08-07

My output should be

我的输出应该是

id      name   value    updated_at

3       abc    asd      2011-08-08
4       xyz    poi      2011-08-07

I used Queries like

我使用了查询

 select id,name,value,updated_at  from users group by name having max(updated_at)

 select id,ep_name,position,max(updated_at)  from test1 group by ep_name having max(updated_at)

But I was not able to get the desired result. I get the max(updated_at) value but not able to get the matching row. Please help

但我无法得到理想的结果。我得到max（updated_at）值但无法获得匹配的行。请帮忙

Note: Can this be done without using sub-query. Please help

注意：这可以在不使用子查询的情况下完成。请帮忙

Thanks in advance

提前致谢

3 个解决方案

#1

SELECT
  users.id,users.name, users.`value`, users.`updated_at`
FROM
  users
INNER JOIN
(
 SELECT name, MAX(updated_at) AS `updated_at`
 FROM
  users
 GROUP BY
  name
) max_users
ON
  users.name = max_users.name
AND
  users.updated_at = max_users.updated_at

Dan thanks a lot... :) small change in group by to match my output

Dan非常感谢... :)组中的小变化以匹配我的输出

#2

No, this cannot be done without a subquery. A subquery is part of a single query.

不，没有子查询就无法做到这一点。子查询是单个查询的一部分。

SELECT
  users.*
FROM
  users
INNER JOIN
  (
    SELECT
      id,
      MAX(updated_at) AS `updated_at`
    FROM
      users
    GROUP BY
      id
  ) max_users
ON
  users.id = max_users.id
AND
  users.updated_at = max_users.updated_at

#3

You are right Mr. Amal you will not get the answer what you have specified above.Query by Dan will return with all results.

你是对的阿迈勒先生你不会得到你上面指定的答案。丹的查询将返回所有结果。

Try this definitely you will be on your way

试试这个肯定你会在路上

SELECT  test.* FROM  test INNER JOIN (SELECT MAX(updated_at) AS `updated_at` FROM test GROUP BY  name) max_users
ON
  test.updated_at = max_users.updated_at

Second option for you:

第二个选择：

SELECT  * FROM  test WHERE updated_at IN(SELECT MAX(updated_at) FROM test GROUP BY  name)

which is much simpler than first one

这比第一个简单得多

#1

SELECT
  users.id,users.name, users.`value`, users.`updated_at`
FROM
  users
INNER JOIN
(
 SELECT name, MAX(updated_at) AS `updated_at`
 FROM
  users
 GROUP BY
  name
) max_users
ON
  users.name = max_users.name
AND
  users.updated_at = max_users.updated_at