****查询相同字段的记录*****

[bigmingming]

表A
id name
00000004,00000005，00000008 单杠,双杠，EE
00000006,00000007 CC,DD


表B
jobno id name
OU2015012800001 00000008 AA
OU2015012800001 00000009 BB
OU2015012800002 00000004 单杠
OU2015012800002 00000005 双杠
OU2015012800002 00000008 EE
OU2015012800003 00000007 DD


得到jobno--OU2015012800002

想从表B中找到ID号完全等于表A中ID的JOBNO号

表A记录一组ID及对应的说明，从表B中找到ID号完全等于表A中ID的JOBNO号

[bigmingming]

print charindex('A,B','B,A') print charindex('0,1','1,0') 返回都是0

[Tiger_Zhao]

例子数据中b.id没有逗号！

[Ekun_sky]

with a(id,name) as
(select '00000004,00000005,00000008','单杠,双杠,EE'
 union all
 select '00000006,00000007','CC,DD')
 ,b(jobno,id,name) as
 (select 'OU2015012800001','00000008','AA' union all
  select 'OU2015012800001','00000009','BB' union all
  select 'OU2015012800002','00000004','单杠' union all
  select 'OU2015012800002','00000005','双杠' union all
  select 'OU2015012800002','00000008','EE' union all
  select 'OU2015012800003','00000007','DD')
  
  select d.jobno from 
  (select jobno,COUNT(bid) as 个数  from 
  (select a.id as aid,b.jobno,b.id as bid
  from a,b where charindex(','+b.id+',',','+a.id+',')<>0
  group by a.id,b.jobno,b.id) as c group by c.jobno) as d
  inner join (select *,LEN(id)-LEN(replace(ID,',',''))+1 as 个数 from a)
  as e on d.个数=e.个数
  
  /*
      jobno
  ---------------
  OU2015012800002
  ----------------
  */
  

[Ekun_sky]

答案错误，请忽略！汗

[bigmingming]

表A id name 00000004,00000005,00000008 单杠,双杠，EE 00000006,00000007 CC,DD

[Ekun_sky]

with a(id,name) as
(select '00000004,00000005,00000008','单杠,双杠,EE'
 union all
 select '00000006,00000007','CC,DD')
 ,b(jobno,id,name) as
 (select 'OU2015012800001','00000008','AA' union all
  select 'OU2015012800001','00000009','BB' union all
  select 'OU2015012800002','00000004','单杠' union all
  select 'OU2015012800002','00000005','双杠' union all
  select 'OU2015012800002','00000008','EE' union all
  select 'OU2015012800003','00000007','DD')
  
  select jobno  from 
  (select aid,jobno,COUNT(*) as 个数   from 
  (select a.id as aid,b.jobno,b.id as bid from a,b
  where CHARINDEX(','+b.id+',',','+a.id+',')<>0
  group by a.id,b.jobno,b.id) as c group  by
  c.aid,c.jobno) as d  inner join 
  (select *,LEN(id)-LEN(replace(id,',',''))+1 as 个数
  from a) as e  on d.aid=e.id and d.个数=e.个数
  
  
  /*
      jobno
  ---------------
  OU2015012800002
  ----------------
  */

再试试

[bigmingming]

谢谢可以的，请问CHARINDEX(','+b.id+',',','+a.id+',')<>0 这样写什么意思

[无涯大者]

select Jobno from B where Id in (select Id from A) 

供参考，不知道是不是LZ想要的结果！

[Tiger_Zhao]

WITH t1 AS (
    SELECT jobno,
           COUNT(*) c -- 全体id数量
      FROM b
  GROUP BY jobno
)
,t2 AS (
    SELECT b.jobno,
           COUNT(*) c -- 匹配id数量
      FROM b
      JOIN a
        ON CHARINDEX(b.id,a.id)<>0
)
SELECT t1.jobno
  FROM t1
  JOIN t2
    ON t1.jobno = t2.jobno
 WHERE t1.c = t2.c

[Tiger_Zhao]

WITH t1 AS (
    SELECT jobno,
           COUNT(*) c -- 全体id数量
      FROM b
  GROUP BY jobno
)
,t2 AS (
    SELECT b.jobno,
           COUNT(*) c -- 匹配id数量
      FROM b
      JOIN a
        ON CHARINDEX(b.id,a.id)<>0
  GROUP BY b.jobno
)
SELECT t1.jobno
  FROM t1
  JOIN t2
    ON t1.jobno = t2.jobno
 WHERE t1.c = t2.c

[bigmingming]

CHARINDEX(b.id,a.id)<>0 有问题 因为例如 AA，BB 也应该等于BB，AA 内容相同，次序不同也认为相同

[Tiger_Zhao]

就是考虑次序不同才用的 CHARINDEX 啊！包含就行。

[bigmingming]

你可以试一下 '双杠,单杠' 和 '单杠,双杠' 认为是不同

[Tiger_Zhao]

id里哪有单杠,双杠？
给出测试数据来证明有问题，不要空口说白话。