MySQL 一表中字段内容是用逗号分隔的 另一表含有对应数据的两表联合查询

有时为了数据库简洁，存放数据的时候，某一字段采用逗号隔开的形式进行存储。

一般情况这个字段都应该具有如下几个共性。

• 被分割的字段一定是有限而且数量较少的，我们不可能在一个字符串中存储无限多个字符
• 这个字段所属的表与这个字段关联的表，一定是一对多的关系

下面举例说明：

原表：

leader表

id       name

1        李一

2        刘二

3        张三

4        李四

5        王五

 

staff表

id         leaderid

1          1,2,3

2           2

3          4,5

4          4

5          2,3,4

想要的结果是：

id         leaderid           领导姓名

1          1,2,3                 李一，刘二，张三

2           2                       刘二

3          4,5                    李四，王五

4          4                       李四

实现的SQL语句为：

    select staff.id,staff.leaderid,GROUP_CONCAT(leader.name) as '领导姓名'
    from staff JOIN leader
    ON FIND_IN_SET(leader.id,staff.leaderid)
    GROUP BY staff.id

解析：上述语句涉及四个关键知识点：

1、join   on 

目的是连接leader表和staff表。

join on 使用较为简单，详情参考 《join on 的用法》http://blog.csdn.net/qfljg/article/details/4383043

 

2、FIND_IN_SET(str,strlist) 

该函数用于判断 str 是否在 strlist 中，

如果是，作为条件，返回对应数据，

如果str不在strlist 或strlist 为空字符串，则返回值为 0 。

 

3、GROUP_CONCAT(leader.name)

group_concat()，官方:该函数返回带有来自一个组的连接的非NULL值的字符串结果。
通俗点理解：group_concat()会计算哪些行属于同一组，将属于同一组的列按照一定的拼接规则拼接好显示出来。

形象的来说就是把列里的内容转化到了行里。

如果不使用该函数，只使用：

select * from staff JOIN leader
ON FIND_IN_SET(leader.id,staff.leaderid)
ORDER BY staff.id

该例的结果会变成：

id         leaderid           领导姓名

1          1,2,3                 李一
1         1,2,3                 刘二
1          1,2,3                 张三
2         2                       刘二
3          4,5                    李四
3          4,5                    王五
4          4                       李四
5          2,3,4                  刘二
5          2,3,4                  张三
5          2,3,4                  李四

没有得到想要的合并效果。

 

4、group by

算不上核心知识点吧，比较简单，但是少了不行。作用是对指定的字段进行分组，聚合。

如果不加使用，只是用：

select staff.id,staff.leaderid,GROUP_CONCAT(leader.name) as'领导姓名'

from staff JOIN leader
ON FIND_IN_SET(leader.id,staff.leaderid)

该例结果会变成：

id         leaderid           领导姓名

1          1,2,3                 李一,刘二,刘二,刘二,张三,张三,李四,李四,李四,王五

即全部聚合在一块（因为前面使用了 GROUP_CONCAT(leader.name)函数）。

 

参考文献：

《在MySQL字段中使用逗号分隔符》http://www.cnblogs.com/hongfei/archive/2012/06/16/2552002.html

《MySQL 的 find_in_set函数的使用方法》http://1055592535.iteye.com/blog/1674734

《group_concat()函数总结》http://www.cnblogs.com/wangtao_20/archive/2011/02/23/1961860.html

《SQL中Group By的使用数》http://www.cnblogs.com/rainman/archive/2013/05/01/3053703.html