***关于字符串查询语句***

Question

select a.jobno from a join b on a.id=b.id

Answer 1

错了一点
select b.jobno from a join b on a.id=b.id 

Answer 2

这样不对， 规则上A，B=B，A 个数和内容相同，次序可以不同

Answer 3

明白你的意思了，sql实现不了

Answer 4

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)

Answer 5

上面有问题，A表中ID数量不一定是2个

Answer 6

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 行受影响)