mysql查询你字符串是a合并_【MySQL 编程你也行】MySQL 表的模糊关联、字符串排序合并...

下面是帖子的内容：

有两个表，表A有字段proj_dept，字段值如下

proj_dept

2，3，4

2，4，5

3，4，6

表B为部门表，其中DEPT_ID跟proj_dept中内容关联

dept_id            dept_name

2                       部门2

3                       部门3

4                       部门4

5                       部门5

6                       部门6

现在想在查询A表的时候，实现A表的proj_dept字段按照如下方式显示

proj_dept

部门2，部门3，部门4

部门2，部门4，部门5

部门3，部门4，部门6

万分感谢

那么这个问题如何解决呢?

一、看到这个问题，觉得这个A表设计的很奇怪，为什么要把一堆的id用逗号分隔后存储，可能在程序上简单了，但是从设计上来说，有几个问题：

1、缺乏完整性约束，也就说不能保证A表的这个字符串中的id，一定是引用自B表的dept_id的。

2、如果A表的数据要修改要怎么办？

比如，增加一个id可能相对简单，但要删除其中一个id，要如何操作。

3、怎么和B表关联，当然这个也就是发帖人提出的问题，一看就晕了。

二、其实不仅仅是在MySQL中，在SQL Server中也有很多类似的问题，那怎么解决呢？ 其实方法都类似，可以这么写：

建表

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');

查询

select group_concat(dept_name order by dept_id) as dept_name

from

(

select *

from A,B

where concat(',',A.proj_dept,',') like concat('%,',cast(B.dept_id as char),',%')

)t

group by proj_dept;

实际效果：

mysql> select group_concat(dept_name order by dept_id) as dept_name

-> from

-> (

-> select *

-> from A,B

-> where concat(',',A.proj_dept,',') like concat('%,',cast(B.dept_id as char),',%')

-> )t

-> group by proj_dept;

+-------------------+

| dept_name |

+-------------------+

| 部门2,部门3,部门4 |

| 部门2,部门4,部门5 |

| 部门3,部门4,部门6 |

+-------------------+

3 rows in set (0.03 sec)

三：注释：

1、concat(',',A.proj_dept,',') like concat('%,',cast(B.dept_id as char),',%')

这句里涉及到了concat函数(用来连接字符串的函数)，cast函数(就是把int类型的dept_id字段，转化为char类型)。

本质上就是用like进行模糊匹配，就是这样 ‘,2,3,4,’ like '%,2,%' ，需要注意的是这里之所以要在前后加上逗号是为了进行准确的匹配，因为有可能是这种数据‘2,3,12’ like '%,2,%' ，那么2匹配2是正确的，但是12也能匹配2 ，因为12中也包含了2这个字符，所以就错了，现在加上逗号后就要匹配 ,2, 就不会有这个问题。

2、group_concat(dept_name order by dept_id)

匹配完成后，要好把匹配上的字符串合并起来，这里用的就是group_concat，其实就是 concat的加强版，能起到group时聚合数据的作用，另外order by可以实现在聚合数据时，先对数据进行排序 ，这里按照dept_id来排，就变成了这样：部门2,部门3,部门4 。

另一个方法：

select group_concat(dept_name) as dept_name

from

(

select a.*,substring_index(substring_index(a.proj_dept,',',b.help_topic_id+1),',',-1) as dept_id

from a join mysql.help_topic b

on b.help_topic_id < (length(a.proj_dept) - length(replace(a.proj_dept,',',''))+1 )

)a

inner join B on b.dept_id =a.dept_id

group by a.proj_dept;

mysql> select group_concat(dept_name) as dept_name

-> from

-> (

-> select a.*,substring_index(substring_index(a.proj_dept,',',b.help_topic_id+1),',',-1) as dept_id

-> from a join mysql.help_topic b

-> on b.help_topic_id < (length(a.proj_dept) - length(replace(a.proj_dept,',',''))+1 )

-> )a

-> inner join B on b.dept_id =a.dept_id

-> group by a.proj_dept;

+-------------------+

| dept_name |

+-------------------+

| 部门2,部门4,部门3 |

| 部门5,部门2,部门4 |

| 部门6,部门3,部门4 |

+-------------------+

3 rows in set (0.00 sec)