有时为了数据库简洁,存放数据的时候,某一字段采用逗号隔开的形式进行存储。
一般情况这个字段都应该具有如下几个共性。
- 被分割的字段一定是有限而且数量较少的,我们不可能在一个字符串中存储无限多个字符
- 这个字段所属的表与这个字段关联的表,一定是一对多的关系
下面举例说明:
原表:
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
1
2
3
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
目的是连接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 李一
11,2,3 刘二
11,2,3 张三
22 刘二
34,5 李四
34,5 王五
44 李四
52,3,4 刘二
52,3,4 张三
52,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