基于逗号分隔的字符串字段值实现表间连接

在数据库的连接（JOIN）查询中，一般都是基于两表之间的外键关系，比如基于职员表（Emp）与部门表（Dep）查询职员所属部门，SQL语句大致类似这样：

select E.Name, D.DepName

from Emp E inner join Dep D

 on E.DepId=D.ID -- 此子句就是外键关联的JOIN条件

在某些数据库表设计中，可能存在一种特殊的字段，字段值是一个逗号分隔的字符串，逗号之间的内容，类似一个外键值。

比如一个项目（Project）表：

ID	ProjectName	Team
1	Project001	1,2
2	Project002	12,21
3	Project003	2
4	Project004	2,22,23

其中的Team字段值，就是用逗号分隔的多个员工ID串接而成的字符串。

员工信息（Emp）如下表：

ID	EmpName
1	张三
2	李四
12	王五
21	赵六
22	陈琦
23	刘刚

如果想要对项目表与员工表做关联查询，将项目成员姓名分列出来，第一感的查询语句如下：

select 
P.*
,E.id EmpId,E.EmpName
from Project P inner join Emp E
on P.Team like ('%' + CONVERT(nvarchar,E.id) + '%')

其中，ON子句的条件，含义是Team字段中包含员工ID。但是查询结果如下：

可以看到，Project002这个项目的成员列表中，本应只有12和21这两个员工，但是查询结果中却把1和2这两个员工ID也查出来了。原因就是 12,21 like '%1%' 和 12,21 like '%2%' 这两个条件表达式的结果都是True。

为了防止这种误查，可将SQL改写为：

select 
P.*
,E.id EmpId,E.EmpName
from Project P inner join Emp E
on (','+P.Team+',') like ('%,' + CONVERT(nvarchar,E.id) + ',%')

改写后的ON子句条件，是先将项目表的Team字段值前后都加上逗号，比如：【12,21】 变成 【,12,21,】，然后匹配条件变成【%,员工ID,%】。这样，【,12,21,】 like 【%,1,%】将不再成立。查询结果就成为：

可以看到，这正是符合期望的查询结果。