mysql经典题目： 科目成绩都大于80分\每个科目的第一名\总成绩排名

2019独角兽企业重金招聘Python工程师标准>>>

数据准备：

drop table if EXISTS tmp_1;
create table tmp_1
(`Id` INT NOT NULL AUTO_INCREMENT,
name varchar(10),
subject varchar(10),
score int,
PRIMARY KEY(`Id`));

insert into tmp_1 (name,subject,score) values ('李云龙','语文',79);
insert into tmp_1 (name,subject,score) values ('李云龙','数学',81);
insert into tmp_1 (name,subject,score) values ('楚云飞','语文',81);
insert into tmp_1 (name,subject,score) values ('楚云飞','数学',89);
insert into tmp_1 (name,subject,score) values ('张大彪','语文',79);
insert into tmp_1 (name,subject,score) values ('张大彪','数学',90);

1、查询出科目成绩都大于80分的学生的名字？

select name
from tmp_1
group by name
having sum(score > 80) > 1;

或者

select a.name
from tmp_1 a
left join tmp_1 b on b.name = a.name
where a.score > 80 and b.score > 80 and a.subject != b.subject
group by a.name;

2、查询出每个科目的第一名的学生的学科、姓名、分数 [依次是数学、语文]？

SELECT
	a. SUBJECT,
	NAME,
	max_score
FROM
	tmp_1 a
LEFT JOIN (
	SELECT
		SUBJECT,
		max(score) max_score
	FROM
		tmp_1
	GROUP BY
		SUBJECT
) b ON a.score = b.max_score
WHERE
	a. SUBJECT = b.`subject`
ORDER BY
	CASE
WHEN a. SUBJECT = '数学' THEN
	1
WHEN a. SUBJECT = '语文' THEN
	2
ELSE
	3
END;

3、查询出总成绩排名，字段为姓名和分数，降序展示？

select name,sum(score)
from tmp_1
group by NAME
order by sum(score) desc;

 

转载于:https://my.oschina.net/grittan/blog/3026056