Oracle sql 中的 ALL,ANY,SOME

最新推荐文章于 2023-02-12 17:50:05 发布

weixin_30952535

最新推荐文章于 2023-02-12 17:50:05 发布

阅读量138

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://www.cnblogs.com/pekkle/p/6568766.html

本文深入解析SQL中的ANY, SOME与ALL关键字的使用方式与区别，通过实例展示它们在查询中的应用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

[转自] http://www.itpub.net/thread-1355835-1-1.html

any和some是等价的，其与all的前面都只能是比较符号，即=, !=, >, <, <=, >=；其与all的后面都只能是子查询或者一串值

any的含义是将一个值与any后的每一个值去比较，满足任何一个比较条件即为true

all的含义是将一个值与all后的每一个值去比较，满足全部比较条件才为true

CREATE TABLE tmp_test(ID number,txt VARCHAR2(10));
 CREATE table tmp_test_his AS SELECT * from tmp_test WHERE 1=2;
 INSERT INTO tmp_test VALUES(1,'a');
 INSERT INTO tmp_test VALUES(2,'b');
 INSERT INTO tmp_test VALUES(3,'c');
 INSERT INTO tmp_test VALUES(4,'d');
 INSERT into tmp_test_his VALUES(2,'b');
 INSERT into tmp_test_his VALUES(3,'c');
 INSERT into tmp_test_his VALUES(4,'x');

select a.* FROM tmp_test_his a where (a.id,a.txt) = all(select b.id,b.txt  from tmp_test b where a.id=b.id and a.txt=b.txt);
        ID TXT      
---------- ----------
         2 b        
         3 c        
         4 x        

3 rows selected.

select b.id,b.txt from tmp_test b,tmp_test_his a where a.id=b.id and a.txt=b.txt;

ID TXT
---------- ----------
2 b
3 c

2 rows selected.

select a.* FROM tmp_test_his a where (a.id,a.txt) = all(select b.id,b.txt  from tmp_test b, tmp_test_his a where a.id=b.id and a.txt=b.txt);

no rows selected.

 select a.* FROM tmp_test_his a where (a.id,a.txt) = any(select b.id,b.txt  from tmp_test b, tmp_test_his a where a.id=b.id and a.txt=b.txt);


        ID TXT      
---------- ----------
         2 b        
         3 c   
2 rows selected.

转载于:https://www.cnblogs.com/pekkle/p/6568766.html