数据准备:
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;