用in语句时如果数据很大的话非常慢,建议使用关联,这样快一些: Select * from ( select o.a a1,u.a a2 from tbname o,tbname u where o.a = u.b(+) ) where a2 is null;
通常有4种写法: 1、not exists select a from table1 x where not exists (select 'x' from table1 where b=x.a); 2、not in 同bzszp(SongZip)兄 3、left join select a from table1 x, table1 y where x.a = y.b (+) and y.b is null; 本例中,还可以使用minus select a from table1 minus select b from table1;用left join通常效率比较高
我用bzszp的方法运行了10多分钟,还没有结束,我的表有10万多条记录。 我的表名是fstruct,如果用qiuyang_wang的关联办法语句如下写法是否正确: Select * from ( select o.a a1,u.a a2 from fstruct o,fstruct u where o.a = u.b) where a2 is null如果用KingSunSha(弱水三千) 的left join办法语句如下写法是否正确: select a from fstruct x, fstruct y where x.a = y.b and y.b is null;其中哪种方法是最快的?
弱水雄说的对,这种最快 select a from fstruct x, fstruct y where x.a = y.b(+) and y.b is null;
弱水雄说的对,这种最快 select a from fstruct x, fstruct y where x.a = y.b(+) and y.b is null;
弱水雄说的对,这种最快 select a from fstruct x, fstruct y where x.a = y.b(+) and y.b is null;
select a from fstruct x, fstruct y where x.a = y.b(+) and y.b is null;
请问select a from fstruct x, fstruct y where x.a = y.b(+) and y.b is null; 中的(+)是什么意思? 我按上面的写法运行出错,提示"未明确定义列"
哦,明白了,你的两个表(x,y 其实是同一个表)中都有a字段 所以要查询的a 也要加上表别名select x.a from fstruct x, fstruct y where x.a = y.b(+) and y.b is null;
哦,明白了,你的两个表(x,y 其实是同一个表)中都有a字段 所以要查询的a 也要加上表别名select x.a from fstruct x, fstruct y where x.a = y.b(+) and y.b is null;
我是在Delphi6中通过odac联接Oracle的错误提示是ora-00918:未明确定义列delphi中的代码是: with MainDM2.Oraq_Structure do begin close; sql.Clear; sql.Text := 'select DISTINCT IXKIT from blyw.F_Structure x, blyw.F_Structure y where x.IXKIT = y.IXITM(+) and y.IXITM is null'; open; end;
我是在Delphi6中通过odac联接Oracle的错误提示是ora-00918:未明确定义列delphi中的代码是: with MainDM2.Oraq_Structure do begin close; sql.Clear; sql.Text := 'select DISTINCT IXKIT from blyw.F_Structure x, blyw.F_Structure y where x.IXKIT = y.IXITM(+) and y.IXITM is null'; open; end;
Select * from ( select o.a a1,u.a a2 from tbname o,tbname u where o.a = u.b(+) ) where a2 is null;
1、not exists
select a from table1 x
where not exists (select 'x' from table1 where b=x.a);
2、not in
同bzszp(SongZip)兄
3、left join
select a from table1 x, table1 y
where x.a = y.b (+)
and y.b is null;
本例中,还可以使用minus
select a from table1
minus
select b from table1;用left join通常效率比较高
我的表名是fstruct,如果用qiuyang_wang的关联办法语句如下写法是否正确:
Select * from ( select o.a a1,u.a a2 from fstruct o,fstruct u where o.a = u.b) where a2 is null如果用KingSunSha(弱水三千) 的left join办法语句如下写法是否正确:
select a from fstruct x, fstruct y
where x.a = y.b and y.b is null;其中哪种方法是最快的?
select a from fstruct x, fstruct y
where x.a = y.b(+) and y.b is null;
select a from fstruct x, fstruct y
where x.a = y.b(+) and y.b is null;
select a from fstruct x, fstruct y
where x.a = y.b(+) and y.b is null;
where x.a = y.b(+) and y.b is null;
where x.a = y.b(+) and y.b is null;
中的(+)是什么意思? 我按上面的写法运行出错,提示"未明确定义列"
所以要查询的a 也要加上表别名select x.a from fstruct x, fstruct y
where x.a = y.b(+) and y.b is null;
所以要查询的a 也要加上表别名select x.a from fstruct x, fstruct y
where x.a = y.b(+) and y.b is null;
with MainDM2.Oraq_Structure do
begin
close;
sql.Clear;
sql.Text := 'select DISTINCT IXKIT from blyw.F_Structure x, blyw.F_Structure y where x.IXKIT = y.IXITM(+) and y.IXITM is null';
open;
end;
with MainDM2.Oraq_Structure do
begin
close;
sql.Clear;
sql.Text := 'select DISTINCT IXKIT from blyw.F_Structure x, blyw.F_Structure y where x.IXKIT = y.IXITM(+) and y.IXITM is null';
open;
end;