使用交叉连接连接三个表

puruo6ea  于 2021-06-20  发布在  Mysql
关注(0)|答案(1)|浏览(440)

我有三张table:

student:        sub:           marks:

id | name     id | sub         stud_id | sub_id | marks
---+--------  ---+------------  --------+----------+------
 1 | Peter     1 | English            1 |        1 |    80
 2 | Alice     2 | Maths              1 |        2 |    70
               3 | History            2 |        1 |    90
                                      2 |        2 |    80

我需要做一个查询,返回以下内容:

name  | subjects  | marks
-------+-----------+----------------------------
  Peter | English  | 80
  Peter | Maths    | 70
  Peter | History  | 0

这就是我所尝试的:

select * 
from student s 
LEFT JOIN stud_marks m ON s.id = m.stud_id 
RIGHT JOIN subjects sub ON sub.id = m.sub_id

这让我

name  | marks  | sub
------+--------+----------
Peter | 80     | English  
Peter | 70     | Maths    
Alice | 90     | English  
Alice | 80     | Maths    
      |        | History

有什么想法吗?

hmae6n7t

hmae6n7t1#

你会把彼得和所有的替补都交出来。然后连接标记:

select
  peter.name,
  sub.sub,
  coalesce(marks.marks, 0) as marks
from (select * from student where id = 1) peter
cross join sub
left join marks on marks.stud_id = peter.id and marks.sub_id = sub.id
order by sub.sub;

(实际上不需要在子查询中选择peter,但我认为它非常清楚我们在这里做什么。我们也可以交叉加入有分数的学生,把这个限制在where子句中的peter。)

相关问题