MySQL中分类排名和分组TOP N的示例分析
发表于:2024-10-02 作者:千家信息网编辑
千家信息网最后更新 2024年10月02日,这篇文章主要介绍MySQL中分类排名和分组TOP N的示例分析,文中介绍的非常详细,具有一定的参考价值,感兴趣的小伙伴们一定要看完!表结构学生表如下:CREATE TABLE `t_student`
千家信息网最后更新 2024年10月02日MySQL中分类排名和分组TOP N的示例分析
这篇文章主要介绍MySQL中分类排名和分组TOP N的示例分析,文中介绍的非常详细,具有一定的参考价值,感兴趣的小伙伴们一定要看完!
表结构
学生表如下:
CREATE TABLE `t_student` ( `id` int NOT NULL AUTO_INCREMENT, `t_id` int DEFAULT NULL COMMENT '学科id', `score` int DEFAULT NULL COMMENT '分数', PRIMARY KEY (`id`));
数据如下:
题目一:获取每个科目下前五成绩排名(允许并列)
允许并列情况可能存在如4、5名成绩并列情况,会导致取前4名得出5条数据,取前5名也是5条数据。
SELECT s1.* FROM student s1 LEFT JOIN student s2 ON s1.t_id = s2.t_id AND s1.score < s2.score GROUP BY s1.idHAVING COUNT( s2.id ) < 5 ORDER BY s1.t_id, s1.score DESC
ps:取前4名时
分析:
1.自身左外连接,得到所有的左边值小于右边值的集合。以t_id=1时举例,24有5个成绩大于他的(74、64、54、44、34),是第6名,34只有4个成绩大于他的,是第5名......74没有大于他的,是第一名。
SELECT * FROM student s1 LEFT JOIN student s2 ON s1.t_id = s2.t_id AND s1.score < s2.score
2. 把总结的规律转换成SQL表示出来,就是group by 每个student 的 id(s1.id),Having统计这个id下面有多少个比他大的值(s2.id)
SELECT s1.* FROM student s1 LEFT JOIN student s2 ON s1.t_id = s2.t_id AND s1.score < s2.score GROUP BY s1.idHAVING COUNT( s2.id ) < 5
3. 最后根据 t_id 分类,score 倒序排序即可。
题目二:获取每个科目下最后两名学生的成绩平均值
取最后两名成绩
SELECT s1.* FROM student s1 LEFT JOIN student s2 ON s1.t_id = s2.t_id AND s1.score > s2.score GROUP BY s1.id HAVING COUNT( s1.id )< 2 ORDER BY s1.t_id, s1.score
并列存在情况下可能导致筛选出的同一t_id 下结果条数大于2条,但题目要求是取最后两名的平均值,多条平均后还是本身,故不必再对其处理,可以满足题目要求。
分组求平均值:
SELECT t_id,AVG(score)FROM ( SELECT s1.* FROM student s1 LEFT JOIN student s2 ON s1.t_id = s2.t_id AND s1.score > s2.score GROUP BY s1.id HAVING COUNT( s1.id )< 2 ORDER BY s1.t_id, s1.score ) tt GROUP BY t_id
结果:
分析:
1. 查询出所有t1.score>t2.score 的记录
SELECT s1.*,s2.* FROM student s1 LEFT JOIN student s2 ON s1.t_id = s2.t_id AND s1.score > s2.score
2. group by s.id 去重,having 计数取2条
3. group by t_id 分别取各自学科的然后avg取均值
题目三:获取每个科目下前五成绩排名(不允许并列)
SELECT * FROM ( SELECT s1.*, @rownum := @rownum + 1 AS num_tmp, @incrnum := CASE WHEN @rowtotal = s1.score THEN @incrnum WHEN @rowtotal := s1.score THEN @rownum END AS rownum FROM student s1 LEFT JOIN student s2 ON s1.t_id = s2.t_id AND s1.score > s2.score, ( SELECT @rownum := 0, @rowtotal := NULL, @incrnum := 0 ) AS it GROUP BY s1.id ORDER BY s1.t_id, s1.score DESC ) tt GROUP BY t_id, score, rownum HAVING COUNT( rownum )< 5
分析:
1.引入辅助参数
SELECT s1.*, @rownum := @rownum + 1 AS num_tmp, @incrnum :=CASE WHEN @rowtotal = s1.score THEN @incrnum WHEN @rowtotal := s1.score THEN @rownum END AS rownum FROM student s1 LEFT JOIN student s2 ON s1.t_id = s2.t_id AND s1.score > s2.score, ( SELECT @rownum := 0, @rowtotal := NULL, @incrnum := 0 ) AS it
2.去除重复s1.id,分组排序
SELECT s1.*, @rownum := @rownum + 1 AS num_tmp, @incrnum := CASE WHEN @rowtotal = s1.score THEN @incrnum WHEN @rowtotal := s1.score THEN @rownum END AS rownum FROM student s1 LEFT JOIN student s2 ON s1.t_id = s2.t_id AND s1.score > s2.score, ( SELECT @rownum := 0, @rowtotal := NULL, @incrnum := 0 ) AS it GROUP BY s1.id ORDER BY s1.t_id, s1.score DESC
3.GROUP BY t_id, score, rownum 然后 HAVING 取前5条不重复的
以上是"MySQL中分类排名和分组TOP N的示例分析"这篇文章的所有内容,感谢各位的阅读!希望分享的内容对大家有帮助,更多相关知识,欢迎关注行业资讯频道!
成绩
题目
分析
分组
分类
平均值
情况
数据
科目
示例
内容
学科
篇文章
结果
排序
价值
倒序
兴趣
分数
参数
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
汽车网络技术试题答案
邯郸c语言软件开发定做
华为手机登录账号无法连接服务器
电信5G服务器是APN什么
尚朋科技互联网公司排名
网络安全事件发生时的风险增
抚州高性价比服务器哪里比较好
网络安全和计算机科学与技术
批处理 代理服务器
做网络技术员学徒有前途吗
关于初一网络安全手抄报
收购服务器管理平台
软件开发培训河南
网络安全通俗心得体会500字
记箅机网络技术就业方向
湖南工业大学网络安全宣传
网络安全更重要一辩稿
服务器上出现广告
csgo不能进安全服务器
合同公示网络安全
高科天逸互联网科技有限公司
工业互联网科技背景素材
软件开发要那把书好
下拉框读取数据库的值
新华社 数据库
税务局加强网络安全建设
手机软件开发工资多少
南京java软件开发平台
软件开发编写小程序
战地一兵种违反服务器规定