Oracle上的SQL：已经连接了两个表，现在我需要另一个表中的另一列（另一个连接）

Question

我有三张桌子；1.学生-id，name 2.主题-sid，sname 3.结果-id，sid，marks（id和sid是上面两个表引用的外键）

现在，我执行

SELECT s.sname AS SubjectName, MAX(r.marks) AS MaxMarks
FROM subjects s, results r
WHERE s.sid=r.sid
GROUP BY r.sid, s.sname
ORDER BY r.sid

我得到了最高分的科目名称。现在，我还想要获得这些最高分的学生姓名。

所以我尝试添加列 r.id，不起作用。我尝试在此查询中添加表学生。我可能会在添加表格或其他东西后对分组进行恶作剧？

这是我干的

SELECT r.id AS StudentID, s.sname AS SubjectName, MAX(r.marks) AS MaxMarks
FROM subjects s, results r
WHERE s.sid=r.sid
GROUP BY r.sid, s.sname, r.id
ORDER BY r.sid

我得到了每个学生的ID，有重复的科目和分数。而我最想要的是每门课都得最高分的学生。

Answer 1

你可以简单地添加新的联接，我建议你使用显式联接辛塔克斯

SELECT t.name, s.sname AS SubjectName, MAX(r.marks) AS MaxMarks
FROM subjects s
INNER JOIN  results r ON  s.sid=r.sid
INNER JOIN  Students t ON t.id = r.id
GROUP BY r.sid, s.sname, t.name
ORDER BY r.sid

在隐式连接sintax中应该是

  SELECT s.sname AS SubjectName, MAX(r.marks) AS MaxMarks
  FROM subjects s, results r, stundet t
  WHERE s.sid=r.sid
  and t.id = r.id 
  GROUP BY r.sid, s.sname, t.name
  ORDER BY r.sid

Answer 2

您可以使用ROW_NUMBER()来标记每个科目得分最高的学生

SELECT st.name,
       sj.sname,
       r.marks
  FROM (SELECT id,
               sid,
               marks,
               ROW_NUMBER() OVER (PARTITION BY sid 
                                      ORDER BY marks DESC) maxmarks
          FROM results) r
  JOIN students st
    ON r.id = st.id
  JOIN subjects sj
    ON r.sid = sj.sid
 WHERE r.maxmarks = 1