mysql分組 排序取每條記錄中最後更新記錄

以下是 test 表，測試sql

代碼如下

複製代碼

CREATE TABLE IF NOT EXISTS `test` ( `id` int(10) unsigned NOT NULL auto_increment, `install` int(10) unsigned NOT NULL, `day` int(10) unsigned NOT NULL, `aid` int(10) unsigned NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=12 ; INSERT INTO `test` (`id`, `install`, `day`, `aid`) VALUES (1, 1232, 20080808, 1), (2, 2321, 20080809, 2), (3, 1236, 20080810, 3), (5, 4212, 20080809, 1), (6, 2312, 20080810, 1), (7, 1432, 20080811, 1), (8, 2421, 20080808, 2), (9, 4245, 20080811, 2), (10, 5654, 20080810, 2), (11, 412, 20080808, 3);

sql語句

代碼如下	複製代碼
SELECT A.* FROM test A, (SELECT aid, MAX(day) max_day FROM test GROUP BY aid) B WHERE A.aid = B.aid AND A.day = B.max_day ORDER BY a.install DESC

mysql實現分組排序並賦予序號的存貯過程

代碼如下

複製代碼

drop procedure set_rank;  create procedure set_rank()  begin  set @i=1;  set @number=(select count(1) from suppliers_performance);  update suppliers_performance set score_rank=0;    while @i<@number  do  begin      update suppliers_performance set score_rank=@i where id in (select id from (select id from suppliers_performance where score_rank=0 order by score limit 1) b);      set @i=@i+1;  end;  end WHILE;  end;