Question

MySQL中group by max如何提取最大的一条记录

Answer 1

tab_game
mysql> select * from tab_game;
+----+-----------+-----------+-----------------+
| id | game_code | game_name | game_desc |
+----+-----------+-----------+-----------------+
| 1 | 1001 | doudizhu | doudizhuhaowan |
| 2 | 1002 | majiang | cuomajiangbucuo |
| 3 | 1003 | fahongbao | hongbaoxiangyao |
+----+-----------+-----------+-----------------+

tab_game_version

mysql> select * from tab_game_version;
+----+-----------+------------------+-----------+
| id | game_code | game_version_int | game_size |
+----+-----------+------------------+-----------+
| 1 | 1001 | 11 | 5MB |
| 2 | 1001 | 12 | 6MB |
| 3 | 1001 | 15 | 8MB |
| 4 | 1002 | 10 | 1MB |
| 5 | 1002 | 20 | 2MB |
| 6 | 1003 | 1000 | 7MB |
| 7 | 1003 | 2000 | 9MB |
+----+-----------+------------------+-----------+

有这样的两张表，要查出每个游戏的最大的game_version_int值的记录

最终想得到的结果是

game_code game_name game_version_int game_size
1001 斗地主 15 8MB
1002 搓麻将 20 2MB
1003 发送包 2000 9MB

SQL 实现
第一种方式 不通用，只在MYSQL中可以用
mysql> select game_code,max(game_version_int),game_size
-> select * from tab_game_version t1 order by t1.gam
nt desc) t group by game_code ;
+-----------+-----------------------+-----------+
| game_code | max(game_version_int) | game_size |
+-----------+-----------------------+-----------+
| 1001 | 15 | 8MB |
| 1002 | 20 | 2MB |
| 1003 | 2000 | 9MB |
+-----------+-----------------------+-----------+

第二种方式，采用字符串拼接的方式，可以解决

mysql> select * from tab_game_version where
-> concat(game_code,game_version_int)
-> in (select concat(game_code,max(game_version_int)) from tab_game_version
t1 group by t1.game_code ) ;
+----+-----------+------------------+-----------+
| id | game_code | game_version_int | game_size |
+----+-----------+------------------+-----------+
| 3 | 1001 | 15 | 8MB |
| 5 | 1002 | 20 | 2MB |
| 7 | 1003 | 2000 | 9MB |
+----+-----------+------------------+-----------+

第三种方式,

mysql> select a.game_code,b.game_size,a.game_version_int,c.game_name from (sele
ct game_code,max(game_version_int) game_version_int from tab_game_version group
by game_code) a
-> left join tab_game_version b on a.game_code = b.game_code and a.game_vers
ion_int = b.game_version_int left join tab_game c on b.game_code = c.game_code
;
+-----------+-----------+------------------+-----------+
| game_code | game_size | game_version_int | game_name |
+-----------+-----------+------------------+-----------+
| 1001 | 8MB | 15 | doudizhu |
| 1002 | 2MB | 20 | majiang |
| 1003 | 9MB | 2000 | fahongbao |