<legend id='fcaUl'><style id='fcaUl'><dir id='fcaUl'><q id='fcaUl'></q></dir></style></legend>
    <tfoot id='fcaUl'></tfoot>
    • <bdo id='fcaUl'></bdo><ul id='fcaUl'></ul>

    <i id='fcaUl'><tr id='fcaUl'><dt id='fcaUl'><q id='fcaUl'><span id='fcaUl'><b id='fcaUl'><form id='fcaUl'><ins id='fcaUl'></ins><ul id='fcaUl'></ul><sub id='fcaUl'></sub></form><legend id='fcaUl'></legend><bdo id='fcaUl'><pre id='fcaUl'><center id='fcaUl'></center></pre></bdo></b><th id='fcaUl'></th></span></q></dt></tr></i><div id='fcaUl'><tfoot id='fcaUl'></tfoot><dl id='fcaUl'><fieldset id='fcaUl'></fieldset></dl></div>

      <small id='fcaUl'></small><noframes id='fcaUl'>

    1. 在 where 子句中使用“case 表达式列"

      Using #39;case expression column#39; in where clause(在 where 子句中使用“case 表达式列)
          <tfoot id='Z0SFg'></tfoot>

            • <bdo id='Z0SFg'></bdo><ul id='Z0SFg'></ul>
              <i id='Z0SFg'><tr id='Z0SFg'><dt id='Z0SFg'><q id='Z0SFg'><span id='Z0SFg'><b id='Z0SFg'><form id='Z0SFg'><ins id='Z0SFg'></ins><ul id='Z0SFg'></ul><sub id='Z0SFg'></sub></form><legend id='Z0SFg'></legend><bdo id='Z0SFg'><pre id='Z0SFg'><center id='Z0SFg'></center></pre></bdo></b><th id='Z0SFg'></th></span></q></dt></tr></i><div id='Z0SFg'><tfoot id='Z0SFg'></tfoot><dl id='Z0SFg'><fieldset id='Z0SFg'></fieldset></dl></div>
            • <legend id='Z0SFg'><style id='Z0SFg'><dir id='Z0SFg'><q id='Z0SFg'></q></dir></style></legend>
            • <small id='Z0SFg'></small><noframes id='Z0SFg'>

                  <tbody id='Z0SFg'></tbody>
              1. 本文介绍了在 where 子句中使用“case 表达式列"的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

                问题描述

                SELECT ename
                  ,    job
                  ,    CASE deptno
                         WHEN 10
                           THEN 'ACCOUNTS'
                         WHEN 20
                           THEN 'SALES'
                         ELSE 'UNKNOWN'
                       END AS department
                FROM emp /* !!! */ 
                WHERE department = 'SALES'
                

                这失败了:

                ORA-00904:%s:无效标识符"

                ORA-00904: "%s: invalid identifier"

                有没有办法克服 Oracle 10.2 SQL 中的这个限制?如何在 where 子句中使用case 表达式列"?

                Is there a way to overcome this limitation in Oracle 10.2 SQL ? How to use the 'case expression column' in where clause ?

                推荐答案

                出现这个错误的原因是 SQL SELECT 语句是逻辑上 * 按以下顺序处理:

                The reason for this error is that SQL SELECT statements are logically * processed in the following order:

                • FROM:选择一张表或多张JOINed表以及符合ON条件的所有行组合.

                WHERE:评估条件并删除不匹配的行.

                WHERE: conditions are evaluated and rows that do not match are removed.

                GROUP BY:行被分组(每组折叠为一行)

                GROUP BY: rows are grouped (and every group collapses to one row)

                HAVING:评估条件并删除不匹配的行.

                HAVING: conditions are evaluated and rows that do not match are removed.

                SELECT:评估列列表.

                SELECT: list of columns is evaluated.

                DISTINCT:删除重复的行(如果是 SELECT DISTINCT 语句)

                DISTINCT: duplicate rows are removed (if it's a SELECT DISTINCT statement)

                UNIONEXCEPTINTERSECT:对子 SELECT 语句的行执行该操作数的操作.例如,如果它是 UNION,则在评估所有子 SELECT 语句后收集所有行(并消除重复,除非它是 UNION ALL).因此,对于 EXCEPT 或 INTERSECT 情况.

                UNION, EXCEPT, INTERSECT: the action of that operand is taken upon the rows of sub-SELECT statements. For example, if it's a UNION, all rows are gathered (and duplicates eliminated unless it's a UNION ALL) after all sub-SELECT statements are evaluated. Accordingly for the EXCEPT or INTERSECT cases.

                ORDER BY:行被排序.

                ORDER BY: rows are ordered.

                因此,您不能在 WHERE 子句中使用尚未填充或计算的内容.另请参阅此问题:oracle-sql-clause-evaluation-order

                Therefore, you can't use in WHERE clause, something that hasn't been populated or calculated yet. See also this question: oracle-sql-clause-evaluation-order

                * 逻辑处理: 请注意,数据库引擎也可以为查询选择其他评估顺序(这就是他们通常做的!)唯一的限制是结果应该与使用上述顺序相同.

                解决方案是将查询包含在另一个查询中:

                SELECT *
                FROM
                  ( SELECT ename
                         , job
                         , CASE deptno
                             WHEN 10 THEN 'ACCOUNTS'
                             WHEN 20 THEN 'SALES'
                                     ELSE 'UNKNOWN'
                           END AS department
                    FROM emp
                  ) tmp
                WHERE department = 'SALES' ;
                

                复制 WHERE 条件中的计算:

                SELECT ename
                     , job
                     , CASE deptno
                         WHEN 10 THEN 'ACCOUNTS'
                         WHEN 20 THEN 'SALES'
                                 ELSE 'UNKNOWN'
                       END AS department
                FROM emp
                WHERE
                    CASE deptno
                      WHEN 10 THEN 'ACCOUNTS'
                      WHEN 20 THEN 'SALES'
                              ELSE 'UNKNOWN'
                    END = 'SALES' ;
                

                <小时>

                我想这是您查询的简化版本,或者您可以使用:


                I guess this is a simplified version of your query or you could use:

                SELECT ename
                     , job
                     , 'SALES' AS department
                FROM emp
                WHERE deptno = 20 ;
                

                这篇关于在 where 子句中使用“case 表达式列"的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益,请联系我们,我们会在确认后第一时间进行删除!

                相关文档推荐

                Oracle PL/SQL - Raise User-Defined Exception With Custom SQLERRM(Oracle PL/SQL - 使用自定义 SQLERRM 引发用户定义的异常)
                Oracle: is there a tool to trace queries, like Profiler for sql server?(Oracle:是否有跟踪查询的工具,例如用于 sql server 的 Profiler?)
                SELECT INTO using Oracle(使用 Oracle SELECT INTO)
                How to handle Day Light Saving in Oracle database(如何在 Oracle 数据库中处理夏令时)
                PL/SQL - Use quot;Listquot; Variable in Where In Clause(PL/SQL - 使用“列表Where In 子句中的变量)
                Oracle: Import CSV file(Oracle:导入 CSV 文件)
                  <bdo id='HBnGQ'></bdo><ul id='HBnGQ'></ul>

                  • <tfoot id='HBnGQ'></tfoot>

                      <small id='HBnGQ'></small><noframes id='HBnGQ'>

                        <legend id='HBnGQ'><style id='HBnGQ'><dir id='HBnGQ'><q id='HBnGQ'></q></dir></style></legend>

                          <tbody id='HBnGQ'></tbody>
                        <i id='HBnGQ'><tr id='HBnGQ'><dt id='HBnGQ'><q id='HBnGQ'><span id='HBnGQ'><b id='HBnGQ'><form id='HBnGQ'><ins id='HBnGQ'></ins><ul id='HBnGQ'></ul><sub id='HBnGQ'></sub></form><legend id='HBnGQ'></legend><bdo id='HBnGQ'><pre id='HBnGQ'><center id='HBnGQ'></center></pre></bdo></b><th id='HBnGQ'></th></span></q></dt></tr></i><div id='HBnGQ'><tfoot id='HBnGQ'></tfoot><dl id='HBnGQ'><fieldset id='HBnGQ'></fieldset></dl></div>