WHERE 子句中的嵌套 CASE 语句

发布于 01-16 13:09 字数 813 浏览 2 评论 0原文

我正在尝试使用嵌套 CASE 语句(下方粗体),其中基于第一个 CASE 语句,我需要验证内部 where 子句,否则验证其他内部 where 子句。使用嵌套 CASE When 语句时,以下方法是正确的吗?

SELECT COUNT(*)
FROM   <list of table>
WHERE  <list of conditions > 
AND    CASE  WHEN cond1 <> 1 or cond2 <>2 or cond3 <> 3
             THEN 
                WHEN ( col1,col2,col3) NOT IN (SELECT col1,col2,col3
                                               FROM   table 1,table 2
                                               WHERE  <condition1> )
                ELSE ( col1,col2,col3,col4) NOT IN (SELECT col1,col2,col3,col4
                                                    FROM   table 1,table 2
                                                    WHERE  <condition2> )
             END

I am trying to use NESTED CASE statement(BOLD Below) where, based on first CASE statment, I need to verify inner where clause, else other inner where clause. Is below right approach when using nested CASE When statements?

SELECT COUNT(*)
FROM   <list of table>
WHERE  <list of conditions > 
AND    CASE  WHEN cond1 <> 1 or cond2 <>2 or cond3 <> 3
             THEN 
                WHEN ( col1,col2,col3) NOT IN (SELECT col1,col2,col3
                                               FROM   table 1,table 2
                                               WHERE  <condition1> )
                ELSE ( col1,col2,col3,col4) NOT IN (SELECT col1,col2,col3,col4
                                                    FROM   table 1,table 2
                                                    WHERE  <condition2> )
             END

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

む无字情书2025-01-23 13:09:39

使用 ANDOR

SELECT COUNT(*)
FROM   <list of table>
WHERE  <list of conditions > 
AND    (  (   ( cond1 <> 1 OR cond2 <> 2 OR cond3 <> 3 )
          AND (col1,col2,col3) NOT IN (SELECT col1,col2,col3
                                       FROM   table1
                                              INNER JOIN table2
                                              ON (<condition>)
                                       WHERE  <condition1>)
          )
       OR (   NOT ( cond1 <> 1 OR cond2 <> 2 OR cond3 <> 3 )
          AND (col1,col2,col3,col4) NOT IN (SELECT col1,col2,col3,col4
                                            FROM   table1
                                                   INNER JOIN table2
                                                   ON (<condition2>)
                                            WHERE  <condition2> )
          )
       )

注意:不要使用旧版逗号连接,请使用现代 ANSI 连接语法。

Use AND and OR:

SELECT COUNT(*)
FROM   <list of table>
WHERE  <list of conditions > 
AND    (  (   ( cond1 <> 1 OR cond2 <> 2 OR cond3 <> 3 )
          AND (col1,col2,col3) NOT IN (SELECT col1,col2,col3
                                       FROM   table1
                                              INNER JOIN table2
                                              ON (<condition>)
                                       WHERE  <condition1>)
          )
       OR (   NOT ( cond1 <> 1 OR cond2 <> 2 OR cond3 <> 3 )
          AND (col1,col2,col3,col4) NOT IN (SELECT col1,col2,col3,col4
                                            FROM   table1
                                                   INNER JOIN table2
                                                   ON (<condition2>)
                                            WHERE  <condition2> )
          )
       )

Notes: Do not use legacy comma joins, use the modern ANSI join syntax.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文