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

本文章來給大家介紹關於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;