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

[bigmingming]

select '单杠挂衣,双杠挂衣' as id into a
union all
select '单杠挂衣,指定挂衣'
.....
....


select 'OU2015012800001' as jobno,'双杠挂衣,单杠挂衣' as id into b union all
select 'OU2015012800002','通试箱,指定提单箱'
...
...



想得到结果jobno:OU2015012800001

A表ID与B表ID对比，个数相等，字符相同的JOBNO

[bigmingming]

有其它办法吗

[Just_Go_Yo]

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)

[bigmingming]

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

[IEEE_China]

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 a.jobno from a join b on a.id=b.id

[俺是大菠萝]

引用 1 楼 u013226693 的回复:

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

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

[bigmingming]

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

[俺是大菠萝]

引用 3 楼 bigmingming 的回复:

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

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