表1:
SG01CYC0001
SG01CYG0001
表2:
SG01CYC0001 1
SG01CYG0001 1
SG01ISS0001 1
SG01PIX0001 1
SG01PIX0002 1
SG01PIX0003 1
SG01PIX0004 1
查询想得到:SG01CYC0001 1
SG01CYG0001 1
SG01ISS0001 0
SG01PIX0001 0
SG01PIX0002 0
SG01PIX0003 0
SG01PIX0004 0
在表1的保持1,不在表1的换成0
SG01CYC0001
SG01CYG0001
表2:
SG01CYC0001 1
SG01CYG0001 1
SG01ISS0001 1
SG01PIX0001 1
SG01PIX0002 1
SG01PIX0003 1
SG01PIX0004 1
查询想得到:SG01CYC0001 1
SG01CYG0001 1
SG01ISS0001 0
SG01PIX0001 0
SG01PIX0002 0
SG01PIX0003 0
SG01PIX0004 0
在表1的保持1,不在表1的换成0
insert into 表1
select 'SG01CYC0001'
union all select 'SG01CYG0001'
create table 表2(code varchar(50),m int)
insert into 表2(code,m)
select 'SG01CYC0001',1
union all select 'SG01CYG0001',1
union all select 'SG01ISS0001',1
union all select 'SG01PIX0001',1
union all select 'SG01PIX0002',1
union all select 'SG01PIX0003',1
union all select 'SG01PIX0004',1select code,case when exists(select 1 from 表1 where 表1.code=表2.code) then 1 else 0 end from 表2
left join t1 b on a.dev=b.dev
set status=case when exists(select 1 from 表1 where 表1.dev=表2.dev) then 1 else 0 end
from 表2
create table T1(col varchar(30))
insert T1 select 'SG01CYC0001'
union all select 'SG01CYG0001'create table T2(col varchar(30), col2 int)
insert into T2 select 'SG01CYC0001', 1
union all select 'SG01CYG0001', 1
union all select 'SG01ISS0001', 1
union all select 'SG01PIX0001', 1
union all select 'SG01PIX0002', 1
union all select 'SG01PIX0003', 1
union all select 'SG01PIX0004', 1select T2.col, col2=case when T1.col is null then 0 else 1 end
from T2
left join T1 on T2.col=T1.coldrop table T1
drop table T2