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

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 t1gam

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 t1game_code ) ;

+----+-----------+------------------+-----------+

| id | game_code | game_version_int | game_size |

+----+-----------+------------------+-----------+

| 3 | 1001 | 15 | 8MB |

| 5 | 1002 | 20 | 2MB |

| 7 | 1003 | 2000 | 9MB |

+----+-----------+------------------+-----------+

第三种方式,

mysql> select agame_code,bgame_size,agame_version_int,cgame_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 agame_code = bgame_code and agame_vers

ion_int = bgame_version_int left join tab_game c on bgame_code = cgame_code

;

+-----------+-----------+------------------+-----------+

| game_code | game_size | game_version_int | game_name |

+-----------+-----------+------------------+-----------+

| 1001 | 8MB | 15 | doudizhu |

| 1002 | 2MB | 20 | majiang |

| 1003 | 9MB | 2000 | fahongbao |

select

mask

from

score

order

by

mask

desc

limit

50

order

by

mask

desc

是按照mask进行降序排列

查询到的结果自然是从大到小

limit

50指的是查询结果取前50个

以上就是关于MySQL中group by max如何提取最大的一条记录全部的内容，包括:MySQL中group by max如何提取最大的一条记录、从mysql数据库中获取最大数值、等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！