-- -- 行列转换
SELECT a.verify_no,a.relation_ids, SUBSTRING_INDEX(SUBSTRING_INDEX(a.relation_ids,',',b.id),',',-1) AS resultNAME
FROM fc_corms_cost_verify a JOIN fc_add_canopy b ON b.id < (LENGTH(a.relation_ids) - LENGTH(REPLACE(a.relation_ids,',',''))+2)
where a.relation_ids like '%,%' and a.pay_no_arr is null ;
--
-- -- 去重
-- SELECT fccps.cost_pay_id,fccps.id, mm.verify_no,fccp.payment_document from
-- fc_corms_cost_pay_site as fccps,
-- (SELECT a.verify_no,a.relation_ids, SUBSTRING_INDEX(SUBSTRING_INDEX(a.relation_ids,',',b.id),',',-1) AS resultNAME FROM fc_corms_cost_verify a JOIN fc_add_canopy b ON b.id < (LENGTH(a.relation_ids) - LENGTH(REPLACE(a.relation_ids,',',''))+2)where a.relation_ids like '%,%' and a.pay_no_arr is null )
-- as mm ,
-- fc_corms_cost_pay as fccp
-- where fccps.id=mm.resultNAME and fccps.cost_pay_id = fccp.id GROUP BY mm.verify_no,fccp.payment_document;
--
--
-- -- 拼接结果集
-- SELECT kk.verify_no,GROUP_CONCAT(kk.payment_document)
-- from (SELECT fccps.cost_pay_id,fccps.id, mm.verify_no,fccp.payment_document from
-- fc_corms_cost_pay_site as fccps,
-- (SELECT a.verify_no,a.relation_ids, SUBSTRING_INDEX(SUBSTRING_INDEX(a.relation_ids,',',b.id),',',-1) AS resultNAME FROM fc_corms_cost_verify a JOIN fc_add_canopy b ON b.id < (LENGTH(a.relation_ids) - LENGTH(REPLACE(a.relation_ids,',',''))+2)where a.relation_ids like '%,%' and a.pay_no_arr is null )
-- as mm ,
-- fc_corms_cost_pay as fccp
-- where fccps.id=mm.resultNAME and fccps.cost_pay_id = fccp.id GROUP BY mm.verify_no,fccp.payment_document) as kk GROUP BY kk.verify_no;
--
--
-- -- charu
-- INSERT INTO test_ref_temp (verify_code, payno_arry)
-- SELECT kk.verify_no,GROUP_CONCAT(kk.payment_document)
-- from (SELECT fccps.cost_pay_id,fccps.id, mm.verify_no,fccp.payment_document from
-- fc_corms_cost_pay_site as fccps,
-- (SELECT a.verify_no,a.relation_ids, SUBSTRING_INDEX(SUBSTRING_INDEX(a.relation_ids,',',b.id),',',-1) AS resultNAME FROM fc_corms_cost_verify a JOIN fc_add_canopy b ON b.id < (LENGTH(a.relation_ids) - LENGTH(REPLACE(a.relation_ids,',',''))+2)where a.relation_ids like '%,%' and a.pay_no_arr is null )
-- as mm ,
-- fc_corms_cost_pay as fccp
-- where fccps.id=mm.resultNAME and fccps.cost_pay_id = fccp.id GROUP BY mm.verify_no,fccp.payment_document) as kk GROUP BY kk.verify_no;
--
SELECT verify_code,payno_arry,GROUP_CONCAT(payno_arry) from test_ref_temp GROUP BY verify_code
-- UPDATE fc_corms_cost_verify as a, test_ref_temp as b set a.pay_no_arr = b.payno_arry where a.verify_no =b.verify_code and a.pay_no_arr is null and a.relation_ids like '%,%';
-- SELECT * from fc_corms_cost_verify as a, test_ref_temp as b where a.verify_no =b.verify_code and a.pay_no_arr is null and a.relation_ids like '%,%';
SELECT * from fc_corms_cost_verify as a where a.pay_no_arr is null and a.relation_ids like '%,%';
-- INSERT INTO test_ref_temp (verify_code, payno_arry)
-- SELECT kk.verify_no,GROUP_CONCAT(kk.payment_document)
-- from (SELECT fccps.other_fee_apply_id,fccps.id, mm.verify_no,fccp.pay_no as payment_document from
-- fc_corms_other_fee_apply_rent_money as fccps,
-- (SELECT a.verify_no,a.relation_ids, SUBSTRING_INDEX(SUBSTRING_INDEX(a.relation_ids,',',b.id),',',-1) AS resultNAME FROM fc_corms_cost_verify a JOIN fc_add_canopy b ON b.id < (LENGTH(a.relation_ids) - LENGTH(REPLACE(a.relation_ids,',',''))+2)where a.relation_ids like '%,%' and a.pay_no_arr is null )
-- as mm ,
-- fc_corms_other_fee_apply as fccp
-- where fccps.id=mm.resultNAME and fccps.other_fee_apply_id = fccp.id GROUP BY mm.verify_no,fccp.pay_no) as kk GROUP BY kk.verify_no;
SELECT verify_code,GROUP_CONCAT(payno_arry) as payno_arry from test_ref_temp GROUP BY verify_code;