表A1:id brand value
1 BM1 1.2
2 BM2 2.6
3 BM3 1.8
4 DZ1 2.1
5 DZ2 3.4
6 DZ2 3.2
7 BM2 2.8
表B1: id brands
1 BM1,BM2
2 BM2
3 BM1,BM3,DZ2
4 DZ2
5 DZ2
现在需要对B1表中brands进行value值统计
若:id=1 BM1=1.2 value = 1.2+2.6+2.8 = 6.6
BM2=2.6
BM2=2.8
id=2 BM2=2.6 value = 2.6+2.8 = 5.4
BM2=2.8 id=3 BM1=1.2 value = 1.2+1.8+3.4+3.2 = 10.6
BM3=1.8
DZ2=3.4
DZ2=3.2
......
1 BM1 1.2
2 BM2 2.6
3 BM3 1.8
4 DZ1 2.1
5 DZ2 3.4
6 DZ2 3.2
7 BM2 2.8
表B1: id brands
1 BM1,BM2
2 BM2
3 BM1,BM3,DZ2
4 DZ2
5 DZ2
现在需要对B1表中brands进行value值统计
若:id=1 BM1=1.2 value = 1.2+2.6+2.8 = 6.6
BM2=2.6
BM2=2.8
id=2 BM2=2.6 value = 2.6+2.8 = 5.4
BM2=2.8 id=3 BM1=1.2 value = 1.2+1.8+3.4+3.2 = 10.6
BM3=1.8
DZ2=3.4
DZ2=3.2
......
或者用什么更好的办法做出来,大家给点建议。
group by a.idid=3 value =9.6
select a.id,sum(b.value) from bb1 a left join ba1 b on
FIND_IN_SET(b.brand,a.brands)>0
group by a.id
from B1 b
left join A1 a on b.brands like '%'||a.brand||'%'
group by b.id
select b.id,b.brands,sum(a.value) value
from B1 b
left join A1 a on ',' || b.brands || ',' like '%,'||a.brand||',%'
group by b.id
insert into A1 values(1,'BM1',1.2);
insert into A1 values(1,'BM2',2.6);
insert into A1 values(1,'BM2',2.8);create table B1(id int,brands varchar(100));
insert into B1 values(1,'BM1,BM2');select *,
(select sum(value) from A1 where brand = any (string_to_array(B1.brands,','))) as sum_value
from B1;--result----------------- id | brands | sum_value
----+---------+-----------
1 | BM1,BM2 | 6.60
position(b.brand in a.brands)>0
group by a.id
from B1 b join A1 a
on a.brand = any (string_to_array(brands,','))
group by b.id;