博客

sql解决：查询每个学生的总成绩的各个科目的分数和姓名，按总成绩排序

SELECT aa.* FROM (SELECT aa.name ,GROUP_CONCAT(数学),GROUP_CONCAT(语文),GROUP_CONCAT(英语) FROM (SELECT a.name,CASE 
WHEN a. SUBJECT = '数学' THEN
	a.score 
	END 数学,
	CASE
WHEN a. SUBJECT = '语文' THEN
	a.score 
	END 语文,
	CASE
WHEN a. SUBJECT = '英语' THEN
	a.score 
END 英语
FROM tmp_1 a ) aa GROUP BY aa.name ) aa INNER JOIN 
(SELECT NAME,SUM(score)
FROM tmp_1
GROUP BY NAME
ORDER BY SUM(score) DESC) bb ON aa.name=bb.name

![在这里插入图片描述](https://img-blog.csdnimg.cn/23f1d97905d845b28ef5393b72134b83.png)
create table `tmp_1` (
	`Id` int ,
	`name` varchar ,
	`subject` varchar ,
	`score` varchar 
); 
insert into `tmp_1` (`Id`, `name`, `subject`, `score`) values('1','李云龙','语文','79');
insert into `tmp_1` (`Id`, `name`, `subject`, `score`) values('2','李云龙','数学','81');
insert into `tmp_1` (`Id`, `name`, `subject`, `score`) values('3','楚云飞','语文','81');
insert into `tmp_1` (`Id`, `name`, `subject`, `score`) values('4','楚云飞','数学','89');
insert into `tmp_1` (`Id`, `name`, `subject`, `score`) values('5','张大彪','语文','79');
insert into `tmp_1` (`Id`, `name`, `subject`, `score`) values('6','张大彪','数学','90');
insert into `tmp_1` (`Id`, `name`, `subject`, `score`) values('7','郭康','英语','10');
insert into `tmp_1` (`Id`, `name`, `subject`, `score`) values('8','大帅','英语','16');
insert into `tmp_1` (`Id`, `name`, `subject`, `score`) values('9','韩凯','英语','19');
insert into `tmp_1` (`Id`, `name`, `subject`, `score`) values('10','小花','英语','19');