很显然,两个查询的结果不是你说的结果
我这样试过了,正确
select t1.kh
from (select 119 kh from dual) t1,
(select 110 kh from dual) t2
where t1.kh<>t2.kh
我这样试过了,正确
select t1.kh
from (select 119 kh from dual) t1,
(select 110 kh from dual) t2
where t1.kh<>t2.kh
where t1.kh<>t2.kh作为条件将会把T1,T2的所有记录作一个组合输出,并去掉相同的kh的值,select t1.kh,t2.kh 就会看到情况如何了。
用下列方法得到的是一条记录
select t1.kh from (select a.kh from t_ksbm a where yyrq='2004-10-19') t1,
where kh not in (select a.kh from t_dtb a where a.kzrq = '2004-10-19')
select t1.kh
from (select 119 kh from dual
union all
select 120 kh from dual
) t1,
(select 119 kh from dual
union all
select 110 kh from dual
) t2
where t1.kh<>t2.kh结果是(
1 120
2 119
3 120
where kh not in (select a.kh from t_dtb a where a.kzrq = '2004-10-19')
这样是可以,不过效率慢,谁还有更好的方法
select t1.kh from (select a.kh from t_ksbm a where yyrq='2004-10-19') t1,
(select a.kh from t_dtb a where a.kzrq = '2004-10-19') t2
where t1.kh<>t2.kh
既然
select a.kh from t_ksbm a where yyrq='2004-10-19' 查出来的记录是120,
select a.kh from t_dtb a where a.kzrq = '2004-10-19' 查出来的记录是119,
那么
这个语句会把两个表所有行的kh列都显示出来,因为你的where条件是t1.kh<>t2.kh,这个where子句在这个语句中会一直返回真,那就是不管哪一行都符合这个条件,因此哪一行都会被select语句返回。
二楼那个用dual表查当然只会有一行,因为那个表只有一行一列而已。