基于 Mysql Case 语句将一张表连接到另一张表

我有一张桌子叫PublicForum4 个不同的用户可以发布到，在这个表上我有 2 个字段称为AuthorId(int) and AuthorType(Enum)显示用户 ID 以及哪个用户撰写了该帖子。

现在我想选择全部PublicForum根据以下条件连接 4 个表中的 1 个AuthorType.

我尝试使用下面的代码通过 Case 语句执行此操作，但其他 3 个表返回 NULL 值，我只想要与 AuthorType 匹配的表。

SELECT PublicForum.*, (SELECT COUNT(*) FROM PublicForumComment WHERE PublicForumComment.ForumId = PublicForum.ForumId) AS CommentCount, 
Student.*, Parent.*, Teacher.*, Counsellor.* FROM PublicForum 
LEFT JOIN Student ON (CASE WHEN PublicForum.AuthorType='Student' AND PublicForum.AuthorId = Student.StudentId THEN 1 ELSE 0 END = 1) 
LEFT JOIN Parent ON (CASE WHEN PublicForum.AuthorType='Parent' AND PublicForum.AuthorId = Parent.ParentId THEN 1 ELSE 0 END = 1) 
LEFT JOIN Teacher ON (CASE WHEN PublicForum.AuthorType='Teacher' AND PublicForum.AuthorId = Teacher.TeacherId THEN 1 ELSE 0 END = 1) 
LEFT JOIN Counsellor ON (CASE WHEN PublicForum.AuthorType='Counsellor' AND PublicForum.AuthorId = Counsellor.CounsellorId THEN 1 ELSE 0 END = 1) 
ORDER BY PublicForum.ForumId DESC LIMIT 10

还有其他方法可以实现这一目标吗？

您不能有条件地加入。执行此操作的方法是使用单独的查询组合起来UNION.

SELECT t1.*, COUNT(pfc.ForumId) AS pfc_count
FROM (
    SELECT p.*, s.StudentName AS Name, s.StudentCol2 AS Col2, s.StudentCol3 AS Col3, s.StudentCol4 AS Col4
    FROM PublicForum AS p
    JOIN Student AS s ON p.AuthorId = s.StudentId
    WHERE p.AuthorType = 'Student'
    UNION
    SELECT p.*, pa.*
    FROM PublicForum AS p
    JOIN Parent AS pa ON p.AuthorId = pa.ParentId
    WHERE p.AuthorType = 'Parent'
    UNION
    SELECT p.*, t.*
    FROM PublicForum AS p
    JOIN Teacher AS t ON p.AuthorId = t.TeacherId
    WHERE p.AuthorType = 'Teacher'
    UNION
    SELECT p.*, c.*
    FROM PublicForum AS p
    JOIN Counsellor AS c ON p.AuthorId = c.CounsellorId
    WHERE p.AuthorType = 'Counsellor') AS t1
LEFT JOIN PublicForumComments AS pfc ON t1.ForumId = pfc.ForumId
GROUP BY t1.ForumId
ORDER BY t1.ForumId DESC
LIMIT 10

指某东西的用途tablename.*因为所有连接的表取决于它们具有相同的列数，并且所有列都是类似的并且顺序相同。这AS然后第一个子查询中的子句为来自UNION.