----------表1-------
id cname
1 USDT
2 ETH
3 EOS
4 HC
5 USDX
6 QTUM
----------表2---------
id name cids
1 A 1,2,3
2 B 1
3 C
4 D
---------要求結果----------
1 A USDT,ETH,EOS
2 B USDT
3 C
4 D
uj5u.com熱心網友回復：
select
id,b.name,group_concat((select a.cname from a where id=substring_index(substring_index(b.cids,','
,bb.help_topic_id+1),',',-1) ) )
from
b
join
mysql.help_topic bb
on bb.help_topic_id < (length(b.cids) - length(replace(b.cids,',',''))+1) group by id
uj5u.com熱心網友回復：
先將逗號進行切割，再根據切割后的id查詢a表的cname,再用group_concat進行分組合并uj5u.com熱心網友回復：
SELECT A.id,A.name,A.cids,REPLACE(REPLACE(GROUP_CONCAT(CASE WHEN REPLACE(A.cids,B.id,B.cnname)
LIKE CONCAT('%',b.cnname,'%') THEN b.cnname ELSE ' ' END),' ,',''),', ','') AS cnname
FROM tb2 A
CROSS JOIN tb1 B
GROUP BY A.id
ORDER BY A.id,B.id;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/21525.html
標籤：MySQL
上一篇：一下操作能否簡化代碼？
下一篇：VS連接STK