SQL 条件内连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7268604/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Conditional Inner Join
提问by KristianB
I want to be able to inner join two tables based on the result of an expression.
我希望能够根据表达式的结果内部连接两个表。
What I've been trying so far:
到目前为止我一直在尝试：
INNER JOIN CASE WHEN RegT.Type = 1 THEN TimeRegistration ELSE DrivingRegistration AS RReg
ON
RReg.RegistreringsId = R.Id
RegT is a join I made just before this join:
RegT 是我在此加入之前进行的加入：
INNER JOIN RegistrationTypes AS RegT ON R.RegistrationTypeId = RegT.Id
This SQL-script does not work.
此 SQL 脚本不起作用。
So all in all, if the Typeis 1, then it should join on the table TimeRegistrationelse it should join on DrivingRegistration.
总而言之，如果Type是 1，那么它应该加入表，TimeRegistration否则它应该加入DrivingRegistration。
Solution:
解决方案：
In my select statement I performed the following joins:
在我的 select 语句中，我执行了以下连接：
INNER JOIN RegistrationTypes AS RegT ON R.RegistrationTypeId = RegT.Id
LEFT OUTER JOIN TimeRegistration AS TReg ON TReg.RegistreringsId = R.Id AND RegT.Type = 1
LEFT OUTER JOIN DrivingRegistration AS DReg ON DReg.RegistreringsId = R.Id AND RegT.Type <>1
Then I edited my where-clauseto output the correct, depending on the RegType, like this:
然后我编辑 mywhere-clause以输出正确的，具体取决于RegType，如下所示：
WHERE (CASE RegT.Type WHEN 1 THEN TReg.RegistreringsId ELSE DReg.RegistreringsId END = R.Id)
采纳答案by Sparky
Try putting both tables in the query using LEFT JOIN's
尝试使用 LEFT JOIN 将两个表放在查询中
LEFT JOIN TimeRegistration TR ON r.rid = TR.Id AND RegT.type =1
LEFT JOIN DrivingRegistration DR ON r.rid = DR.Id AND RegT.type <>1
Now, in you select clause, use
现在，在您选择子句中，使用
CASE RegType.Type WHEN 1 THEN TR.SomeField ELSE DR.someField END as SomeField
The other option is to use dynamic SQL
另一种选择是使用动态 SQL
回答by David
You probably need to perform two left joins, one onto TimeRegistrationand one onto DrivingRegistration, and return the fields you want from the appropriate join table something like this:
您可能需要执行两个左连接，一个 onTimeRegistration和一个 on DrivingRegistration，然后从适当的连接表中返回您想要的字段，如下所示：
LEFT JOIN TimeRegistration ON TimeRegistration.RegistreringsId = R.Id
LEFT JOIN DrivingRegistration ON DrivingRegistration.RegistreringsId = R.Id
and you select statement would be something like this:
你选择语句将是这样的：
SELECT CASE WHEN RegT.Type = 1 THEN TimeRegistration.Foo ELSE DrivingRegistration.Bar END
I like what you're trying to do, but I don't think SQL is that clever.
我喜欢你正在尝试做的事情，但我认为 SQL 没有那么聪明。
回答by Tomalak
SELECT
R.foo, tr.bar
FROM
SomeTable AS R
INNER JOIN RegistrationTypes AS RegT ON R.RegistrationTypeId = RegT.Id
AND RegT1.Type = 1
INNER JOIN TimeRegistration AS tr ON /* whatever */
UNION
SELECT
R.foo, dr.bar
FROM
SomeTable AS R
INNER JOIN RegistrationTypes AS RegT ON R.RegistrationTypeId = RegT.Id
AND RegT1.Type = 2
INNER JOIN DrivingRegistration AS dr ON /* whatever */
回答by ozzy432836
So I had a scenario where there were three email columns in one table (don't ask why) and any of them could be null (or empty). In this example code I will just deal with a case where it is null.
所以我有一个场景，在一个表中有三个电子邮件列（不要问为什么），其中任何一个都可以为空（或为空）。在这个示例代码中，我将只处理它为空的情况。
I had to join it to another table by any of the emails to retrieve the users firstname.
我必须通过任何电子邮件将其加入另一个表以检索用户的名字。
Here is what worked
这是有效的
select
m.email1,
m.email2,
m.email3,
m2.firstName
from MyTable m
left join MyOtherTable m2 on m2.Email =
case when m.email1 is null then
case when m.email2 is null then
case when m.email3 null then
'[email protected]' -- i stopped here
else m.email3 end
else wm.email2 end
else m.email1 end
Obviously you would include further conditions like
显然你会包括更多的条件，比如
case when m.email1 is null or m.email1 = '' then ...
To cover for empty values.
覆盖空值。
