SQL编程：模糊表关联不求人 ---> concat + like就能行

1、表的结构：​

create table A(proj_dept varchar(30));


create table B(dept_id int,dept_name varchar(30));


insert into A values('2,3,4'),('2,4,5'),('3,4,6');


insert into B values(2,'部门2'),(3,'部门3'),(4,'部门4'),(5,'部门5'),(6,'部门6');

 

mysql> select * from A;
+-----------+
| proj_dept |
+-----------+
| 2,3,4 |
| 2,4,5 |
| 3,4,6 |
+-----------+
3 rows in set (0.01 sec)

mysql> select * from B;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 2 | 部门2 |
| 3 | 部门3 |
| 4 | 部门4 |
| 5 | 部门5 |
| 6 | 部门6 |
+---------+-----------+
5 rows in set (0.02 sec)

2、问题：
A表中的proj_dept字段包含了多个B表中的，用逗号分隔的dept_id，那么要如何实现A表和B表的关联？

3、解决方法：

select * from A,B
where concat(',',A.proj_dept,',') like concat('%,',cast(B.dept_id as char),',%')
order by proj_dept,dept_id;

mysql> select * from A,B
    -> where concat(',',A.proj_dept,',') like concat('%,',cast(B.dept_id as char),',%')
    -> order by proj_dept,dept_id;
+-----------+---------+-----------+
| proj_dept | dept_id | dept_name |
+-----------+---------+-----------+
| 2,3,4 | 2 | 部门2 |
| 2,3,4 | 3 | 部门3 |
| 2,3,4 | 4 | 部门4 |
| 2,4,5 | 2 | 部门2 |
| 2,4,5 | 4 | 部门4 |
| 2,4,5 | 5 | 部门5 |
| 3,4,6 | 3 | 部门3 |
| 3,4,6 | 4 | 部门4 |
| 3,4,6 | 6 | 部门6 |
+-----------+---------+-----------+
9 rows in set (0.01 sec)