带 OR 的 where 子句中的 Case 语句

Case statement in where clause w/an OR(带 OR 的 where 子句中的 Case 语句)
本文介绍了带 OR 的 where 子句中的 Case 语句的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

提前道歉,因为我觉得我可能忘记/遗漏了一些明显的东西.开始;我在我的 WHERE 子句中使用 case 语句,以下工作正常:

Apologies in advance since I feel like I'm probably forgetting/missing something obvious on this one. Here goes; I'm using a case statement in my WHERE clause, the below works fine:

WHERE r.[SomeCol] = @SomeColVal
AND SomeOtherCol =  
(
CASE WHEN (@Year = 0 AND @Period = 0) THEN
@SomeVal
     WHEN...
...
     ELSE
@SomeVal
END 

我的问题"是我想在我的 ELSE 块中添加一个额外的 OR 子句......像这样:

My "issue" is that I want to add an additional OR clause to my ELSE block..something like this:

WHERE r.[SomeCol] = @SomeColVal
AND SomeOtherCol =  
(
CASE WHEN (@Year = 0 AND @Period = 0) THEN
@SomeVal
     WHEN...
...
     ELSE
@SomeVal OR @SomeVal - 1
END 

当然,这会引发此错误:关键字OR"附近的语法不正确.在 ELSE 语句中

Naturally, this throws this error: Incorrect syntax near the keyword 'OR'. within the ELSE statement

因此我的问题是……我可以用来完成此任务的正确/替代逻辑是什么?
提前致谢

Hence my question...what is the correct/alternate logic I can use to accomplish this?
Thank you in advance

推荐答案

CASE 是一个返回一个值的表达式.您可以在两个表达式之间执行 OR,而不是针对单个 CASE 表达式进行测试,唯一的区别是 else 子句.(使用 IN 作为编写 SomeOtherCol = ... OR SomeOtherCol = 的快捷方式)你可以这样做:

CASE is an expression that returns one value. Instead of testing against the single CASE expresssion, you could do an OR between two, that only difference is the else clause. (Using IN as a shortcut for writing SomeOtherCol = ... OR SomeOtherCol =) You could do:

WHERE r.[SomeCol] = @SomeColVal
AND SomeOtherCol in   
    (CASE WHEN (@Year = 0 AND @Period = 0) THEN
        @SomeVal
        CASE WHEN...
            ...
        CASE ELSE
        @SomeVal END,
    CASE WHEN (@Year = 0 AND @Period = 0) THEN
        @SomeVal
        CASE WHEN...
            ...
        CASE ELSE
        @SomeVal - 1 END)

我的猜测是这比您需要的逻辑更多,如果知道您的具体情况,可以编写更简单、更清晰的 else 语句.

My guess is this has more logic than you need, and if the specifics of your situation were known a much simpler and clearer else statement could be written.

这篇关于带 OR 的 where 子句中的 Case 语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!

本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除!

相关文档推荐

Execute complex raw SQL query in EF6(在EF6中执行复杂的原始SQL查询)
SSIS: Model design issue causing duplications - can two fact tables be connected?(SSIS:模型设计问题导致重复-两个事实表可以连接吗?)
SQL Server Graph Database - shortest path using multiple edge types(SQL Server图形数据库-使用多种边类型的最短路径)
Invalid column name when using EF Core filtered includes(使用EF核心过滤包括时无效的列名)
How should make faster SQL Server filtering procedure with many parameters(如何让多参数的SQL Server过滤程序更快)
How can I generate an entity–relationship (ER) diagram of a database using Microsoft SQL Server Management Studio?(如何使用Microsoft SQL Server Management Studio生成数据库的实体关系(ER)图?)
7