用 MAX 函数连接三个表 [英] Join three tables with MAX function

问题描述

我有三个表:student、subject 和 score.
我想显示每个学生的 max(subject_id) 的详细信息.

I have three tables: student, subject and score.
I want to display the details of max(subject_id) of each student.

学生表

student_id   student_name  exam_date
   1            Sharukh     24/06/12
   2            Amir        23/06/12

主题表

subject_id    sub_name
   200         Maths
   300         English
   400         Science

评分表

student_id   subject_id     score
    1           200         50 
    1           300         20
    2           300         10

结果应该是:

student_id    student_name     subject_id      score
     1          Sharukh           300            20
     2          Amir              300            10

推荐答案

使用 MAX 函数和 GROUP BY 其他选择.

Use the MAX function and GROUP BY your other selections.

SELECT st.student_id, st.student_name, MAX(su.subject_id) AS subject_id, sc.score
FROM student st
INNER JOIN score sc ON st.student_id = sc.student_id
INNER JOIN subject su ON sc.subject_id = su.subject_id
WHERE su.subject_id = (SELECT MAX(sca.subject_id) 
                       FROM score sca 
                       WHERE sc.student_id = sca.student_id 
                       GROUP BY sca.student_id)
GROUP BY st.student_id, st.student_name, sc.score

输出:

student_id  student_name  subject_id  score
1           Sharukh       300         20
2           Amir          300         10

SQL 小提琴:http://sqlfiddle.com/#!9/71c46a/7/0

没有GROUP BY

SELECT st.student_id, st.student_name, su.subject_id, sc.score
FROM student st
INNER JOIN score sc ON st.student_id = sc.student_id
INNER JOIN subject su ON sc.subject_id = su.subject_id
WHERE su.subject_id = (SELECT MAX(sca.subject_id) 
                       FROM score sca 
                       WHERE sc.student_id = sca.student_id 
                       GROUP BY sca.student_id)

这篇关于用 MAX 函数连接三个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！