常见sql问题总结三

-- 查询各科成绩前三名的记录:(不考虑成绩并列情况) 
-- ROW_NUMBER()函数 方便统计排序。
-- ROW_NUMBER() OVER(PARTITION BY SC.corse_id ORDER BY SC.number DESC) RN   根据corse_id分组在进行倒叙排序
SELECT * FROM (
              SELECT SC.student_id SNO,
              SC.corse_id CNO,
              SC.number SCORE,
              ROW_NUMBER() OVER(PARTITION BY SC.corse_id ORDER BY SC.number DESC) RN
                            
              FROM score SC) A
WHERE A.RN <= 3   -- 取 rn的前三个 （前三名）

-- 查询每门课程被选修的学生数；
SELECT COUNT(DISTINCT student_id) as 人数,corse_id FROM score GROUP BY score.corse_id

-- 查询出只选修了一门课程的全部学生的学号和姓名
select bb.选课数量 数量,aaa.sid,aaa.sname FROM
(SELECT * FROM student) aaa
INNER JOIN
(SELECT COUNT(DISTINCT corse_id) as 选课数量,student_id FROM score GROUP BY score.student_id HAVING COUNT(DISTINCT corse_id)=1) bb
on aaa.sid=bb.student_id;

-- 查询男生、女生的人数；
SELECT gender, COUNT(sid) as 人数 FROM student GROUP BY gender;

-- 查询姓“张”的学生名单；
SELECT * FROM student WHERE sname LIKE '张%';

-- 查询同名同姓学生名单，并统计同名人数；
SELECT sname, COUNT(sid) as 人数 FROM student GROUP BY sname HAVING 人数>1;
    
-- 查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列

SELECT AVG(number)AS avg_num ,corse_id FROM score GROUP BY corse_id ORDER BY avg_num  ,corse_id DESC;

-- 查询平均成绩大于85的所有学生的学号、姓名和平均成绩；
SELECT sid,avg_num,sname FROM
(SELECT sid,sname FROM student) aa
INNER JOIN
(SELECT AVG(number)AS avg_num ,student_id FROM score  GROUP BY student_id HAVING avg_num>85) bb
ON aa.sid=bb.student_id

-- 查询课程名称为“数学”，且分数低于60的学生姓名和分数；
SELECT student.sid,sname,score.number,course.cname FROM student
INNER JOIN
score
on 
student.sid=score.student_id 
INNER JOIN
course
on 
score.corse_id=course.cid
WHERE course.cname='生物' AND score.number<60


-- 查询课程编号为003且课程成绩在80分以上的学生的学号和姓名； 
SELECT student.sid,sname,score.number,course.cname FROM student
INNER JOIN
score
on 
student.sid=score.student_id 
INNER JOIN
course
on 
score.corse_id=course.cid
WHERE course.cid='3' AND score.number>80

-- 求选了课程的学生人数
SELECT COUNT(DISTINCT student_id)FROM score 

-- 查询选修“王老师”老师所授课程的学生中，成绩最高的学生姓名及其成绩；
SELECT MAX(score.number),student.sname FROM score 
LEFT JOIN
course
on score.corse_id=course.cid
LEFT JOIN
teacher
ON course.tearch_id=teacher.tid
LEFT JOIN
student
ON score.student_id=student.sid
WHERE teacher.tname='王老师'
GROUP BY student.sid
ORDER BY MAX(score.number) desc 
LIMIT 1