代码之家 › 专栏 › 技术社区 › Rafi

修改分组依据列以获取最新结果

group-by mysql

Rafi · 技术社区 · 16 年前

我很抱歉,如果这已经张贴或在互联网上。我找了很久才来这里

假设这是桌子:

+----+-------+----------+---------------------+
| id | name  | group_id | created_time        |
+----+-------+----------+---------------------+
|  1 | foo   | 1        | 2010-09-22 00:00:00 |
|  2 | rafi  | 2        | 2010-09-23 00:00:00 |
|  3 | rafi1 | 2        | 2010-09-24 00:00:00 |
|  4 | rafi2 | 2        | 2010-09-25 00:00:00 |
|  5 | bar   | 5        | 2010-09-26 00:00:00 |
|  6 | baz   | 6        | 2010-09-27 00:00:00 |
|  7 | baz1  | 6        | 2010-09-26 00:00:00 |
|  8 | rafi3 | 2        | 2010-09-24 00:00:00 |
|  9 | baz2  | 6        | 2010-09-30 00:00:00 |
+----+-------+----------+---------------------+

我要根据组ID对其进行分组,并按创建时间描述(先更新)对其进行排序。

但当我说

SELECT id,name,group_id,created_time FROM test group by group_id ORDER BY id desc;

我明白了

+----+------+----------+---------------------+
| id | name | group_id | created_time        |
+----+------+----------+---------------------+
|  6 | baz  | 6        | 2010-09-27 00:00:00 |
|  5 | bar  | 5        | 2010-09-26 00:00:00 |
|  2 | rafi | 2        | 2010-09-23 00:00:00 |
|  1 | foo  | 1        | 2010-09-22 00:00:00 |
+----+------+----------+---------------------+

我想要的是像这样的东西

+----+------+----------+---------------------+
| id | name | group_id | created_time        |
+----+------+----------+---------------------+
|  9 | baz2 | 6        | 2010-09-30 00:00:00 |
|  5 | bar  | 5        | 2010-09-26 00:00:00 |
|  5 | rafi2| 2        | 2010-09-25 00:00:00 |
|  1 | foo  | 1        | 2010-09-22 00:00:00 |
+----+------+----------+---------------------+

我试过了

SELECT max(date(created_time)) as foo,name,group_id FROM test group by group_id ORDER BY foo desc;

我知道日期是对的,但不知道名字是对的。

2 回复 | 直到 16 年前

Jagmag 16 年前

这个怎么样?使用子查询是一个选项-如果是,这可能有效!

SELECT id, name, group_id WHERE id IN (Select max(id) FROM test group by group_id) ORDER BY id desc;

pierroz 16 年前

这个问题符合你的需要吗?

SELECT t1.id, t1.name, t1.group_id FROM Test t1
INNER JOIN
  (SELECT MAX(id) as maxid FROM Test GROUP BY group_id) t2
ON t2.maxid = t1.id
ORDER BY t1.id DESC;

编辑:

如果要按日期时间字段排序,可以稍微修改上面的查询:

SELECT t1.id, t1.name, t1.group_id, t1.created_date FROM Test t1
INNER JOIN
  (SELECT MAX(created_date) as maxdate, group_id FROM Test GROUP BY group_id) t2
ON (t2.maxdate = t1.created_date AND t2.group_id = t1.group_id)
ORDER BY t1.created_date DESC;

你还在找吗?

推荐文章

Bard.Mus · 迁移后的数据库字符集环境

1 年前

tinkerr · 如何在2025年在MacOS上安装MySQL 5.7

1 年前