哈哈哈,今天终于有时间进行小结整理下了
最近根据公司实际业务,要经常对业务表数据进行拆分
背景:
一张项目表,项目负责人存在多人的场景(表名:project_task)
现取出该项目表里头的项目多负责人项目,对应项目负责人id和名称
思路:
1、先判断出项目负责人为多人的场景,因一个id是36位,所以仅需过滤出该id长度大于36即可
SELECT responsible_leader_id FROM project_task WHERE LENGTH(responsible_leader_id)>36
2、逗号拆分,借助系统自带help_topic_id表
SELECT DISTINCT
(
substring_index( substring_index( a.responsible_leader_id, ',', b.help_topic_id + 1 ), ',',- 1 )) as id
FROM
( SELECT responsible_leader_id FROM project_task WHERE LENGTH(responsible_leader_id)>36 ) AS a
JOIN mysql.help_topic AS b ON b.help_topic_id <(char_length( a.responsible_leader_id ) - char_length(REPLACE ( a.responsible_leader_id, ',', '' ))+ 1)
该逻辑可参考地址:
http://www.lsjlt.com/news/301764.html
3、因为要取项目负责人名称,项目表不存在该字段,需去重关联用户表,最终语句如下:
SELECT t.id ,s.user_name FROM (SELECT DISTINCT
(
substring_index( substring_index( a.responsible_leader_id, ',', b.help_topic_id + 1 ), ',',- 1 )) as id
FROM
( SELECT responsible_leader_id FROM project_task WHERE LENGTH(responsible_leader_id)>36 ) AS a
JOIN mysql.help_topic AS b ON b.help_topic_id <(char_length( a.responsible_leader_id ) - char_length(REPLACE ( a.responsible_leader_id, ',', '' ))+ 1)) t
LEFT JOIN yijian_user_center.sys_user s
on t.id=s.id