MySQL根据字段值加入不同的表。

I have a table containing the last comments posted on the website, and I'd like to join a different table depending on the comment type.

我有一个包含网站上最后评论的表格，我想根据评论类型加入一个不同的表格。

Comments Table is similar to this structure:

Comments表与此结构相似:

id | type | ressource_id |
---+------+--------------+
1  |  1   |      10      |
2  |  3   |       7      |

What I'd like to do is to join the "News" table if type type = 1 (on news.id = comments.ressource_id), "tutorial" table if type type = 3, etc.

我想做的是加入“News”表，如果类型为1(在News上)。id = comments.ressource_id)、“教程”表(类型为3)等。

How can I do this please? I've tried different queries using CASE and UNION, but never got the expected results.

请问我该怎么做?我尝试过使用CASE和UNION进行不同的查询，但从未得到预期的结果。

Thanks.

谢谢。

2 个解决方案

#1

Try using left outer join and a on clause matching on the type:

尝试使用左外连接和on子句匹配类型:

select coalesce(n.id, t.id) id
,      c.type
,      c.resource_id
from   comments c
left
outer
join   news n
on     n.id = comments.resource_id
and    c.type = 1
left
outer
join   tutorial t
on     t.id = comments.resource_id
and    c.type = 3

#2

You can do this with unioned queries assuming that you can coerce partial queries into producing a similar schema, along the lines of:

您可以使用统一的查询来实现这一点，假设您可以强制执行部分查询，从而生成一个类似的模式:

select n.id as id, c.something as something
    from news n, comments c
    where n.type = 1
    and   n.id = c.resource_id
union all
select n.id as id, t.something as something
    from news n, tutorial t
    where n.type = 3
    and   n.id = t.resource_id

In other words, the first query simply joins news and comments for rows where news.type indicates a comment, and the second query joins news and tutorials for rows where news.type indicates a tutorial.

换句话说，第一个查询只是将新闻和注释连接到新闻所在的行。类型指示一个注释，第二个查询将新闻和教程连接到新闻所在的行。类型表示一个教程。

Then the union combined the two into a single record set.

然后联盟将这两个组合成一个记录集。

I'd steer clear of case in this situation (and many others) since it almost always invariably requires per-row modification of the data, which rarely scales wee. Running two queries and combining the results is usually more efficient.

在这种情况下(以及许多其他情况下)，我将避免使用case，因为它几乎总是要求对数据进行每一行的修改，而这些修改很少被缩放。运行两个查询并组合结果通常更有效。

#1