mysql分组聚合-＞连表联查-＞求topn

朝夕而望，眼落余阳

于 2021-08-10 16:21:52 发布

阅读量165

点赞数 2

文章标签： mysql

本文链接：https://blog.csdn.net/kgsunshine/article/details/119571936

版权

这篇博客介绍了如何使用SQL进行数据处理，包括对学生表和分数表的分组聚合，以及如何通过LEFT JOIN连接两个表。接着，展示了如何利用子查询和分组来获取每个性别组别中的分数前三名学生。这个过程涉及到了SQL的聚合函数SUM，LEFT JOIN，以及嵌套查询用于实现分组TOPN的操作。

摘要由CSDN通过智能技术生成

首先建立两个表如下：

CREATE TABLE `student` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `age` INT(11) DEFAULT NULL,
  `sex` ENUM('0','1') DEFAULT NULL,
  `time` TIMESTAMP NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8;

INSERT INTO student(NAME,age,sex,`time`)VALUES("李四",19,"1",'2021-08-10-15-03-00');
INSERT INTO student(NAME,age,sex)VALUES("王五",20,"1");
INSERT INTO student(NAME,age,sex)VALUES("赵六",21,"1");
INSERT INTO student(NAME,age,sex)VALUES("钱琪",22,"1");
INSERT INTO student(NAME,age,sex)VALUES("杨幂",18,"0");
INSERT INTO student(NAME,age,sex)VALUES("丽丽",19,"0");
INSERT INTO student(NAME,age,sex)VALUES("莎莎",20,"0");
INSERT INTO student(NAME,age,sex)VALUES("慧慧",22,"0");
INSERT INTO student(NAME,age,sex)VALUES("翠翠",23,"0");

CREATE TABLE `score` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `subjectName` VARCHAR(255) NOT NULL,
  `score` INT(11) NOT NULL,
  `studentId` INT(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8;
INSERT INTO score(subjectName,score,studentId)VALUES("语文",80,1001);
INSERT INTO score(subjectName,score,studentId)VALUES("数学",89,1001);
INSERT INTO score(subjectName,score,studentId)VALUES("英语",73,1001);
INSERT INTO score(subjectName,score,studentId)VALUES("语文",81,1002);
INSERT INTO score(subjectName,score,studentId)VALUES("数学",99,1002);
INSERT INTO score(subjectName,score,studentId)VALUES("英语",94,1002);
INSERT INTO score(subjectName,score,studentId)VALUES("语文",65,1003);
INSERT INTO score(subjectName,score,studentId)VALUES("数学",45,1003);
INSERT INTO score(subjectName,score,studentId)VALUES("英语",12,1003);
INSERT INTO score(subjectName,score,studentId)VALUES("语文",33,1004);
INSERT INTO score(subjectName,score,studentId)VALUES("数学",68,1004);
INSERT INTO score(subjectName,score,studentId)VALUES("英语",59,1004);
INSERT INTO score(subjectName,score,studentId)VALUES("语文",19,1005);
INSERT INTO score(subjectName,score,studentId)VALUES("数学",100,1005);
INSERT INTO score(subjectName,score,studentId)VALUES("英语",80,1005);
INSERT INTO score(subjectName,score,studentId)VALUES("语文",85,1006);
INSERT INTO score(subjectName,score,studentId)VALUES("数学",88,1006);
INSERT INTO score(subjectName,score,studentId)VALUES("英语",73,1006);
INSERT INTO score(subjectName,score,studentId)VALUES("语文",88,1007);
INSERT INTO score(subjectName,score,studentId)VALUES("数学",45,1007);
INSERT INTO score(subjectName,score,studentId)VALUES("英语",89,1007);
INSERT INTO score(subjectName,score,studentId)VALUES("语文",51,1008);
INSERT INTO score(subjectName,score,studentId)VALUES("数学",42,1008);
INSERT INTO score(subjectName,score,studentId)VALUES("英语",43,1008);
INSERT INTO score(subjectName,score,studentId)VALUES("语文",90,1009);
INSERT INTO score(subjectName,score,studentId)VALUES("数学",56,1009);
INSERT INTO score(subjectName,score,studentId)VALUES("英语",69,1009);
INSERT INTO score(subjectName,score,studentId)VALUES("语文",99,1010);
INSERT INTO score(subjectName,score,studentId)VALUES("数学",63,1010);
INSERT INTO score(subjectName,score,studentId)VALUES("英语",88,1010);

在这里插入图片描述

很明显我们的需求，首先要进行数据的处理，比如这两个表中，需要处理的数据就是分数的求和。很明显要用到分组聚合。

SELECT studentId,SUM(score) sc
FROM score
GROUP BY studentId;

在这里插入图片描述
接下来就是链接两个表（student表和我们刚刚得到的表），上面我们保留了score表中的两个属性 id和sc。所以在链接两个表的时候我们要用id作为链接的属性，左边保留student表里的所有数据，而链接的右边的表我们只需要分数这一栏的数据。所以就有：将上面所打的代码就当作一张表我们用括号将其括弧起来，并且起一个名字：s。利用left join进行链接。ON后面跟链接的判断条件。

SELECT student.*,s.sc FROM student
LEFT JOIN
(
SELECT studentId,SUM(score) AS sc
FROM score
GROUP BY studentId
) AS s
ON student.`id`=s.studentId;
![在这里插入图片描述](https://img-blog.csdnimg.cn/f92ccab395c14fd5b9084c39d4e5a15f.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2tnc3Vuc2hpbmU=,size_16,color_FFFFFF,t_70)

在这里插入图片描述接下来也就是比较麻烦的一件事，进行分组求topN
首先正常的求topN的格式如下：

SELECT * FROM student 
AS p1
WHERE 3>  -- 这里我们求分数的前三，并且是男女进行分组
(
	SELECT COUNT(*) FROM student 
	AS p2
	WHERE 	p1.`sex` = p2.`sex`
	AND
		p1.sc > p2.sc   -- sc代表分数，因为我们所求的就是分数的top3
);

然后我们将我们之前所链接表锁敲打的代码，当作student去进行替换。这样分组topN就求出来了。

SELECT * FROM 
(
	SELECT student.*,s.sc FROM student
	LEFT JOIN
	(
		SELECT studentId,SUM(score) AS sc
		FROM score
		GROUP BY studentId
	) AS s
	ON student.`id`=s.studentId
) 
AS p1
WHERE 3>  -- 这里我们求分数的前三，并且是男女进行分组
(
	SELECT COUNT(*) FROM 
	(
		SELECT student.*,s.sc FROM student
		LEFT JOIN
		(	
			SELECT studentId,SUM(score) AS sc
			FROM score
			GROUP BY studentId
		) AS s
		ON student.`id`=s.studentId
	)
	AS p2
	WHERE 	p1.`sex` = p2.`sex`
	AND
		p1.sc > p2.sc   -- sc代表分数，因为我们所求的就是分数的top3
);