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');
any和some是等价的，其与all的前面都只能是比较符号，即=, !=, >, <, <=, >=；其与all的后面都只能是子查询或者一串值
any的含义是将一个值与any后的每一个值去比较，满足任何一个比较条件即为true
all的含义是将一个值与all后的每一个值去比较，满足全部比较条件才为true
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.
--转自
当前位置：
论坛首页 »
自由讨论区 »
MySQL, Oracle及数据库讨论区
»
发起投票
技术讨论
加好友
发消息
2015-7-27 16:48:43 |
赞(
操作
