select jobno from b where id in (select id from a union all select substring(id,charindex(',',id)+1 ,20)+','+substring(id,0,charindex(',',id)) from a)
上面有问题,A表中ID数量不一定是2个
if OBJECT_ID('Tempdb..#a') is not null drop table #a if OBJECT_ID('Tempdb..#b') is not null drop table #b create table #a( aID nvarchar(100) null ) create table #b( jobno nvarchar(100) null, bID nvarchar(100) null ) insert into #a select '单杠挂衣,双杠挂衣' union all select '单杠挂衣,指定挂衣' insert into #b select 'OU2015012800001','双杠挂衣,单杠挂衣' union all select 'OU2015012800002','通试箱,指定提单箱' select b.jobno,a.aID,b.bID from #a a join #b b on a.aID=b.bID or (PARSENAME(replace(a.aID,',','.'),1)=PARSENAME(replace(b.bID,',','.'),2) and PARSENAME(replace(a.aID,',','.'),2)=PARSENAME(replace(b.bID,',','.'),1) ) --------------------------------------- (2 行受影响)(2 行受影响) jobno aID bID ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- OU2015012800001 单杠挂衣,双杠挂衣 双杠挂衣,单杠挂衣(1 行受影响)
错了一点
select b.jobno from a join b on a.id=b.id
select substring(id,charindex(',',id)+1 ,20)+','+substring(id,0,charindex(',',id)) from a)
if OBJECT_ID('Tempdb..#b') is not null drop table #b
create table #a(
aID nvarchar(100) null
)
create table #b(
jobno nvarchar(100) null,
bID nvarchar(100) null
)
insert into #a
select '单杠挂衣,双杠挂衣' union all
select '单杠挂衣,指定挂衣'
insert into #b
select 'OU2015012800001','双杠挂衣,单杠挂衣' union all
select 'OU2015012800002','通试箱,指定提单箱' select b.jobno,a.aID,b.bID from #a a join #b b
on a.aID=b.bID
or (PARSENAME(replace(a.aID,',','.'),1)=PARSENAME(replace(b.bID,',','.'),2)
and PARSENAME(replace(a.aID,',','.'),2)=PARSENAME(replace(b.bID,',','.'),1)
)
---------------------------------------
(2 行受影响)(2 行受影响)
jobno aID bID
---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
OU2015012800001 单杠挂衣,双杠挂衣 双杠挂衣,单杠挂衣(1 行受影响)