mysql使用GROUP_CONCAT和left join进行联合多表查询
(处理多表查询时,某表数据为空null值处理以及结果集多条数据展示在一行的处理)
文章是按照需求分析以及跳坑顺序情景再现的顺序来写的.
如果你不是小白,你可以直接跳到最后看一下代码看个思路.
有三个表,分别是
buyer表,记录用户的信息
buyerstags表,记录用户有哪些标签,标签是谁赋予的.是一个用户与标签的关系表.通常不用来记录需要显示给UI的数据
tagforbuyer表,直接理解就是标签表,里面是每个标签的信息.由于这个标签是为了买家建立的,所以取名比较长,在本例中,可以直接理解为tag
现实应用中,我们常常需要一句话把需要的简单信息查询出来,
所以我们需要联合查询
其中,联合查询,可以直接使用 from多表的方式 也可以使用 left join的方式
下面通过实际操作 来看一下具体的哪些功能需要哪些语法
首先 需求是这样的:
查到如下显示效果的信息:
要查询的字段有
买家id,买家昵称,所拥有的标签名称 (注意 这里的buyerstags只是一个关系表.要查询的时候需要借助此表,但是不展示他的字段)
并且,一个买家有多个标签的时候,我们使用 "标签1,标签2,标签3"这样的 使用一行数据,标签字段使用逗号分隔的方式展示出来
然后,我们想到的是使用多表联合基础查询
来看一下 这个5908买家的所有标签
但是我们想要 家属,微信一般顾客 这样的一行数据 就需要使用的group_concat
运行起来是这样的
但是这样就有一个问题 那就是 如果你where=条件的时候会返回一条数据,那么where in的时候 就也会合并到一行 如下:
可以看到,显然不是我们要的结果,因为我们需要把这三个买家的信息和他们的标签分别展示出来.那我们就需要加一个group by,不然的话,group_concat默认是把所有结果表述成一行
好了 看起来好像没有问题了. 不过注意 这里没有5910的信息呀? 为什么呢 因为 在where条件中,这种联表查询的时候 有一个条件的不符合,就不会返回结果.
这条语句中,当在检索买家的标签关系表 tagforbuyer的时候,没有关于5910的标签关联信息,所以,匹配是空的,就没有返回这一条结果.
后来 我摸索着,在where条件中 使用ifnull, if 在里面子集select等等多种多种方式(因为我也是半路出家) 最后都失败了.
然后重新摸起书,上谷歌翻资料 想到了left join
这个left join的特点是,当你查询的时候,如果前面的表有数据,返回前面查询表的数据,后面的如果匹配不上,就是null 这好像就是我们要的结果了.
于是改用left join
这里我们又要注意了.tagid,tagname的关系, 比如结果第一行 id就一个,name是2个 显然id是错误的,因为没有用group_concat
改一下:
那这样就对了.
由于我们不需要使用tagid字段 所以可以给他去掉,也是不影响结果的,只要关系语句中关系表述正确即可
最后,我们在where条件中,加入我们想要搜索的买家的id集合
结果是正确的.
但是现实使用中,我们可能还需要使用关联的拼音检索表,根据电话查询等场景,所以 我们还需要在 where buyer.buyerid in 里面变成我们自己的查询语句 比如
或者增加根据电话查询(如果不在应用程序判断给定的内容是字符串还是数字的话,直接加条件,也不会太影响性能)
如果想把数字和字母的检索分开的话,可以在查询数据库之前,应用程序判断查询的条件,比如判断了是数字检索 那么就查询mobile字段
这样查询就简单了.
这两种查询,返回的结果如果放在where in语句中,就相当于是 1,2,3,4,5 这样的数据
所以增加了拼音和电话检索的语句是这样的
圈选位置就是代替了之前的 5908,5909,5910
查询示例:
