MySQL中group by max如何提取最大的一条记录
来源:互联网 发布:淘宝假冒伪劣产品 降权 编辑:程序博客网 时间:2024/06/05 04:57
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 |
+----+-----------+------------------+-----------+
1001 斗地主 15 8MB
1002 搓麻将 20 2MB
1003 发送包 2000 9MB
-> 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 |
+-----------+-----------------------+-----------+
-> 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 (select 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 |
+-----------+-----------+------------------+-----------+
- MySQL中group by max如何提取最大的一条记录
- MySQL中Max+Group BY查询最大值记录无效
- Mysql有无group by 对max函数查询结果的影响 ResultSet中有无记录受到影响
- SQL数据库group by的用法--从相同记录中查找任意一条记录
- 在mysql 中查询时间最大的一条记录
- 在mysql 中查询时间最大的一条记录
- mysql group by and max
- mysql和sql2008兼容group by中带函数的情况 max
- MySQL中MAX函数与Group By一起使用的注意事项
- MySQL中MAX函数与Group By一起使用的注意事项
- MySQL中MAX()函数与Group By一起使用的注意事项
- MySQL中MAX函数与Group By一起使用的注意事项
- mysql中group by子句和聚合函数MAX(),MIN(),SUM(),AVG()等的使用
- MySQL中MAX函数与Group By一起使用的注意事项
- MYSQL group by 分组后只取最大、最小值对应的记录方法
- 如何随机的从mysql表中取出一条记录?
- 如何随机的从mysql表中取出一条记录?
- group by分组后获得每组中时间最大的那条记录
- Smack报错:Trust anchor for certification path not found
- Bootstrap中的datetimepicker用法总结
- HDU 2033
- java 读取properties + 类反射的应用
- ppt制作干货
- MySQL中group by max如何提取最大的一条记录
- JSP ffmpeg实现视频截图
- 地理定位 (iOS 9.0)
- [BZOJ2809][Apio2012]dispatching(枚举&dfs序+主席树||可并堆||splay+启发式合并)
- CSS3 动画
- 线性时间内统计一个字符串中每个字符出现的次数
- 巴什博奕(Bash Game)
- 数据结构简单调试
- 未来攻城师