complex-sql

-- 表结构
-- * 学生表student(id,name)
-- * 课程表course(id,name)
-- * 学生课程表student_course(sid,cid,score)

create table student(
id int unsigned primary key auto_increment,
name char(10) not null
);
insert into student(name) values('张三'),('李四');

create table course(
id int unsigned primary key auto_increment,
name char(20) not null
);
insert into course(name) values('语文'),('数学');

create table student_course(
sid int unsigned,
cid int unsigned,
score int unsigned not null,
foreign key (sid) references student(id),
foreign key (cid) references course(id),
primary key(sid, cid)
);
insert into student_course values(1,1,80),(1,2,90),(2,1,90),(2,2,70);

-- 查询student表中重名的学生，结果包含id和name，按name,id升序
select id,name
from student
where name in (
select name from student group by name having(count(*) > 1)
) order by name;

-- 在student_course表中查询平均分不及格的学生，列出学生id和平均分
select sid,avg(score) as avg_score
from student_course
group by sid having(avg_score<60);

-- 在student_course表中查询每门课成绩都不低于80的学生id
select distinct sid
from student_course
where sid not in (
select sid from student_course
where score < 80);

-- 查询每个学生的总成绩，结果列出学生姓名和总成绩 如果使用下面的sql会过滤掉没有成绩的人
select name,sum(score) total
from student,student_course
where student.id=student_course.sid
group by sid;

-- 使用左外连接
select name,sum(score)
from student left join student_course
on student.id=student_course.sid
group by sid;

-- 总成绩最高的学生，结果列出学生id和总成绩 下面的sql效率很低，因为要重复计算所有的总成绩。
select sid,sum(score) as sum_score
from student_course group by sid
order by sum_score desc limit 1;

-- 在student_course表查询课程1成绩第2高的学生，如果第2高的不止一个则列出所有的学生
select score from student_course where cid = 1 group by score order by score desc limit 1,1;
select * from student_course
where cid=1 and score = (
select score from student_course where cid = 1 group by score order by score desc limit 1,1
);

-- 在student_course表查询各科成绩最高的学生，结果列出学生id、课程id和对应的成绩 你可能会这样写：
select * from student_course as x where score>=
		(select max(score) from student_course as y where cid=x.cid);

-- 在student_course表中查询每门课的前2名，结果按课程id升序，同一课程按成绩降序 这个问题也就是取每组的前N条纪录
select * from student_course x where
	2>(select count(*) from student_course y where y.cid=x.cid and y.score>x.score)
		order by cid,score desc;

-- 一个叫team的表，里面只有一个字段name,一共有4条纪录，分别是a,b,c,d,对应四个球队，两两进行比赛，用一条sql语句显示所有可能的比赛组合
select a.name, b.name
	from team a, team b
		where a.name < b.name