SQL 我可以使用 ON 语句中的 CASE 子句加入 ORACLE (10g) 中的表吗（或者甚至 where 子句，因为它是一个内部联接）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/427736/
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
Can I join to a table in ORACLE (10g) using a CASE clause in the ON statement (or even where clause as it's an inner join)
提问by Dave Costa
I'm trying to make the following code smaller. Is this possible?
我正在尝试使以下代码更小。这可能吗？
select a.*
from table1 a
WHERE a."cola1" = 'valuea1'
UNION ALL
select a.*
from tablea1 a
inner join tablea2 b on a."cola2" = b."colb2"
WHERE a."cola1" = 'valuea2'
and b."colb3" = 'valueb3'
In effect I'm looking for records from table1 for value1 or value2, but for records matching value2 I want to apply 1 extra condition which involves a join to a 2nd table Can this be done without a UNION clause?
实际上，我正在从 table1 中查找 value1 或 value2 的记录，但是对于匹配 value2 的记录，我想应用 1 个额外条件，其中涉及到第二个表的连接 这可以在没有 UNION 子句的情况下完成吗？
A skeleton or what I'm trying to code is below....but it's not working naturally.
下面是一个骨架或我正在尝试编写的代码......但它不能自然地工作。
select a.*
from table1 a
inner join table2 b on a."cola1" = b."COLb1"
WHERE a."cola2" IN ('valuea1','valuea2')
and
CASE
WHEN a."cola2" = 'valuea2' THEN b."colb1" = 'valueb3'
ELSE 1=1
END CASE
回答by Hosam Aly
I think CASEstatements work in join conditions, but I'm not sure. But would this work for you?
我认为CASE语句在连接条件下有效，但我不确定。但这对你有用吗？
select *
from table1 a
where a.cola1 = 'valuea1'
or (a.cola1 = 'valuea2'
and Exists(select 1
from table2 b
where a.cola2 = b.colb2
and b.colb3 = 'valueb3'
)
)
Edit:Wouldn't this simply work?
编辑：这不是简单的工作吗？
select a.*
from table1 a
Left Outer Join table2 b On (a.cola2 = b.colb2)
where a.cola1 = 'valuea1'
or (a.cola1 = 'valuea2' and b.colb3 = 'valueb3')
回答by Dave Costa
Overall you should follow Hosam's suggestion of rewriting the predicate entirely. But to explain your original problem further, the issue is that in SQL, CASE .. END is an expressionand can only be used where any other expression could be used. A condition like "a=b" is two expressions connected by a logical operator. You may want to think of it as a boolean expression but that's not the way SQL views it.
总的来说，您应该遵循 Hosam 的建议，完全重写谓词。但是为了进一步解释您的原始问题，问题在于在 SQL 中， CASE .. END 是一个表达式，只能在可以使用任何其他表达式的地方使用。像“a=b”这样的条件是由逻辑运算符连接的两个表达式。您可能希望将其视为布尔表达式，但这不是 SQL 看待它的方式。
You could accomplish what you want with CASE by using it as one of the two expressions in the condition, like so:
您可以通过将 CASE 用作条件中的两个表达式之一来完成您想要的操作，如下所示：
WHERE a."cola2" IN ('valuea1','valuea2')
and
b."colb1" = CASE
WHEN a."cola2" = 'valuea2' THEN 'valueb3'
ELSE b."colb1"
END CASE
(If it is possible for colb1 to include NULLs you would need to modify to handle that.)
（如果 colb1 可能包含 NULL，您需要修改以处理它。）
回答by Joshan George
You can achieve this by using left join and where condition
您可以通过使用左连接和 where 条件来实现这一点
select a.*
from table1 a
left join tablea2 b on a."cola2" = b."colb2"
WHERE a."cola1" = 'valuea2'
and ( b."colb2" is null or b."colb3" = 'valueb3' )
回答by Joshan George
OP: I've got a mini-workaround which goes close (This may only work given this is an inner join.)
OP：我有一个接近的小型解决方法（这可能只有在这是一个内部连接的情况下才有效。）
select a.* from table1 a inner join table2 b on a."cola1" = b."COLb1" WHERE (a."cola2" = 'valuea1') OR (a."cola2" = 'valuea2' and b."colb1" = 'valueb3')
select a.* from table1 a inner join table2 b on a."cola1" = b."COLb1" WHERE (a."cola2" = 'valuea1') OR (a."cola2" = 'valuea2' and b." colb1" = 'valueb3')
Sometimes writing code out can prompt some alternative thinking. Self-Therapy sort of. Thanks for your input.
有时，写出代码会引发一些另类的思考。自我治疗之类的。感谢您的输入。
