在表中选择最新更改的行

Question

我有一个Mysql表test和id, changedate, name字段。

有些名称有多个转换日期。

我可以选择所有的行

SELECT id, changedate, name FROM `test` ORDER BY changedate;

如何仅为每个名称选择该表的最新行？

Answer 1

如果您不需要id (或者问题中没有包括的任何其他列)，而只需要name和最新的changedate，那么您只需要一个简单的GROUP BY查询--以及一个(name, changedate)索引以提高效率：

SELECT name, MAX(changedate) AS changedate
FROM test 
GROUP BY name ;

如果需要更多列，解决方法之一是将上述查询放在派生表中，并将JOIN放到原始表中：

-- Query 1
SELECT t.*
FROM test AS t
  JOIN
  ( SELECT name, MAX(changedate) AS changedate
    FROM test 
    GROUP BY name
  ) AS g
    ON  g.name = t.name
    AND g.changedate = t.changedate ;

您还可以使用LEFT JOIN或NOT EXISTS解决方案：

-- Query 2a
SELECT t1.*
FROM test AS t1
  LEFT JOIN test AS t2 
    ON  t1.name = t2.name
    AND t1.g.changedate < t2.changedate
WHERE t2.name IS NULL ;

-- Query 2b
SELECT t1.*
FROM test AS t1
WHERE NOT EXISTS
      ( SELECT *
        FROM test AS t2 
        WHERE t1.name = t2.name
          AND t1.changedate < t2.changedate
      ) ;

Answer 2

SELECT * FROM 
(SELECT id, changedate, name FROM test 
  ORDER BY changedate DESC)
as output_name GROUP BY name;