根据Mysql Case语句将一个表连接到另一个表 [英] Join one table to another based on Mysql Case Statement

问题描述

我有一个名为PublicForum的表，可以向4个不同的用户发布该表，该表上有2个名为AuthorId(int)和AuthorType(Enum)的字段，它们显示用户ID以及哪个用户写了该帖子.

I have a table called PublicForum that 4 different users can post to, on this table I have 2 fields called AuthorId(int) and AuthorType(Enum) that shows the User Id and which user wrote the post.

现在我要从PublicForum中选择全部，然后根据AuthorType联接4个表中的1个.

Now I want to select All from PublicForum Join 1 of the 4 tables based on the AuthorType.

我尝试使用下面的代码对Case语句进行此操作，但是其他3个表都返回了NULL值，而我只希望与AuthorType匹配的表.

I have tried to do this with Case statement using the code below, but the other 3 tables are returned with NULL values and I want only the table that matches the 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

还有其他方法可以实现吗?

Is there any other way to acheive this?

推荐答案

您不能有条件地加入.完成此操作的方法是使用UNION组合的单独查询.

You can't join conditionally. The way to do this is with separate queries that you combine using 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的列提供通用名称.

The use of tablename.* for all the joined tables depends on them having the same number of columns, and all the columns are analogous and in the same order. The AS clauses in the first subquery then give a generic name to the columns coming from the UNION.

这篇关于根据Mysql Case语句将一个表连接到另一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！