条件 JOIN 语句 SQL Server
是否可以执行以下操作:
Is it possible to do the following:
IF [a] = 1234 THEN JOIN ON TableA
ELSE JOIN ON TableB
如果是，正确的语法是什么?
If so, what is the correct syntax?
推荐答案
我认为通过将 Initial 表加入 Option_A 和 Option_B 使用 LEFT JOIN，这将产生如下结果:
I think what you are asking for will work by joining the Initial table to both Option_A and Option_B using LEFT JOIN, which will produce something like this:
Initial LEFT JOIN Option_A LEFT JOIN NULL
OR
Initial LEFT JOIN NULL LEFT JOIN Option_B
示例代码:
SELECT i.*, COALESCE(a.id, b.id) as Option_Id, COALESCE(a.name, b.name) as Option_Name
FROM Initial_Table i
LEFT JOIN Option_A_Table a ON a.initial_id = i.id AND i.special_value = 1234
LEFT JOIN Option_B_Table b ON b.initial_id = i.id AND i.special_value <> 1234
完成此操作后，您将忽略"NULL 集.这里的额外技巧是在 SELECT 行中，您需要在其中决定如何处理 NULL 字段.如果 Option_A 和 Option_B 表相似，那么您可以使用 COALESCE 函数返回第一个非空值(如示例所示).
Once you have done this, you 'ignore' the set of NULLS. The additional trick here is in the SELECT line, where you need to decide what to do with the NULL fields. If the Option_A and Option_B tables are similar, then you can use the COALESCE function to return the first NON NULL value (as per the example).
另一种选择是，您只需列出 Option_A 字段和 Option_B 字段，然后让使用 ResultSet 的任何内容来确定要使用的字段.
The other option is that you will simply have to list the Option_A fields and the Option_B fields, and let whatever is using the ResultSet to handle determining which fields to use.
相关文章