mysql - 使用 like 语句连接三个表

Question

我有三个表“学生”、“科目”和“分数”。学生

Student
id,name,address
1,tom,London
2,sam,USA
3,cal,Fort

subject
id,subjectname
1,maths
2,Science

marks
studid,subid,marks
1,1,30
1,2,40
2,1,60
2,2,70

需要对学生和科目进行搜索查询所以我对学生使用类似的查询 LIKE CONCAT('%', "cal", '%')

    SELECT student.name,
        subject.name,
       marks.marks
  FROM marks
  RIGHT JOIN student ON student.name LIKE CONCAT('%', "cal", '%') AND student.id = marks.stuid 
 RIGHT JOIN subject ON subject.id = marks.subjectid

在搜索学生时需要这样的输出

Studentname subject Marks
cal,maths,0
cal,science,0

Studentname subject Marks
tom,maths,30
tom,science,40

Studentname subject Marks
sam,maths,60
sam,science,70

我还需要所有科目和所有学生，如果没有传递任何值，则应该看到所有值

Studentname subject Marks
tom,maths,30
tom,science,40    
sam,maths,60
sam,science,70
cal,maths,0
cal,science,0

Answer 1

您需要所有科目。因此，从这里开始并使用 left join :

SELECT s.name, su.name, coalesce(m.marks, 0)
FROM subjects su LEFT JOIN
     marks m
     ON m.subjectid = su.id LEFT JOIN
     student s
     ON s.name LIKE CONCAT('%', 'cal', '%') AND
        s.id = m.stuid ;

如果您需要所有学生和科目，请从 cross join 开始:

SELECT s.name, su.name, coalesce(m.marks, 0)
FROM subjects su CROSS JOIN
     students s LEFT JOIN
     marks m
     ON m.subjectid = su.id AND s.id = m.stuid ;