在数据库的连接(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,%】将不再成立。查询结果就成为:
可以看到,这正是符合期望的查询结果。