按相反的顺序对rev字段进行排序,然后按id分组,id给出每个分组的第一行,即rev值最高的那一行。
SELECT * FROM (SELECT * FROM table1 ORDER BY id, rev DESC) X GROUP BY X.id;
测试在
http://sqlfiddle.com/
使用以下数据
CREATE TABLE table1
(`id` int, `rev` int, `content` varchar(11));
INSERT INTO table1
(`id`, `rev`, `content`)
VALUES
(1, 1, 'One-One'),
(1, 2, 'One-Two'),
(2, 1, 'Two-One'),
(2, 2, 'Two-Two'),
(3, 2, 'Three-Two'),
(3, 1, 'Three-One'),
(3, 3, 'Three-Three')
;
这在mysql 5.5和5.6中产生了以下结果
id rev content
1 2 One-Two
2 2 Two-Two
3 3 Three-Two