数据表前置条件
| uid | tag_ids |
|---|
| 100001002 | (NULL) |
| 1 | (NULL) |
| 100001006 | (NULL) |
| 100001011 | ["2", "4", "11"] |
| 100001012 | ["2", "4", "7", "11"] |
| 100001013 | ["4", "5", "11", "24", "17", "19"] |
| 100001014 | ["2", "4", "11"] |
| 100001015 | ["4", "9", "11", "10"] |
| 100001016 | ["4", "11", "17"] |
| 100001017 | ["2", "4", "5", "7", "11"] |
| id | name | memo |
|---|
| 1 | 合作商 | |
| 2 | 代理商 | |
| 3 | 老用户 | |
| 4 | 奇迹30学员 | |
| 5 | 灵性之美 | |
| 6 | 三千面相 | |
SELECT xx.uid,tag_ids,GROUP_CONCAT(tag_title) AS 'user_tag_names' FROM
(
SELECT uid,tag_ids,SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(REPLACE(REPLACE(REPLACE(t1.tag_ids,' ',''),'[',''),']',''),'"',''),',',t2.help_topic_id + 1),',',-1) AS tag_id
FROM user_center.`project_users` t1 JOIN mysql.help_topic t2
WHERE t2.help_topic_id< (LENGTH(t1.tag_ids) - LENGTH(REPLACE(t1.tag_ids,',','')) + 1)
) xx
INNER JOIN (SELECT id,IF(memo='',`name`,memo) AS tag_title FROM user_center.`user_tag_types` ) ut ON ut.id=xx.tag_id
GROUP BY xx.uid
| uid | tag_ids | user_tag_names |
|---|
| 10000 | ["9", "10"] | 工作室成员,免费特权 |
| 100001000 | ["4", "11"] | 奇迹30学员,精品课 |
| 100001004 | ["4"] | 奇迹30学员 |
| 100001011 | ["2", "4", "11"] | 代理商,奇迹30学员,精品课 |
| 100001012 | ["2", "4", "7", "11"] | 奇迹30学员,灵商密码,精品课,代理商 |
| 100001013 | ["4", "5", "11", "24", "17", "19"] | 代理41,奇迹30学员,灵性之美,精品课,现场课到新代理商,代理21 |