mysql - 如何使用 order by 和 limit 获取多行

Question

我有一个mysql表，该表的结构如下:

CREATE TABLE IF NOT EXISTS `salary_log` (`salary_id` int(11) NOT NULL AUTO_INCREMENT, `salary` int(11) NOT NULL, `name` varchar(200) NOT NULL, `create_date` date NOT NULL, PRIMARY KEY (`salary_id`)) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

INSERT INTO `salary_log` (`salary_id`, `salary`, `name`, `create_date`) VALUES (1, 5000, 'a', '2013-05-02'), (2, 8000, 'a', '2013-05-08'), (3, 4500, 'b', '2013-05-10'), (4,6000, 'c', '2013-05-08'), (5, 8000, 'b', '2013-05-17');

从上表中我需要获取每个员工的最新记录。我尝试了以下查询:

SELECT * FROM `salary_log` GROUP BY `name` ORDER BY `create_date` DESC;

但它返回的是错误的数据。有人可以帮我吗？

Answer 1

试试这个:

SELECT s1.* 
FROM `salary_log` AS s1
INNER JOIN
(
    SELECT name, MAX(create_date) AS maxDate
    FROM salary_log
    GROUP BY `name`
) AS s2  ON s1.name        = s2.name 
        AND s1.create_date = s2.maxDate;