求解以下两个SQL的代码

Question

想知道以上两个问题的sql代码该如何续写！
想知道以上两个问题的sql代码该如何续写！

Answer 1

第一题：
select  id ,replace (group_concat(value1),',','') value1,replace (group_concat(value2),',','') value2
from A
group by id;

第二题：
select name,a.score,`leave` from (
select name,sum(score) score from (
select id,name,c_city,score from (SELECT a.id,a.name,a.city,
SUBSTRING_INDEX( SUBSTRING_INDEX( SUBSTRING(city, LOCATE('[', city)+1,LOCATE(']', city)-2), ',', b.help_topic_id + 1 ), ',',- 1 ) c_city
FROM test.A a
JOIN mysql.help_topic b ON b.help_topic_id < ( LENGTH( a.city ) - LENGTH( REPLACE ( a.city, ',', '' ) ) + 1 )) a 
left join test.B b 
on a.c_city = b.city ) a 
group by name
) a 
left join (select score,`leave`,cast(score_min as SIGNED ) score_min,cast(score_max as SIGNED) score_max from (
select *,LOCATE('~', score),substr(score,1,LOCATE('~', score)-1) score_min, substr(score,LOCATE('~', score)+1,length(score)) score_max
from test.C ) a) b 
on b.score_min <= a.score
and a.score < b.score_max
order by name;

详细步骤：https://blog.csdn.net/Allenzyg/article/details/128146788?spm=1001.2014.3001.5502