问题：ask.csdn.net/questions/7…
建表和插入数据：
第一题： 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 ('010','D'),('1115','C'),('1620','B'),('21100','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,', score),substr(score,1,LOCATE('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)-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;
