- 站点信息
- 问题反馈
- 邮箱：[email protected]
- 吐槽一下
关注微信
©2020-2025 ioDraw All rights reserved,
Privacy Policy
一、真实环境
第一步：concat_ws()，把需要的四列合并成以逗号隔开的字符串，形成新的列corpName
SELECT L_PARK,RC_PM_UIDS ,RC_MC_UIDS,RC_MC_ADMIN,RC_PM_ADMIN,
group_concat(distinct(concat_ws(',',RC_PM_ADMIN,RC_MC_ADMIN,RC_PM_UIDS,RC_MC_UIDS)))
corpName FROM dev_lift WHERE 1=1 and id in (select id from dev_lift where
STATUS>0 and L_AREA=523) group by L_PARK
需求：现在要实现的结果就是把哪些id替换成对应人物的公司名，展示如下：
第二步：怎么实现
1.新建一个test_topic(随便取)表，添加死数据。从0开始
2.使用 SUBSTRING_INDEX函数跟test_topic表连接，语句如下
例子
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('1,3,21,5,9',',',topic_id+1),',',-1)
AS num FROM test_topic WHERE topic_id <
LENGTH('1,3,21,5,9')-LENGTH(REPLACE('1,3,21,5,9',',',''))+1
然后你把刚才那个语句套在你的 In()条件里就可以了，同时查询条件加
group_concat() 函数。
SELECT group_concat(name) from auth a left join corp c on a.corp=c.id where
a.id in ( SELECT
SUBSTRING_INDEX(SUBSTRING_INDEX(corpName,',',topic_id+1),',',-1) AS num FROM
corp_topic WHERE topic_id < LENGTH(corpName)-LENGTH(REPLACE(corpName,',',''))+1
)
由于我的数据有重复,需要去重分组等一些操作。
完整sql。你能套就套
SELECT L_PARK parkId, weibao,anbao,weibaojinli,wuyejinli, (SELECT
group_concat( DISTINCT c.name ) from auth a left join corp c on a.corp=c.id
where a.id in ( SELECT
SUBSTRING_INDEX(SUBSTRING_INDEX(corpName,',',topic_id+1),',',-1) AS num FROM
corp_topic WHERE topic_id < LENGTH(corpName)-LENGTH(REPLACE(corpName,',',''))+1
)) corpName FROM ( SELECT L_PARK,group_concat(distinct(RC_PM_UIDS))
anbao,group_concat(distinct(RC_MC_UIDS)) weibao,
group_concat(distinct(RC_MC_ADMIN))
weibaojinli,group_concat(distinct(RC_PM_ADMIN)) wuyejinli,
group_concat(distinct(concat_ws(',',RC_PM_ADMIN,RC_MC_ADMIN,RC_PM_UIDS,RC_MC_UIDS)))
corpName FROM dev_lift group by L_PARK )c