Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Example Analysis of Classification, ranking and grouping TOP N in MySQL

2025-03-31 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Development >

Share

Shulou(Shulou.com)06/01 Report--

This article mainly introduces the sample analysis of classification, ranking and grouping TOP N in MySQL, which is very detailed and has certain reference value. Interested friends must finish reading it!

Table structure

The student table is as follows:

CREATE TABLE `t_ student` (`id` int NOT NULL AUTO_INCREMENT, `tid`int DEFAULT NULL COMMENT 'discipline id', `score`int DEFAULT NULL COMMENT' score', PRIMARY KEY (`id`))

The data are as follows:

Topic 1: get the top five scores in each subject (juxtaposition allowed)

The situation of allowing juxtaposition may exist, such as the juxtaposition of 4 or 5 scores, which will lead to five pieces of data from the top 4, and the top 5 is also 5 pieces of data.

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条,但题目要求是取最后两名的平均值,多条平均后还是本身,故不必再对其处理,可以满足题目要求。

Average by grouping:

SELECT s2.score GROUP BY s1.id HAVING COUNT (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 's records.

SELECT s1.Journal s2.* FROM student S1 LEFT JOIN student S2 ON s1.t_id = s2.t_id AND s1.score > s2.score

2. Remove the weight by group by s.id and take 2 items by having count.

3. Group by t_id takes the respective disciplines, and then avg takes the average.

Topic 3: get the top five scores in each subject (juxtaposition is not allowed) SELECT * FROM (SELECT s1.ranking, @ 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

two。 Remove duplicate s1.id and sort in groups

SELECT s1.AS num_tmp, @ 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 and then HAVING take the first 5 items that are not repeated

The above is all the contents of the article "sample Analysis of classified ranking and grouping TOP N in MySQL". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Development

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report