需求:
A表
id L_id a b c1 11 1 2 3
2 11 2 3 4
3 12 5 6 7
B表
L_id name text(存A表字段名,数量不定)
11 A a
11 B b
11 C c
12 B b
12 C c
根据B表的数据取A表的字段值,并且拼一个json
C表
id text
1 {"A":"1","B":"2","C":"3"}
2 {"A":"2","B":"3","C":"4"}
3 {"B":"6","C":"7"}
拼写要查的列
DROP TABLE IF EXISTS B_GROUP;
create table B_GROUP
(
L_ID varchar(19),
CP_TEXT text
);
insert into B_GROUP(
select L_ID,
concat('REPLACE(concat(''{'',',
group_concat(concat('''\\"',NAME ,'\\":\\"'',', 'ifnull(',TEXT,','''')', ',''\\",'''
) ) ,',''}''),',''',}'',','''}'')')
from B表 group by LABLES_ID
);
结果:
11 REPLACE(concat('{','\"A\":\"',ifnull(a,''),'\",','\"B\":\"',ifnull(b,''),'\",','\"C\":\"',ifnull(c,''),'\",','}'),',}','}')12 REPLACE(concat('{','\"B\":\"',ifnull(b,''),'\",','\"C\":\"',ifnull(c,''),'\",','}'),',}','}')
DROP TABLE IF EXISTS C_C;
create table C_C
(
ID varchar(19),
CP_TEXT text
);
drop procedure if exists C_C_TEXT;
CREATE PROCEDURE C_C_TEXT()
BEGIN
DECLARE Done1 INT DEFAULT 0;
DECLARE L_ID_C varchar(19);
DECLARE CP_TEXT_C text;
DECLARE vstrSql VARCHAR(3000) DEFAULT '';
#声明游标b_result
DECLARE b_result CURSOR FOR select L_ID,CP_TEXT from B_GROUP;
#异常处理
DECLARE CONTINUE HANDLER FOR NOT FOUND SET Done1 = 1;
OPEN b_result;
# 逐个取出当前记录字段的值
FETCH NEXT FROM b_result INTO L_ID_C, CP_TEXT_C;
WHILE Done1 <> 1 DO
SET vstrSql = CONCAT('select ID, ', CP_TEXT_C,' as CP_TEXT from A_A p where P.L_ID=<span style="color:#ff0000;font-weight: bold;">''</span><span style="color:#333333;">',L_ID_C,'</span><span style="color:#ff0000;font-weight: bold;">''</span> ');
SET @sql = CONCAT('insert into C_C(',vstrSql,');');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET vstrSql='';
FETCH NEXT FROM b_result INTO L_ID_C, CP_TEXT_C;
END WHILE;
#关闭游标
CLOSE b_result;
END;
delimiter ;
CALL C_C_TEXT();
select * from C_C;
结果:
1 {"A":"1","B":"2","C":"3"}
2 {"A":"2","B":"3","C":"4"}
3 {"B":"6","C":"7"}
作为sql只会增删改查的少年,调通这样的sql花了不少时间。
在这过程中,先是漏了where后面的引号,导致where 条件失效,执行了次数是:A表数量 乘以 B_GROUP 数量,害的我以为是死循环,一直在找光标的原因,后来把sql拆分出 vstrsql 才发现。
之后一直纠结于B_GROUP的参数拼写,要下一次查询直接得到需要的格式,煞费苦心。