困惑于“或” oracle中的条件子句？

发布于 2024-11-08 23:31:08 字数 799 浏览 0 评论 0原文

这是我工作中遇到的问题，我简化如下：首先是测试用例

create table A_TESTB
(
  A VARCHAR2(10),
  B VARCHAR2(10)
);

insert into A_TESTB (A, B)
values ('1', '2');
insert into A_TESTB (A, B)
values ('1', '3');
insert into A_TESTB (A, B)
values ('1', '4');
commit;

SQL> select * from a_testb;

A          B
---------- ----------
1          2
1          3
1          4

，我使用如下sql

SQL> SELECT *
  2    FROM (SELECT '10' TESTTYPE, T.* FROM A_TESTB T) W
  3   WHERE (W.A = '1' OR 1 = 2)
  4     AND W.TESTTYPE = '999999';

TESTTYPE A          B
-------- ---------- ----------
10       1          2
10       1          3
10       1          4

问题是：我使用条件 AND W.TESTTYPE = '999999' 进行过滤，我期望的结果集应该是空的，但似乎该条件不起作用，秘密可能来自OR，但为什么它不起作用？

原文

It's a problem I encountered in the work and I simplify it as follows:
First, is the testcase

create table A_TESTB
(
  A VARCHAR2(10),
  B VARCHAR2(10)
);

insert into A_TESTB (A, B)
values ('1', '2');
insert into A_TESTB (A, B)
values ('1', '3');
insert into A_TESTB (A, B)
values ('1', '4');
commit;

SQL> select * from a_testb;

A          B
---------- ----------
1          2
1          3
1          4

then,I use sql like below

SQL> SELECT *
  2    FROM (SELECT '10' TESTTYPE, T.* FROM A_TESTB T) W
  3   WHERE (W.A = '1' OR 1 = 2)
  4     AND W.TESTTYPE = '999999';

TESTTYPE A          B
-------- ---------- ----------
10       1          2
10       1          3
10       1          4

The question is : I have used the condition AND W.TESTTYPE = '999999' to filter， and my expecting resultset should be empty,but it seems that the condition doesn't work ,the secret maybe from OR, but why doesn't it work?

分享到QQ

分享到微博

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

发布评论

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

难忘№最初的完美 2024-11-15 23:31:08

我的结果集是空的！

我在

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0  Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

使用 TOAD 版本 10.5.0.41 的查询中使用 NO ROWS RETURNED：

SELECT *
  FROM (SELECT '10' TESTTYPE, T.*
          FROM A_TESTB T) W
 WHERE (W.A = '1' OR 1 = 2) AND W.TESTTYPE = '999999';

我也在 SQL*Plus 中运行它，只是为了 100% 确定：

SQL> SELECT *
  2    FROM (SELECT '10' TESTTYPE, T.* FROM A_TESTB T) W
  3   WHERE (W.A = '1' OR 1 = 2)
  4      AND W.TESTTYPE = '999999';

No rows selected.

SQL>

My result set is empty!

I am using

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0  Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

NO ROWS RETURNED for your query using TOAD release 10.5.0.41:

SELECT *
  FROM (SELECT '10' TESTTYPE, T.*
          FROM A_TESTB T) W
 WHERE (W.A = '1' OR 1 = 2) AND W.TESTTYPE = '999999';

I run it also in SQL*Plus, just to be 100% sure:

SQL> SELECT *
  2    FROM (SELECT '10' TESTTYPE, T.* FROM A_TESTB T) W
  3   WHERE (W.A = '1' OR 1 = 2)
  4      AND W.TESTTYPE = '999999';

No rows selected.

SQL>

回复收藏 0 原文

~没有更多了~