create database mysql_test;
use mysql_test;
create table student(
s_id int,
s_name varchar(8),
s_birth date,
s_sex varchar(4)
);
insert into mysql_test.student values
(1,'赵雷','1990-01-01','男'),
(2,'钱电','1990-12-21','男'),
(3,'孙风','1990-05-20','男'),
(4,'李云','1990-08-06','男'),
(5,'周梅','1991-12-01','女'),
(6,'吴兰','1992-03-01','女'),
(7,'郑竹','1989-07-01','女'),
(8,'王菊','1990-01-20','女');
create table course (
c_id int,
c_name varchar(8),
t_id int
);
insert into course values
(1,'语文',2),
(2,'数学',1),
(3,'英语',3);
create table teacher (
t_id int,
t_name varchar(8)
);
insert into teacher values
(1,'张三'),
(2,'李四'),
(3,'王五');
create table score (
s_id int,
c_id int,
s_score int
);
insert into score values
(1,3,99),
(1,1,80),
(1,2,90),
(2,1,70),
(2,2,60),
(2,3,65),
(3,1,80),
(3,2,80),
(3,3,80),
(4,1,50),
(4,1,56),
(4,2,30),
(4,3,40),
(5,1,76),
(5,2,87),
(6,1,31),
(6,3,34),
(7,2,89),
(7,3,98);
-- select *
-- from student
-- where sid = (
-- select s_id from score
-- )
-- select student.*,sc1.s_score as score_01,sc2.s_score as score_02
-- from student
-- left join (
-- select * from score where c_id = 1
-- ) sc1
-- on student.s_id = sc1.s_id
-- left join (
-- select * from score where c_id = 2
-- ) sc2
-- on student.s_id = sc2.s_id
-- where sc1.s_score < sc2.s_score;
-- select s.s_id,s.s_name,(sc1.s_score+sc2.s_score+sc3.s_score)/3 as average_score
-- from student s
-- left join (
-- select * from score where c_id=1
-- ) sc1
-- on s.s_id = sc1.s_id
-- left join (
-- select * from score where c_id=2
-- ) sc2
-- on s.s_id = sc2.s_id
-- left join (
-- select * from score where c_id=3
-- ) sc3
-- on s.s_id = sc3.s_id
-- where (sc1.s_score+sc2.s_score+sc3.s_score)/3 >= 60;
-- select s.s_id,s.s_name,round(avg_score, 2) as avg_score
-- from student s
-- inner join (
-- select s_id,avg(s_score) as avg_score
-- from score
-- group by s_id
-- having avg_score >= 60
-- ) t1
-- on s.s_id = t1.s_id;
-- select s.s_id,s.s_name,ifnull(round(avg_score, 2), 0) as avg_score
-- from student s
-- left join (
-- select s_id,avg(s_score) as avg_score
-- from score
-- group by s_id
-- ) t1
-- on s.s_id = t1.s_id
-- where avg_score is null or avg_score < 60;
-- select s.s_id,s.s_name,ifNULL(sum1.sum_score,0),ifNULL(round(sum_class,0),0) as sum_class
-- from student s
-- left join (
-- select s_id,sum(s_score) as sum_score,sum(s_score)/avg(s_score) as sum_class
-- from score
-- group by s_id
-- ) sum1
-- on s.s_id = sum1.s_id;
-- select s.*
-- from student s
-- where s.s_id in (
-- select distinct s_id
-- from score sc
-- inner join (
-- select c_id from score where s_id = 1
-- ) sc1
-- on sc.c_id = sc1.c_id
-- );
select s.*,sco.s_score as score_01
from (
select s_id,s_score
from score
where s_score < 60 and c_id = 1
group by s_id
) sco
left join student s
on sco.s_id = s.s_id
order by score_01 desc;
-- select s*
-- from student s
-- left join (
-- select s_id,s_score
-- from score
-- group by s_id
-- where s_score < 60
-- )