SQL | 一道sql笔试题11.14

最新推荐文章于 2024-07-09 15:20:37 发布

月公子

最新推荐文章于 2024-07-09 15:20:37 发布

阅读量118

点赞数

分类专栏： SQL 文章标签： SQL 内连接成绩对比分数段统计学生信息

本文链接：https://blog.csdn.net/weixin_44878159/article/details/109693704

版权

SQL 专栏收录该内容

19 篇文章 0 订阅

订阅专栏

现有几个表：
student学生：
在这里插入图片描述

course课程：
在这里插入图片描述

sc成绩表:
在这里插入图片描述

teacher老师表：
在这里插入图片描述
题目：

找出01课程比02课程成绩高的学生的信息。

#（1）首先，找出01课程比02课程成绩高的学生的id
##内连接的两种等价写法：inner join...on  和  , ... where
SELECT a.Sid,a.score 01_score,b.score 02_score FROM (SELECT * FROM sc WHERE Cid = '01') a INNER JOIN (SELECT * FROM sc WHERE Cid = '02') b
ON a.Sid = b.Sid AND a.score > b.score; 

SELECT a.Sid,a.score 01_score,b.score 02_score FROM (SELECT * FROM sc WHERE Cid = '01') a, (SELECT * FROM sc WHERE Cid = '02') b
WHERE a.Sid = b.Sid AND a.score > b.score; 

#SELECT a.Sid,a.score 01_score,b.score 02_score FROM (SELECT * FROM sc WHERE Cid = '01') a left join (SELECT * FROM sc WHERE Cid = '02') b
#ON a.Sid = b.Sid AND a.score > b.score; 
#select * from sc where Cid = '01';
#SELECT * FROM sc WHERE Cid = '02';
#select * from student;

#（2）然后，将这些学生的id与学生表的具体信息连接。
##当大圆套小圆时，内连接和左右外连接等价
SELECT * FROM student p,(SELECT a.Sid,a.score 01_score,b.score 02_score FROM (SELECT * FROM sc WHERE Cid = '01') a, (SELECT * FROM sc WHERE Cid = '02') b
WHERE a.Sid = b.Sid AND a.score > b.score) q
WHERE p.Sid = q.Sid;

SELECT * FROM student p RIGHT JOIN (SELECT a.Sid,a.score 01_score,b.score 02_score FROM (SELECT * FROM sc WHERE Cid = '01') a, (SELECT * FROM sc WHERE Cid = '02') b
WHERE a.Sid = b.Sid AND a.score > b.score) q
ON p.Sid = q.Sid;

（1）的结果
（2）的结果

将每门课程按照不同成绩段，统计人数占比百分比。

#向量计算
SELECT c.cid,c.cname,
((SELECT COUNT(1) FROM sc WHERE sc.Cid = c.Cid AND sc.score<=100 AND sc.score>80)/(SELECT COUNT(1) FROM sc WHERE sc.Cid = c.Cid)) '80-100',
((SELECT COUNT(1) FROM sc WHERE sc.Cid = c.Cid AND sc.score<=80 AND sc.score>70)/(SELECT COUNT(1) FROM sc WHERE sc.Cid = c.Cid)) '70-80',
((SELECT COUNT(1) FROM sc WHERE sc.Cid = c.Cid AND sc.score<=70 AND sc.score>60)/(SELECT COUNT(1) FROM sc WHERE sc.Cid = c.Cid)) '60-70',
((SELECT COUNT(1) FROM sc WHERE sc.Cid = c.Cid AND sc.score<=60 AND sc.score>50)/(SELECT COUNT(1) FROM sc WHERE sc.Cid = c.Cid)) '50-60'
FROM course c ORDER BY c.Cid;