create table aaa (id int,titile char(10)) ----建立测试数据 insert into aaa select 1,'a' insert into aaa select 2,'b' insert into aaa select 3,'c' insert into aaa select 4,'d' insert into aaa select 5,'e' create table bbb (id int, titile char(10)) insert into bbb select 1,'a' insert into bbb select 2,'b' insert into bbb select 2,'c' insert into bbb select 3,'c' insert into bbb select 3,'111' insert into bbb select 3,'1' insert into bbb select 4,'22' insert into bbb select 4,'55' insert into bbb select 5,'d' insert into bbb select 5,'e'select distinct aaa.id,aaa.titile from aaa join bbb on aaa.id = bbb.id where aaa.titile not in(select titile from bbb where aaa.id=bbb.id)
用了like效率就比较差了,能不用like写吗?
select a.id from a where (a.id between 3 and 5) and not exists (select * from b where a.id=id and title=a.title)
create table aaa (id int,titile char(10)) ----建立测试数据 insert into aaa select 1,'a' insert into aaa select 2,'b' insert into aaa select 3,'c' insert into aaa select 4,'d' insert into aaa select 5,'e' create table bbb (id int, titile char(10)) insert into bbb select 1,'a' insert into bbb select 2,'b' insert into bbb select 2,'c' insert into bbb select 3,'c' insert into bbb select 3,'111' insert into bbb select 3,'1' insert into bbb select 4,'22' insert into bbb select 4,'55' insert into bbb select 5,'d' insert into bbb select 5,'e'select * from bbb where id not in(select id from aaa where id between 3 and 5)
select * from bbb where titile not in(select titile from aaa where id between 3 and 5)
create table aaa (id int,titile char(10)) ----建立测试数据
insert into aaa select 1,'a'
insert into aaa select 2,'b'
insert into aaa select 3,'c'
insert into aaa select 4,'d'
insert into aaa select 5,'e'
create table bbb (id int, titile char(10))
insert into bbb select 1,'a'
insert into bbb select 2,'b'
insert into bbb select 2,'c'
insert into bbb select 3,'c'
insert into bbb select 3,'111'
insert into bbb select 3,'1'
insert into bbb select 4,'22'
insert into bbb select 4,'55'
insert into bbb select 5,'d'
insert into bbb select 5,'e'select distinct aaa.id,aaa.titile
from aaa join bbb
on aaa.id = bbb.id
where aaa.titile not in(select titile from bbb where aaa.id=bbb.id)
insert into aaa select 1,'a'
insert into aaa select 2,'b'
insert into aaa select 3,'c'
insert into aaa select 4,'d'
insert into aaa select 5,'e'
create table bbb (id int, titile char(10))
insert into bbb select 1,'a'
insert into bbb select 2,'b'
insert into bbb select 2,'c'
insert into bbb select 3,'c'
insert into bbb select 3,'111'
insert into bbb select 3,'1'
insert into bbb select 4,'22'
insert into bbb select 4,'55'
insert into bbb select 5,'d'
insert into bbb select 5,'e'select * from bbb where id not in(select id from aaa where id between 3 and 5)
select * from bbb where titile not in(select titile from aaa where id between 3 and 5)
and rtrim(id)+rtrim(title) not in(select rtrim(id)+rtrim(title) from 表二 where id in(3,4,5))