在问答里面看到得两道MySQL题

问题：https://ask.csdn.net/questions/7850925

建表和插入数据：

第一题：
create table test.spgl (
id int,
value1 varchar(10),
value2 varchar(10)
);
insert into test.spgl values (1,'a1','b1'), (1,'a2','b2'),(2,'a1','b2');
insert into test.spgl (id,value1) values (2,'a2');

第二题：
create table test.A (
id int,
name varchar(10),
city varchar(50)
);
insert into test.A values (1,'a','[1a1,1a2,1a3]'),(2,'b','[2b1,2b2]'),(3,'c','[3c1]');

create table test.B (
city varchar(10),
score int
);
insert into test.B values ('1a1',10),('1a2',15),('1a3',5),('2b1',1),('2b2',2),('3c1',3);

create table test.C (
score varchar(50),
`leave` varchar(10)
);
insert into test.C values ('0~10','D'),('11~15','C'),('16~20','B'),('21~100','A');

解决方案：

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

第二题：
/*第一步：去掉"[]"*/
select *,SUBSTRING(city, LOCATE('[', city)+1,LOCATE(']', city)-2) from test.A;
/*第二步：把city拆分开*/
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 )
ORDER BY a.id;
/*第三步：关联B表*/
select * 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;
/*第四步：以name分组求和score*/
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;
/*第五步：分数拆分成整数型*/
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;
/*第六步：根据score获得leave*/
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;