Sql如下:
select *
from (select 0 as ccccc
from tb1
left join tb2 c on ('0000' || c.id = b.id or c.id = b.id)
)
where ccccc <> 0
上面的Sql查询出的记录,ccccc等于0的记录居然也出来了,为什么啊?????注:tb1的记录全部要出来,所以用Left join
select *
from (select 0 as ccccc
from tb1
left join tb2 c on ('0000' || c.id = b.id or c.id = b.id)
)
where ccccc <> 0
上面的Sql查询出的记录,ccccc等于0的记录居然也出来了,为什么啊?????注:tb1的记录全部要出来,所以用Left join
表结构和数据:
create table A
(
A1 VARCHAR2(1),
A2 VARCHAR2(10)
);create table B
(
B1 VARCHAR2(1),
B2 VARCHAR2(10)
);insert into A (A1, A2)values ('1', '11');
insert into A (A1, A2)values ('2', '22');
insert into A (A1, A2)values ('3', '33');
insert into B (B1, B2)values ('1', '11');
insert into B (B1, B2)values ('00001', '12');
insert into B (B1, B2)values ('2', '21');
insert into B (B1, B2)values ('2', '22');
insert into B (B1, B2)values ('2', '23');
Sql语句:
select * from (
select 0 as a1 from a
left join b on '0000' || a.a1 = b.b1 or a.a1 = b.b1
)
where a1 <> 0执行结果:
A1
1 0
2 0
3 0
create table A
(
A1 VARCHAR2(10),
A2 VARCHAR2(10)
); create table B
(
B1 VARCHAR2(10),
B2 VARCHAR2(10)
);
2 select 0 as a1 from a
3 left join b on '0000' || a.a1 = b.b1 or a.a1 = b.b1
4 )
5 where a1 <> 0 ;no rows selectedSQL>用你的例子做测试,没有问题啊。
他在9.2.0.7上测试也没有问题
我的版本是9.2.0.1
SQL*Plus: Release 9.2.0.1.0 - Production on Wed Jan 21 10:45:38 2009Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
code]我的是10.2.0.4.0
我的是9.2.0.4.0
最后的判断改成where 0<>0或者1<>1也是这样的结果(实际上解释计划也翻译成这样了)
数据库版本,我用9.2.0.1 做测试可以查出和楼主相同的记录,用10.2.0.1.0做测试就查不到记录
这个帖子已经解决了