我可以在 ON 语句中使用 CASE 子句(甚至是 where 子句,因为它是内部联接)来联接 ORACLE (10g) 中的表
我正在尝试使以下代码更小。 这可能吗?
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'
实际上,我正在从 table1 中查找 value1 或 value2 的记录,但对于匹配 value2 的记录,我想应用 1 个额外条件,其中涉及到第二个表的联接 可以在没有 UNION 子句的情况下完成此操作吗?
框架或我正在尝试编写的代码如下......但它不能自然地工作。
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
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?
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
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
我认为
CASE
语句在连接条件下有效,但我不确定。 但这对你有用吗?编辑:这不是很简单吗?
I think
CASE
statements work in join conditions, but I'm not sure. But would this work for you?Edit: Wouldn't this simply work?
总的来说,您应该遵循 Hosam 的建议,完全重写谓词。 但为了进一步解释你原来的问题,问题是在 SQL 中, CASE .. END 是一个表达式,并且只能在可以使用任何其他表达式的地方使用。 像“a=b”这样的条件是通过逻辑运算符连接的两个表达式。 您可能希望将其视为布尔表达式,但这不是 SQL 看待它的方式。
您可以通过将 CASE 用作条件中的两个表达式之一来完成您想要的操作,如下所示:(
如果 colb1 可能包含 NULL,则需要进行修改以处理该问题。)
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 expression and 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.
You could accomplish what you want with CASE by using it as one of the two expressions in the condition, like so:
(If it is possible for colb1 to include NULLs you would need to modify to handle that.)
您可以通过使用 left join 和 where 条件来实现这一点
You can achieve this by using left join and where condition
OP:我有一个接近的迷你解决方法(这可能只在这是一个内部联接的情况下有效。)
select a.* from table1 a
在 a."cola1" = b."COLb1" 上内连接 table2 b
在哪里
(a."cola2" = 'valuea1')
OR (a."cola2" = 'valuea2' and b."colb1" = 'valueb3')
有时写出代码可以激发一些替代思维。 自我治疗之类的。 感谢您的输入。
OP: I've got a mini-workaround which goes close (This may only work given this is an inner join.)
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.