MySQL - 按照DESC的顺序分组 [英] MySQL - Group by with Order by DESC

问题描述

表：uuid，版本，日期时间

版本不是唯一的，但想法是仅获取具有给定uuid的最新日期时间的行

SELECT * FROM表WHERE uuid ='bla'GROUP BY版本ORDER BY日期时间desc

...当然会得到datetime asc结果 - 是否有一种方法可以通过desc来预订组，从而只提取最新版本？

<由于表只有这3个字段，并且您正在使用uid进行过滤，所以您可以在不加入JOIN的情况下使用MAX：

  SELECT版本，MAX（日期时间）Maxdatetime 
 FROM表$ b $ WHERE uuid ='bla'
 GROUP BY版本
  

但是，如果表中有更多字段，或者您未按 uid - 您需要先为每个版本获取MAX日期时间，然后选择该行：

  SELECT t.uuid ，t.version，t.datetime 
 FROM表t JOIN（
 SELECT版本，MAX（日期时间）Maxdatetime 
 FROM表$ b $ WHERE uuid ='bla'
 GROUP BY版本
）r ON t.version = r .version AND t.datetime = r.Maxdatetime 
 WHERE t.uuid ='bla'
 ORDER BY t.datetime desc 
  

table: uuid, version, datetime

version is not unique, but the idea is to fetch only the rows with the latest datetime for a given uuid

SELECT * FROM table WHERE uuid='bla' GROUP BY version ORDER BY datetime desc

... of course gets datetime asc results -- is there a way to "preorder" the group by to desc, so that only the latest version is fetched?

解决方案

since the table only has those 3 field, and you are filtering by uid you can just use the MAX without the JOIN:

SELECT version, MAX(datetime) Maxdatetime
FROM table
WHERE uuid='bla'
GROUP BY version

However, if the table had more fields, or you are not filtering by uid - you need to first get the MAX datetime for each version, then select the row:

SELECT t.uuid, t.version, t.datetime 
FROM table t JOIN (
    SELECT version, MAX(datetime) Maxdatetime
    FROM table
    WHERE uuid='bla'
    GROUP BY version
) r ON t.version = r.version AND t.datetime = r.Maxdatetime
WHERE t.uuid='bla'
ORDER BY t.datetime desc

这篇关于MySQL - 按照DESC的顺序分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！