mysql 逗号分隔 计算,mysql:表字段值为多值用逗号隔开，计算其和

一、奇怪的表设计，奇怪的需求

最近遇到个问题就是由张表有个字段是多值的，每个值用逗号进行隔开。现在需要其和。注意：结合图看，不是1+2+3+4+5+6;1+2+3+4+5+7+7;1+2+3+4+5+7+8这种。而是1+1+1；2+2+2;....

首先来说这种表这样设计不安全，比如长度。完全可以再设计一张表当作这张表的扩展表,如：tb_ext(pk_id,fk_id,_index,_value).pk_id:主键,fk_id:外键,_index:坐标,_value:值。

计算出每行同坐标的值。如:1+1+1,因表的不完善导致这个需求变的很难。

二、思路

0.抱怨完，该做还得做。哎

1.将原表转换成如:

tb_demo(r_id,_index,_value).r_id:原表主键,_index:坐标,_value:值。

如何将源表转换成tb_demo。

1)分离出值

使用substring_index函数分离出值.substring_index不熟悉可以百度一下，这里不做赘述。

2)计算出有几个值

使用length,REPLACE函数计算出有几个值.length,REPLACE不熟悉可以百度一下，这里不做赘述。

思路有几个分割符就有有几个加一个的值。源长度-去掉分割符的长度 =  分割符的个数。

3)如何动态分离出值

使用join tb_index(_index) 如：

整合的sql为：

SELECT

com1,

id,

substring_index( substring_index( a.com1, ',', b.id ), ',', - 1 )

FROM

( SELECT '1,2,3,4,5,6' com1 UNION ALL SELECT '1,2,3,4,5,7,8' com1 UNION ALL SELECT '1,2,3,4,5,7,7' com1 ) a

JOIN (

SELECT 1 id UNION ALL

SELECT 2 id UNION ALL

SELECT 3 id UNION ALL

SELECT 4 id UNION ALL

SELECT 5 id UNION ALL

SELECT 6 id UNION ALL

SELECT 7 id UNION ALL

SELECT 8 id ) b ON b.id <= ( length( a.com1 ) - length( REPLACE ( a.com1, ',', '' )) + 1

) order by com1,id

2.在通过select _index,sum(_value)  from tb_demo group by _index.即可完成我们的需求。

select r_id,sum(_value)  from tb_demo group by r_id. 可以计算出每行的值如：1+2+3+4....

select id,sum(_value) from(

SELECT

com1,

id,

substring_index( substring_index( a.com1, ',', b.id ), ',', - 1 ) _value

FROM

( SELECT '1,2,3,4,5,6' com1 UNION ALL SELECT '1,2,3,4,5,7,8' com1 UNION ALL SELECT '1,2,3,4,5,7,7' com1 ) a

JOIN (

SELECT 1 id UNION ALL

SELECT 2 id UNION ALL

SELECT 3 id UNION ALL

SELECT 4 id UNION ALL

SELECT 5 id UNION ALL

SELECT 6 id UNION ALL

SELECT 7 id UNION ALL

SELECT 8 id ) b ON b.id <= ( length( a.com1 ) - length( REPLACE ( a.com1, ',', '' )) + 1

) order by com1,id) c GROUP BY id