从MySQL数据中获取最后一个mod_date

Question

我的数据库中有以下数据

|NO | name  |exp   | mod_date          | 
+---+-------+------+-------------------+
|1  | dede  |  1   |2012-11-07 01:04:26|
|2  | dede  |  10  |2012-11-07 01:05:42|
|3  | GoGo  |  20  |2012-11-07 03:05:40|
|4  | baba  |  23  |2012-11-07 05:22:50|
|5  | baba  |  35  |2012-11-07 08:06:57|
|6  | baba  |  50  |2012-11-08 06:32:13|
|7  | rara  |  43  |2012-11-13 06:40:35|

我想要获取每个名称的最新更新mod_date：

|NO | name  |exp   | mod_date          | 
+---+-------+------+-------------------+
|2  | dede  |  10  |2012-11-07 01:04:26|
|3  | GoGo  |  20  |2012-11-07 03:05:40|
|6  | baba  |  50  |2012-11-08 06:32:13|
|7  | rara  |  43  |2012-11-13 06:40:35|

我尝试了这个解决方案，但它不起作用(same question)

SELECT no,name,exp,max(date) FROM skill_exp
GROUP BY name

我从那个查询中得到了这个结果

|NO | name  |exp   | mod_date          | 
+---+-------+------+-------------------+
|1  | dede  |  1   |2012-11-07 01:05:42|
|3  | GoGo  |  20  |2012-11-07 03:05:40|
|4  | baba  |  23  |2012-11-07 05:22:50|
|7  | rara  |  43  |2012-11-13 06:40:35|

Answer 1

子查询背后的思想是，它分别获取每个名称的最新(最大) mod_Date。然后将其结果连接回原始表，以获得每行中的其他列。

SELECT  a.*
FROM    tableName a
        INNER JOIN
        (
            SELECT name, MAX(mod_date) maxDate
            FROM tableName
            GROUP BY name
        ) s ON  a.name = s.name AND
                a.mod_date = s.maxDate

• SQLFiddle Demo

Answer 2

使用SELF JOIN的一种更简单的方法

select max(t1.no) NO,t1.name,max(t1.exp),max(t1.mod_date) from tablename t1
inner join tablename t2
on t1.name=t2.name
where t1.mod_date>=t2.mod_date
group by t1.name order by t1.No