left join 逗号分隔字段查询 mysql和postgresql

た入场券 2021-06-10 20:38 676阅读 0赞

商品表 goods 有学校字段 school\_ids

学校字段 school\_ids 中保存学校表 school 的id，如果商品包含多个学校则多个id使用逗号分隔保存

school:  
![在这里插入图片描述][20200306105835822.png]  
goods:  
![在这里插入图片描述][20200306105856691.png]

最终查询：两个表关联，goods表中字段中school\_ids 逗号隔开和school表中的id关联

MySQL版:

SELECT g.id,g.school_ids,GROUP_CONCAT(s.locality_name) 
    FROM goods g LEFT JOIN school s ON FIND_IN_SET(s.id , g.school_ids)
    GROUP BY g.id
    ORDER BY g.id ASC

运行结果  
![在这里插入图片描述][20200306110501421.png]  
解释：  
concat\_ws()函数  
该函数将分组中的非NULL值串联起来称为一个字符串。如果分组中的某个值为NULL，而串联的结果为NULL。  
自己理解：将多个字符串连接成一个字符串，中间用逗号隔开

语法`GROUP_CONCAT(expr)`  
例子：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FkbWluUHdk_size_16_color_FFFFFF_t_70]

FIND\_IN\_SET函数  
语法：`FIND_IN_SET(str,strlist)`

定义：

1.  假如字符串str在由N子链组成的字符串列表strlist中，则返回值的范围在1到N之间。
2.  一个字符串列表就是一个由一些被‘,’符号分开的自链组成的字符串。
3.  如果第一个参数是一个常数字符串，而第二个是typeSET列，则FIND\_IN\_SET()函数被优化，使用比特计算。
4.  如果str不在strlist或strlist为空字符串，则返回值为0。
5.  如任意一个参数为NULL，则返回值为NULL。这个函数在第一个参数包含一个逗号(‘,’)时将无法正常运行。  
    strlist：一个由英文逗号“,”链接的字符串，例如：“a,b,c,d”，该字符串形式上类似于SET类型的值被逗号给链接起来。  
    示例：SELECT FIND\_IN\_SET(‘b’,‘a,b,c,d’); //返回值为2，即第2个值  
    个人理解：比如一个字段中信息用逗号隔开，在关联表时，他可以拆分逗号一个一个去和被关联表的字段比较

PostgreSQL版:

SELECT g.id,g.school_ids,string_agg(s.locality_name,',')
    FROM goods g LEFT JOIN school s ON s.id = ANY(STRING_TO_ARRAY(g.school_ids, ',')) 
    GROUP BY g.id,g.school_ids
    ORDER BY g.id ASC

运行结果  
![在这里插入图片描述][20200306110426616.png]

解释  
string\_agg函数  
语法`string_agg(字段,',')`  
定义和上面的mysql相似`concat_ws`

ANY函数  
语法:`ANY(STRING_TO_ARRAY(字段, ','))`  
定义和上面的mysql相似`FIND_IN_SET`

[20200306105835822.png]: /images/20210522/2c7e30bd226d445286f27d631b13943c.png
[20200306105856691.png]: /images/20210522/3a52b90c1cec4a78b73bd807657eb25d.png
[20200306110501421.png]: /images/20210522/3caae1efa015469d86826f5460a0c26a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FkbWluUHdk_size_16_color_FFFFFF_t_70]: /images/20210522/8515911f68ac4476b53bc8ed55a30ba3.png
[20200306110426616.png]: /images/20210522/40e265862ac049e794c8235d1b815d3f.png