id1,id2是同一个表的主键,我重写一下: select * from table1 where cast( table1.id1 as nvarchar(20)) & cast( table1.id2 as nvarchar(20)) in (select cast( table1.id1 as nvarchar(20)) & cast( table1.id2 as nvarchar(20) from table1 where ...)
用exists 代替IN,用not exists 代替not in会快一点.
select * from table1 join table2 on table.id1 = table2.id1 and table1.id2 = table2.id2 如果只要table1的字段,就用table1的字段代替*
create table T1 (Key1 int, Key2 int, Key3 int)create table T2 (Key1 int, Key2 int, Key3 int) insert into T1 values(1,2,3)insert into T1 values(4,5,6) insert into T1 values(7,8,9)insert into T2 values(1,2,3)insert into T2 values(4,5,6) insert into T2 values(7,8,8)select T1.* from T1 inner join T2 on T1.Key1=T2.Key1 and T1.Key2=T2.Key2 and T1.Key3=T2.Key3 go drop table T1 go drop table T2
select * from table1 where cast( table1.id1 as nvarchar(20)) & cast( table1.id2 as nvarchar(20)) in
(select cast( table1.id1 as nvarchar(20)) & cast( table1.id2 as nvarchar(20) from table1 where ...)
如果只要table1的字段,就用table1的字段代替*
(Key1 int,
Key2 int,
Key3 int)create table T2
(Key1 int,
Key2 int,
Key3 int)
insert into T1
values(1,2,3)insert into T1
values(4,5,6)
insert into T1
values(7,8,9)insert into T2
values(1,2,3)insert into T2
values(4,5,6)
insert into T2
values(7,8,8)select T1.* from T1 inner join T2 on T1.Key1=T2.Key1 and T1.Key2=T2.Key2
and T1.Key3=T2.Key3
go
drop table T1
go
drop table T2