mysql - 选择具有最新时间戳的行

Question

实际上我的数据库由两个主表组成:城市和天气，我想选择天气表的最新条目。

它看起来大约是这样的:

plz     cities_plz description name         modified
9020      9020     looks great Klagenfurt   24.04.2017
9020      9020     rain        Klagenfurt   25.04.2017
9020      9020     rain        Klagenfurt   10.04.2017
9100      9100     sunny       Villach      25.04.2017

所以现在，我只想获取最新的条目。我已经用这个命令尝试过了:

select   * 
from     weather, cities 
WHERE    weather.cities_plz = cities.plz 
ORDER BY modfified DESC;

问题是，这个命令没有删除重复项......有任何想法吗？将不胜感激!

Answer 1

您可以使用LIMIT来获取最后修改的条目:

SELECT * 
FROM weather, cities 
WHERE weather.cities_plz = cities.plz
ORDER BY modified DESC
LIMIT 1;

您想要每个城市的最后一个条目:

SELECT DISTINCT * 
FROM weather, cities 
WHERE weather.cities_plz = cities.plz
    AND modified = (SELECT MAX(modified) FROM weather WHERE weather.cities_plz = plz)