mysql获取分组后每组的最大值实例详解

 mysql获取分组后每组的最大值实例详解

1. 测试数据库表如下：

create table test
(
  `id` int not null auto_increment,
  `name` varchar(20) not null default '',
  `score` int not null default 0,
  primary key(`id`)
)engine=InnoDB CHARSET=UTF8;

2. 插入如下数据：

mysql> select * from test;
+----+----------+-------+
| id | name   | score |
+----+----------+-------+
| 1 | jason  |   1 |
| 2 | jason  |   2 |
| 3 | jason  |   3 |
| 4 | linjie  |   1 |
| 5 | linjie  |   2 |
| 6 | linjie  |   3 |
| 7 | xiaodeng |   1 |
| 8 | xiaodeng |   2 |
| 9 | xiaodeng |   3 |
| 10 | hust   |   2 |
| 11 | hust   |   3 |
| 12 | hust   |   1 |
| 13 | haha   |   1 |
| 14 | haha   |   2 |
| 15 | dengzi  |   3 |
| 16 | dengzi  |   4 |
| 17 | dengzi  |   5 |
| 18 | shazi  |   3 |
| 19 | shazi  |   4 |
| 20 | shazi  |   2 |
+----+----------+-------+

3. 下面是重点，目的是要按照name分组，然后分组后，获取每组中score分数最多的，sql如下

select a.* from test a inner join (select name,max(score) score from test group by name)b on a.
name=b.name and a.score=b.score order by a.name;

当然，上面的最后的order by a.name可以去掉

4. 测试结果如下:

+----+----------+-------+
| id | name   | score |
+----+----------+-------+
| 3 | jason  |   3 |
| 6 | linjie  |   3 |
| 9 | xiaodeng |   3 |
| 11 | hust   |   3 |
| 14 | haha   |   2 |
| 17 | dengzi  |   5 |
| 19 | shazi  |   4 |
+----+----------+-------+

5. 网上很多方法都是错误的，比如如下一些，亲测是不行的

select * from (select * from test order by score desc) t group by name order by score desc limit 4;
select score,max(score) from test group by name;
select * from test where score in (select max(score) from test group by name);
select * from test where score in (select substring_index(group_concat(score order by score desc separator ','),',',1) from test group by name); 

select * from (select name,score,ROW_NUMBER() over(group by name order by score desc) as rowNum from test) rank where rank.rowNum <=1 order by rank.score desc; 

select * from( select StoresNo,[CustomerCaseNo],[PaymentsTime], ROW_NUMBER() over(partition by CustomerCaseNo order by [PaymentsTime] desc) as rowNum
from BAL_paymentsSwiftInfo where StoresNo='zq00000034') ranked where ranked.rowNum <= 1 order by ranked.CustomerCaseNo, ranked.PaymentsTime desc 

select * from (select * from test order by score desc) as a group by a.name;

感谢阅读，希望能帮助到大家，谢谢大家对本站的支持！

赞 (0)

相关推荐

  • mysql使用GROUP BY分组实现取前N条记录的方法

    mysql使用GROUP BY分组实现取前N条记录的方法

    本文实例讲述了mysql使用GROUP BY分组实现取前N条记录的方法.分享给大家供大家参考,具体如下: MySQL中GROUP BY分组取前N条记录实现 mysql分组,取记录 GROUP BY之后如何取每组的前两位下面我来讲述mysql中GROUP BY分组取前N条记录实现方法. 这是测试表(也不知道怎么想的,当时表名直接敲了个aa,汗~~~~): 结果: 方法一: 复制代码 代码如下: SELECT a.id,a.SName,a.ClsNo,a.Score FROM aa a LEFT J

  • 详解MySQL中的分组查询与连接查询语句

    分组查询 group by group by 属性名 [having 条件表达式][ with rollup] "属性名 "指按照该字段值进行分组:"having 条件表达式 "用来限制分组后的显示,满足条件的结果将被显示:with rollup 将会在所有记录的最后加上一条记录,该记录是上面所有记录的总和. 1)单独使用 group by 单独使用,查询结果只显示一个分组的一条记录. 实例: select * from employee group by sex;

  • mysql分组取每组前几条记录(排名) 附group by与order by的研究

    mysql分组取每组前几条记录(排名) 附group by与order by的研究

    --按某一字段分组取最大(小)值所在行的数据 复制代码 代码如下: /* 数据如下: name val memo a 2 a2(a的第二个值) a 1 a1--a的第一个值 a 3 a3:a的第三个值 b 1 b1--b的第一个值 b 3 b3:b的第三个值 b 2 b2b2b2b2 b 4 b4b4 b 5 b5b5b5b5b5 */ --创建表并插入数据: 复制代码 代码如下: create table tb(name varchar(10),val int,memo varchar(20)

  • MySQL分组查询Group By实现原理详解

    MySQL分组查询Group By实现原理详解

    由于GROUP BY 实际上也同样会进行排序操作,而且与ORDER BY 相比,GROUP BY 主要只是多了排序之后的分组操作.当然,如果在分组的时候还使用了其他的一些聚合函数,那么还需要一些聚合函数的计算.所以,在GROUP BY 的实现过程中,与 ORDER BY 一样也可以利用到索引. 在MySQL 中,GROUP BY 的实现同样有多种(三种)方式,其中有两种方式会利用现有的索引信息来完成 GROUP BY,另外一种为完全无法使用索引的场景下使用.下面我们分别针对这三种实现方式做一个分

  • mysql列转行以及年月分组实例

    如下所示: SELECT count(DISTINCT(a.rect_id)) zcount, a.job_dept,  DATE_FORMAT(submit_date, '%Y-%m') zsubmit_date  FROM  表名 a  WHERE  a.statu = 3  AND a.rstatu = 2  AND a.job_dept IN ('19', '20', '21')  GROUP BY  a.job_dept,  DATE_FORMAT(submit_date, '%Y-%

  • mysql 将字段time按天/月/年分组

    假设表中有一个字段time,格式为Unix时间戳,现需要按照该字段统计每天的记录数. 方法: 利用convert函数convert(char(10),time,120) as time,然后group by time即可. 原理:convert函数将时间戳转换成ODBC标准时间(120参数指定),然后取前十位,则刚好得到完整日期(不含时分秒),再分组即可. 按月,按年分组方法一样,只需要修改convert()方法第一个参数的长度.

  • MYSQL每隔10分钟进行分组统计的实现方法

    前言 本文的内容主要是介绍了MYSQL每隔10分钟进行分组统计的实现方法,在画用户登录.操作情况在一天内的分布图时会非常有用,之前我只知道用「存储过程」实现的方法(虽然执行速度快,但真的是太不灵活了),后来学会了用高级点的「group by」方法来灵活实现类似功能. 正文: -- time_str '2016-11-20 04:31:11' -- date_str 20161120 select concat(left(date_format(time_str, '%y-%m-%d %h:%i'

  • Mysql利用group by分组排序

    昨天有个需求对数据库的数据进行去重排名,同一用户去成绩最高,时间最短,参与活动最早的一条数据进行排序.我们可以利用MySQL中的group by的特性. MySQL的group by与Oracle有所不同,查询得字段可以不用写聚合函数,查询结果取得是每一组的第一行记录. 利用上面的特点,可以利用mysql实现一种独特的排序: 首先先按某个字段进行order by,然后把有顺序的表进行分组,这样每组的成员都是有顺序的,而mysql默认取得分组的第一行.从而得到每组的最值. select id, (

  • mysql获取分组后每组的最大值实例详解

     mysql获取分组后每组的最大值实例详解 1. 测试数据库表如下: create table test ( `id` int not null auto_increment, `name` varchar(20) not null default '', `score` int not null default 0, primary key(`id`) )engine=InnoDB CHARSET=UTF8; 2. 插入如下数据: mysql> select * from test; +---

  • 基于PHP+Mysql简单实现了图书购物车系统的实例详解

    PHP+Mysql简单实现了图书购物车 本文主要讲述如何通过PHP+HTML简单实现图书购物车的功能,这是提取我们php项目的部分内容.主要内容包括: 1.通过JavaScript和Iframe实现局部布局界面     2.PHP如何定义类实现访问数据库功能     3.实现简单的添加购物车功能     4.实现了后台管理前台的页面     由于这个项目是在期末完成,PHP只是刚学的,比较简单. 效果图如下: 这是后台管理的页面: 这是前台页面: index.php页面: <!DOCTYPE h

  • MySQL数据库优化之分表分库操作实例详解

    本文实例讲述了MySQL数据库优化之分表分库操作.分享给大家供大家参考,具体如下: 分表分库 垂直拆分 垂直拆分就是要把表按模块划分到不同数据库表中(当然原则还是不破坏第三范式),这种拆分在大型网站的演变过程中是很常见的.当一个网站还在很小的时候,只有小量的人来开发和维护,各模块和表都在一起,当网站不断丰富和壮大的时候,也会变成多个子系统来支撑,这时就有按模块和功能把表划分出来的需求.其实,相对于垂直切分更进一步的是服务化改造,说得简单就是要把原来强耦合的系统拆分成多个弱耦合的服务,通过服务间的

  • jQuery实现获取选中复选框的值实例详解

    应用场景: 我们应该经常见到系统中出现列表,会有一个对列表数据的操作(如删除, 修改,查看等).我们可以在每个列表项后面加一个删除按钮,把列表项的相关参数(如 id)post到后台进行删除.当然如果你每次只要删除一两条数据这样子做并没有什么问题,但是如果你需要一次性删 除100条,你还去一条 条的删除吗?每一次删除10条会不会更好些呢? 好了,接下来.我们就使用jQuery解决这个问题. 首先,假设我们需要删除5个列表项.HTML里面列表标签是ul和ol, 其中ul是无序的,而ol是有序.每一个

  • AngularJS 获取ng-repeat动态生成的ng-model值实例详解

    AngularJS 获取ng-repeat动态生成的ng-model值实例详解

    AngularJS 获取ng-repeat动态生成的ng-model值               最近做项目遇到了ng-model是ng-repeat动态生成的,ng-model="变量",什么变量,是未知的,所以你无法在$scope."变量"取到值,就算取到值也是其中一个值,这样的问题,经过百度一番查找找到解决方案,这里记录下,也行可以帮助到大家. 代码 html <div> <div class="modal-header"

  • java 获取用户的MAC地址多种方法实例详解

    java实现获取用户的MAC地址方法: 方法一:将本机地址与局域网内其他机器区分开来 /** * 根据IP地址获取mac地址 * @param ipAddress 127.0.0.1 * @return * @throws SocketException * @throws UnknownHostException */ public static String getLocalMac(String ipAddress) throws SocketException, UnknownHostEx

  • php获取POST数据的三种方法实例详解

    php获取POST数据的三种方法 方法一,$_POST $_POST或$_REQUEST存放的是PHP以key=>value的形式格式化以后的数据. 方法二,使用file_get_contents("php://input") 对于未指定 Content-Type 的POST数据,则可以使用file_get_contents("php://input");来获取原始数据. 事实上,用PHP接收POST的任何数据均使用本方法.而不用考虑Content-Type,

  • Java多线程 线程组原理及实例详解

    Java多线程 线程组原理及实例详解

    线程组 线程组可以批量管理线程和线程组对象. 一级关联 例子如下,建立一级关联. public class MyThread43 implements Runnable{ public void run() { try { while (!Thread.currentThread().isInterrupted()) { System.out.println("ThreadName = " + Thread.currentThread().getName()); Thread.slee

  • MySQL中几种插入和批量语句实例详解

    目录 前言 1.insert ignore into 2.on duplicate key update 3.replace into 4.insert if not exists 5.批量插入数据 6.批量更新 总结 前言 最常见的方式就是为字段设置主键或唯一索引,当插入重复数据时,抛出错误,程序终止,但这会给后续处理带来麻烦,因此需要对插入语句做特殊处理,尽量避开或忽略异常,下面我简单介绍一下,感兴趣的朋友可以尝试一下: 这里为了方便演示,我新建了一个user测试表,主要有id,userna

  • mysql字段为NULL索引是否会失效实例详解

    mysql字段为NULL索引是否会失效实例详解

    项目场景: 很多博客说mysql在字段中创建普通索引,如果该索引中的数据存在null值是不走索引这个结论是错误的,不过尽量还是设置默认值.(版本8.0低于这个版本可能结果不一致) 1.创建表sc_base_color,其中普通索引为 “name,group_num”,这里暂时不测组合索引,下面再测试. CREATE TABLE `sc_base_color` ( `id` bigint NOT NULL AUTO_INCREMENT, `group_num` bigint DEFAULT NUL

随机推荐