编辑代码


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
-- )