如何执行这个SQL查询?

5

架构:

Student(studentid,name,age)
Course(coursename,dept)
enroll(studentid,coursename,grade)

我需要编写SQL语句,以查找每个年龄组的学生姓名,并且这些学生在历史和政治科学系所选课程中获得了最高成绩。目前我的尝试是:
SELECT 
    name 
FROM 
    student 
GROUP BY age 
HAVING sid IN 
            (
                SELECT 
                    max(grade) 
                FROM 
                    enroll e,enroll e1 
                WHERE 
                    e.studentid = e1.studentid 
                AND e.coursename = (
                                    SELECT coursename FROM course 
                                    WHERE 
                                        dname like '%History%'
                                  ) 
                AND e1.coursename = (
                                    SELECT coursename FROM course 
                                    WHERE 
                                        dname like '%PoliticalScience%'
                                    )
            )

你如何定义年龄组? - vikiiii
我的猜测是年龄组指的是同一年龄段的学生,因此按年龄分组。 - user1675198
2个回答

2

您可以使用子查询来获得最高分数。请尝试以下方法:

SELECT  d.*,
        f.dept,
        e.grade
FROM    student d
        INNER JOIN enroll e
            on d.studentID = e.studentID
        INNER JOIN course f
            ON e.courseName = f.courseName
        INNER JOIN
            (
                SELECT  a.age, c.dept, Max(b.grade) maxGrade
                FROM    student a
                        INNER JOIN enroll b
                            on a.studentID = b.studentID
                        INNER JOIN course c
                            ON b.courseName = c.courseName
                WHERE    c.dept IN ('history','political science')
                GROUP BY a.age, c.dept
            ) topScore
            ON  topscore.age = d.age AND
                topscore.dept = f.dept AND
                topscore.maxGrade = e.grade

0
你可以尝试类似这样的东西。
select 
    s.name 
from 
    student s, enroll e,
    (
        select 
            s.age as age, e.coursename as coursename, max(e.grade) as grade 
        from 
            student s, course c, enroll e
        where 
            s.studentid = e.studentid 
        and c.coursename = e.coursename 
        and (c.dept = 'history' or c.dept = 'political science') 
        group by s.age, e.coursename
    ) t
where 
    s.studentid = e.studentid 
and s.age = t.age 
and e.grade = t.grade 
and e.coursename = t.coursename

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文,
原文链接