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

问题描述

我有这样一个表：

 名字日期时间
 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 
  

我希望名字最早：

 选择* 
 ORDER BY日期ASC，时间ASC 
 GROUP BY名称
  

（ - >不起作用！）

现在它应该给我第一次疯狂（有更早的日期）然后tom

但带有 GROUP BY名称ORDER BY日期ASC，时间ASC 首先让我感到新的疯狂，因为它组再次分析：

再次提示：问题在于我无法按日期和时间排序，因为GROUP BY必须位于ORDER BY之前！

解决方案

另一种方法：

  SELECT * 
 FROM（
 SELECT * 
 ORDER BY日期ASC，时间ASC 
）AS sub 
 GROUP BY名称
  

GROUP BY组找到它命中的第一个匹配结果。如果第一个匹配匹配碰巧是你想要的匹配匹配，那么一切都应该按预期工作。

我更喜欢这种方法，因为子查询是合乎逻辑的，而不是与其他条件。

i have a table like this:

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

i want oldest name first:

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

(->doesn't work!)

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

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

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

解决方案

Another method:

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

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屋！