ORDER BY 日期和时间 BEFORE GROUP BY 在 mysql 中的名称 [英] ORDER BY date and time BEFORE GROUP BY name in mysql

问题描述

我有一张这样的桌子:

name    date         time
tom | 2011-07-04 | 01:09:52
tom | 2011-07-04 | 01:09:52
mad | 2011-07-04 | 02:10:53
mad | 2009-06-03 | 00:01:01

我想要最老的名字:

SELECT * 
ORDER BY date ASC, time ASC 
GROUP BY name

(->不起作用！)

现在它应该先让我生气(有更早的日期)然后是汤姆

now it should give me first mad(has earlier date) then tom

但是使用 GROUP BY name ORDER BY date ASC, time ASC 会先给我更新的 mad，因为它在排序之前先分组！

but with GROUP BY name ORDER BY date ASC, time ASC gives me the newer mad first because it groups before it sorts!

再次:问题是我不能在分组之前按日期和时间排序，因为 GROUP BY 必须在 ORDER BY 之前！

again: the problem is that i can't sort by date and time before i group because GROUP BY must be before ORDER BY!

推荐答案

另一种方法:

SELECT * 
FROM (
    SELECT * FROM table_name
    ORDER BY date ASC, time ASC 
) AS sub
GROUP BY name

GROUP BY 对它命中的第一个匹配结果进行分组.如果第一个匹配的匹配恰好是您想要的匹配，那么一切都应该按预期进行.

GROUP BY groups on the first matching result it hits. If that first matching hit happens to be the one you want then everything should work as expected.

我更喜欢这种方法，因为子查询具有逻辑意义，而不是用其他条件填充它.

I prefer this method as the subquery makes logical sense rather than peppering it with other conditions.

这篇关于ORDER BY 日期和时间 BEFORE GROUP BY 在 mysql 中的名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！