Mysql数据库的查询操作（四）

31、查询任何一门课程成绩在70分以上的姓名、课程名称和分数

```sql
select sname, cname, score from student left join score on score.sid = student.sid
left join course on score.cid = course.cid
where student.sid in (select sid from score group by sid having min(score) > 70);

```

32、查询不及格的课程并按课程号从大到小排列

```sql
select * from score where score<60 order by cid desc;
```

33、查询课程编号为03且课程成绩在80分以上的学生的学号和姓名

```sql
select student.sid,student.sname from student join score on student.sid=score.sid where score.cid=03 and score.score>80;

```

34、查询选了课程的学生人数

```sql
select *,count(sid) from score group by cid;
--简单方法
select count(distinct sid) from score;
```

35、查询选修“王五”老师所授课程的学生中成绩最高的学生姓名及其成绩

```sql
select *,max(score) from course join teacher join student join score on student.sid=score.sid and score.cid=course.cid and course.tid = teacher.tid where tname='王五';

--麻烦方法
select sname, cname, score from
(select *, max(score) over(partition by cid) as max_s from (select student.*, score, course.*, tname from student join score join teacher join course on student.sid = score.sid and score.cid = course.cid and teacher.tid = course.tid where tname = '张三') as t) as t1 where score = max_s;

```

36、查询各个课程及相应的选修人数

```sql
select *,count(sid) from course join score on score.cid=course.cid group by course.cid;
-- 简单方法
select cid, count(sid) from score group by cid;
```

37、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

```sql
select * from score s1 join score s2 where s1.score=s2.score and s1.cid!=s2.cid;

-- 简单方法
select distinct s1.* from score s1 join score s2
on s1.cid != s2.cid and s1.score = s2.score and s1.sid = s2.sid;
```

38、查询每门课程成绩最好的前两名

```sql
select * from (select *,row_number()over(partition by cid) p from score) d where d.p<3;

-- rank方法
select * from  (select *, rank() over(partition by cid order by score desc) as ranking from score) as t where ranking < 3;
```

39、统计每门课程的学生选修人数(超过5人的课程才统计)。要求输出课程号和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序

```sql
select cid,count(sid) from score  group by cid having count(sid)>5 order by cid;
-- 方法二
select cid, count(sid) 选修人数 from score group by cid
having count(sid) > 5
order by 选修人数 desc, cid;
```

40、查询至少选修两门课程的学生学号

```sql
select sid,count(cid) from score group by sid having count(cid)>=2;

-- 方法二：
select student.sid, sname from score join student on score.sid = student.sid
group by sid having count(cid) >= 2;
```

Mysql数据库的查询操作（四）

发表评论