34,588
社区成员
发帖
与我相关
我的任务
分享
select a.testno from #b b
right join #a a on charindex(substring(a.testno,1,4),b.testno)>0 or
charindex(substring(a.testno,2,4),b.testno)>0
where b.testno is null
中的charindex(substring(a.testno,2,4),b.testno)>0应该是这个吧charindex(substring(a.testno,2,5),b.testno)>0
select a.TESTNO from a
where left(cast(a.TESTNO as varchar(5)),4) not exists(select left(cast(b.TESTNO as varchar(5)),4) from b)
select * from #b
create table #a(testno char(5))
insert into #a
select '23431' union
select '12346' union
select '66547' union
select '54515'
create table #b(testno char(5))
insert into #b
select '42346' union
select '66548' union
select '62498' union
select '52922'
select a.testno from #b b
right join #a a on charindex(substring(a.testno,1,4),b.testno)>0 or
charindex(substring(a.testno,2,4),b.testno)>0
where b.testno is null
select a.testno from #b b
right join #a a on charindex(substring(a.testno,1,4),b.testno)>0 or
charindex(substring(a.testno,2,4),b.testno)>0
where b.testno is null
SELECT TESTNO FROM A
WHERE NOT EXISTS (
SELECT TESTNO FROM B
WHERE LEFT(B.TESTNO,4)<>LEFT(A.TESTNO,4) AND RIGHT(B.TESTNO,4)<>RIGHT(A.TESTNO,4)
)
--> 测试数据: [a]
if object_id('[a]') is not null drop table [a]
create table [a] ([TESTNO] int)
insert into [a]
select 23431 union all
select 12346 union all
select 66547 union all
select 54515
--select * from [a]
--> 测试数据: [b]
if object_id('[b]') is not null drop table [b]
create table [b] ([TESTNO] int)
insert into [b]
select 42346 union all
select 66548 union all
select 62498 union all
select 52922
--select * from [b]
select *
from a
except
select a.*
from b join a on b.testno like '%'+LEFT(a.testno,4)+'%' or b.testno like '%'+right(a.testno,4)+'%'
23431
54515
select a.* from a where TESTNO not in(
select b.TESTNO from a,b
where left(cast(a.TESTNO as varchar(5)),4)=left(cast(b.TESTNO as varchar(5)),4)
)
干嘛要用循环遍历呢,你那表里会有数据插入的话,你循环的时候会锁表,插入不成功咋整嗫?或者说你循环的时候加了with nolock ,可以插入数据,但是会有脏读的哟