mysql分组 排序取每条记录中最后更新记录

本文章来给大家介绍关于mysql分组 排序取每条记录中最后更新记录,有需要了解的朋友可进入参考参考。

以下是 test 表,测试sql,代码如下:

1. CREATETABLEIFNOTEXISTS`test`(
2. `id`int(10)unsignedNOTNULLauto_increment,
3. `install`int(10)unsignedNOTNULL,
4. `day`int(10)unsignedNOTNULL,
5. `aid`int(10)unsignedNOTNULL,
6. PRIMARYKEY(`id`)
7. )ENGINE=MyISAMDEFAULTCHARSET=utf8AUTO_INCREMENT=12;
10. INSERTINTO`test`(`id`,`install`,`day`,`aid`)VALUES
11. (1,1232,20080808,1),
12. (2,2321,20080809,2),
13. (3,1236,20080810,3),
14. (5,4212,20080809,1),
15. (6,2312,20080810,1),
16. (7,1432,20080811,1),
17. (8,2421,20080808,2),
18. (9,4245,20080811,2),
19. (10,5654,20080810,2),
20. (11,412,20080808,3);

sql语句,代码如下:

1. SELECTA.*FROMtestA,
2. (SELECTaid,MAX(day)max_dayFROMtestGROUPBYaid)B
3. WHEREA.aid=B.aidANDA.day=B.max_day
4. ORDERBYa.installDESC

mysql实现分组排序并赋予序号的存贮过程,代码如下:

1. dropprocedureset_rank;
2. createprocedureset_rank()
3. begin
4. set@i=1;
5. set@number=(selectcount(1)fromsuppliers_performance);
6. updatesuppliers_performancesetscore_rank=0;
8. while@i<@number
9. do
10. begin
11. updatesuppliers_performancesetscore_rank=@iwhereidin(selectidfrom(selectidfromsuppliers_performancewherescore_rank=0orderbyscorelimit1)b);--phpfensi.com
12. set@i=@i+1;
13. end;
14. endWHILE;
15. end;