Mysql学生课程表SQL面试集合

 

现有如下2个表，根据要求写出SQL语句。

student表：编号(sid)，姓名(sname)，性别(sex)

course表：编号(sid)，科目(subject)，成绩(score)

 

 问题1：查询出所有科目总成绩最高的学生姓名及总成绩

方式一）：

select s.sid,s.sname,sum(c.score) as z
from course c
left join student s
on c.sid = s.sid
group by c.sid,s.sname
order by z desc 
limit 1

 

方式二）：

SELECT
    sname
FROM
    student
WHERE
    sid IN (
        SELECT
            sid
        FROM
            (
                SELECT
                    sid,
                    sum(score) AS z
                FROM
                    grade
                GROUP BY
                    sid
                ORDER BY
                    z DESC
                LIMIT 1
            ) AS qw
    )
UNION
    (
        SELECT
            sum(score) AS z
        FROM
            grade
        GROUP BY
            sid
        ORDER BY
            z DESC
        LIMIT 1
    )