|
|
-- 19．使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计各分数段人数：课程ID和课程名称
SELECT
COURSE.CD,
COURSE.CNAME,
SUM( CASE WHEN SCORE BETWEEN 85 AND 100 THEN 1 ELSE 0 END ) AS &#39;85-100&#39;,
SUM( CASE WHEN SCORE BETWEEN 75 AND 80 THEN 1 ELSE 0 END ) AS &#39;70-85&#39;,
SUM( CASE WHEN SCORE BETWEEN 60 AND 70 THEN 1 ELSE 0 END ) AS &#39;60-70&#39;,
SUM( CASE WHEN SCORE < 60 THEN 1 ELSE 0 END ) AS &#39;<60&#39;
FROM
sc
INNER JOIN course ON course.CD = sc.CD
GROUP BY
sc.CD
-- 20．查询学生平均成绩及其名次（学号，平均成绩，名次）
SELECT
SD,
AVG( SCORE ) AS 平均成绩
FROM
SC
GROUP BY
SD
ORDER BY
AVG( SCORE ) DESC;-- GPT
-- 可得出正确的结果
SELECT
s.SD,
AVG( sc.SCORE ) AS AVG_SCORE,
(
SELECT
COUNT(*) + 1
FROM
( SELECT AVG( SCORE ) AS AVG_SCORE FROM SC GROUP BY SD ) AS t
WHERE
t.AVG_SCORE > AVG( sc.SCORE )
) AS RANK
FROM
STUDENT s
INNER JOIN SC sc ON s.SD = sc.SD
GROUP BY
s.SD
ORDER BY
AVG_SCORE DESC;
-- MySQL8,可使用窗口函数
SELECT
SD,
AVG( SCORE ) AS 平均成绩,
RANK() OVER ( ORDER BY AVG( SCORE ) DESC ) AS 名次
FROM
SC
GROUP BY
SD
-- 21．查询各科成绩前三名的记录。要求：展示字段为课程编号、课程名、前三名的成绩，前三名的成绩放在一个字段展示
SELECT
C.CD,
C.CNAME,
CONCAT( GROUP_CONCAT( S.SCORE ORDER BY S.SCORE DESC SEPARATOR &#39;,&#39; ), &#39;,&#39; ) AS TOP_3_SCORES
FROM
COURSE C
INNER JOIN SC S ON C.CD = S.CD
WHERE
( SELECT COUNT( DISTINCT SCORE ) FROM SC WHERE CD = C.CD AND SCORE >= S.SCORE ) <= 3
GROUP BY
C.CD,
C.CNAME;
-- 22．查询每门课程被选修的学生数(课程号，选修人数)
SELECT
CD,
COUNT( SD ) AS 选修人数
FROM
SC
GROUP BY
CD;
-- 23．查询选修了17门课程的学生（学号和姓名）
SELECT
SD FROM SC
GROUP BY
SD
HAVING
COUNT( CD ) = 17;
-- 24．查询同名同性别学生名单并统计同名人数。展示字段：学生姓名，同名人数。
SELECT
SNAME,
COUNT( SNAME ) AS 同名人数
FROM
STUDENT
GROUP BY
SNAME,
SSEX
HAVING
COUNT( SNAME )> 1;
-- 25．查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时按课程号降序排列。课程号，平均成绩。
SELECT
CD,
AVG( SCORE ) 平均成绩
FROM
SC
GROUP BY
CD
ORDER BY
AVG( SCORE ) ASC,
CD DESC;
-- 26．查询平均成绩大于85的所有学生的学号、姓名和平均成绩
SELECT
STUDENT.SD,
STUDENT.SNAME,
AVG( SC.SCORE ) AS 平均成绩
FROM
SC
INNER JOIN STUDENT ON STUDENT.SD = SC.SD
GROUP BY
SC.SD
HAVING
平均成绩 > 85;
-- 27．查询课程名称为“数据库原理”且分数低于70的学生姓名和分数
SELECT
student.SD,
sc.SCORE
FROM
student
INNER JOIN sc ON sc.SD = student.SD
INNER JOIN course ON course.CD = sc.CD
WHERE
course.CNAME = &#39;数据库原理&#39;
AND sc.SCORE < 70;
-- 28．查询课程编号为1216且课程成绩在80分以上的学生的学号和姓名
SELECT
student.SD,
student.SNAME
FROM
sc
INNER JOIN student ON sc.SD = student.SD
WHERE
sc.CD = &#39;1216&#39;
AND sc.SCORE > 80;
-- 29．查询任何一门课程成绩在90分以上的姓名、课程名称和分数 尽量用INNER JOIN连接表
SELECT
student.SNAME,
course.CNAME,
sc.SCORE
FROM
student,
course,
sc
WHERE
student.SD = sc.SD
AND sc.CD = course.CD
AND sc.SCORE > 90;
-- 30．查询选修“朱芳”老师所授课程的学生中成绩最高的学生姓名及其成绩
SELECT
student.SNAME,
sc.SCORE
FROM
student
INNER JOIN sc ON sc.SD = student.SD
INNER JOIN course ON course.CD = sc.CD
INNER JOIN teacher ON teacher.TD = course.TD
WHERE
teacher.TNAME = &#39;朱芳&#39;
LIMIT 1;
-- 31．查询所有学生的选课情况.学号，选修课数。
SELECT
SD,
COUNT( CD )
FROM
sc
GROUP BY
SD
-- 32．查询各门课程及相应的选修人数.
SELECT
CD,
COUNT( SD )
FROM
sc
GROUP BY
CD
-- 33．查询每门课程的学生选修人数，超过110人的课程才统计。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同按课程号升序排列
SELECT
CD,
COUNT( SD ) AS 选修人数
FROM
sc
GROUP BY
CD
HAVING
选修人数 >= 110
ORDER BY
选修人数 DESC,
CD ASC;
-- 34．查询所有学生都选修的课程的课程号和课程名
SELECT
COURSE.CD,
COURSE.CNAME
FROM
COURSE
WHERE
COURSE.CD IN (
SELECT
sc.CD
FROM
sc
LEFT JOIN student ON sc.SD = student.SD
GROUP BY
sc.CD
HAVING
COUNT( student.SD ) =(
SELECT
COUNT(*)
FROM student ))
-- 35．查询两门及以上不及格课程的同学的学号及其平均成绩
SELECT
SC.SD,
AVG( SC.SCORE ) AS 平均成绩
FROM
SC
WHERE
SC.SD IN ( SELECT SC.SD FROM SC WHERE SC.SCORE < 60 GROUP BY SC.SD HAVING COUNT(*) >= 2 )
GROUP BY
SC.SD;
-- 36．查询每门课程成绩最好的前两名.课程号，学生姓名
SELECT
SC1.CD,
STUDENT.SNAME
FROM
( SELECT CD, MAX( SCORE ) AS MAX_SCORE FROM SC GROUP BY CD ) SC1
INNER JOIN ( SELECT CD, SD, SCORE FROM SC ) SC2 ON SC1.CD = SC2.CD
AND SC1.MAX_SCORE = SC2.SCORE
INNER JOIN STUDENT ON SC2.SD = STUDENT.SD
GROUP BY
SC1.CD,
STUDENT.SNAME
HAVING
COUNT(*) <= 2;答案仅供参考，SQL写法多样，有不同写法欢迎交流与指正！ |
|
发表于 2023-4-19 19:21:13