Example Analysis of Classification, ranking and grouping TOP N in MySQL
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!
