求sql 方法 - MS-SQL Server / 疑难问题

表A 
Field A1(Varchar) A2
record 1 1
record 2 1
record 3 1

表B
Field B1(Varchar) B2
record 1,2,5,6 1


要求，查找出 A1表中A1字段的值，在B表B1中，条件A2=B2

---

SQL code:
select a1 from a where exists(select 1 from b where charindex(','+ltrim(a.a1)+',',','+b1+',')>0 and a.a2=b2)

---

如何返回

---

for xml path()

---

record 这东西是字段内容还是多余的？

---

select A.* from A , B where A2=B2 and charindex(','+A1+',',','+B1+',')>0

---

SQL code:
select A1
from 表A a
where exists
(select B1
from 表B b
where b.B2 = a.A2
and charindex(',' + a.A1 + ',', ',' + b.B1 + ',') > 0)


1.exists:用来过滤记录的，相当于条件
2.CHARINDEX函数返回字符或者字符串在另一个字符串中的起始位置。不存在返回 0；
  CHARINDEX函数调用方法如下： 
  CHARINDEX ( expression1 , expression2 [ , start_location ] )

---

SQL code:
select A1
from 表A a
left join 表B b
on a.A2 = b.B2
where charindex(',' + a.A1 + ',', ',' + b.B1