+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| bh | n1 | n2 | n3 | g1 | g2 | g3 |
+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 001 | 苹果 | 苹果 | 西瓜 | 5 | 6 | 7 |
+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 002 | 生梨 | 西瓜 | 西瓜 | 1 | 4 | 1 |
+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
经过sql语句处理后变成
+-----------+-----------+-----------+-----------+
| bh | 苹果 | 生梨 | 西瓜 |
+-----------+-----------+-----------+-----------+
| 001 | 11 | 0 | 7 |
+-----------+-----------+-----------+-----------+
| 002 | 0 | 1 | 5 |
+-----------+-----------+-----------+-----------+ 请高手帮忙
| bh | n1 | n2 | n3 | g1 | g2 | g3 |
+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 001 | 苹果 | 苹果 | 西瓜 | 5 | 6 | 7 |
+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 002 | 生梨 | 西瓜 | 西瓜 | 1 | 4 | 1 |
+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
经过sql语句处理后变成
+-----------+-----------+-----------+-----------+
| bh | 苹果 | 生梨 | 西瓜 |
+-----------+-----------+-----------+-----------+
| 001 | 11 | 0 | 7 |
+-----------+-----------+-----------+-----------+
| 002 | 0 | 1 | 5 |
+-----------+-----------+-----------+-----------+ 请高手帮忙
sum(if(fname='苹果',qty,0) s 苹果,
sum(if(fname='生梨',qty,0) s 生梨,
sum(if(fname='西瓜',qty,0) s 西瓜
from (
select bh,n1 as fname,g1 as qty from t_36397
union all
select bh,n2 as fname,g2 as qty from t_36397
union all
select bh,n3 as fname,g3 as qty from t_36397
) t
group by bh
SELECT bh
,(Case n1 When '苹果' Then g1 Else 0 End)+(Case n2 When '苹果' Then g2 Else 0 End)+(Case n3 When '苹果' Then g3 Else 0 End) As '苹果'
,(Case n1 When '生梨' Then g1 Else 0 End)+(Case n2 When '生梨' Then g2 Else 0 End)+(Case n3 When '生梨' Then g3 Else 0 End) As '生梨'
,(Case n1 When '西瓜' Then g1 Else 0 End)+(Case n2 When '西瓜' Then g2 Else 0 End)+(Case n3 When '西瓜' Then g3 Else 0 End) As '西瓜'
FROM tb;
sum(if(n1='苹果',g1,0)+if(n2='苹果',g2,0)+if(n3='苹果',g3,0)) as 苹果,
sum(if(n1='生梨',g1,0)+if(n2='生梨',g2,0)+if(n3='生梨',g3,0)) as 生梨,
sum(if(n1='西瓜',g1,0)+if(n2='西瓜',g2,0)+if(n3='西瓜',g3,0)) as 西瓜
from
(
select '001' as bh,'苹果' as n1 ,'苹果' as n2,'西瓜' as n3,5 as g1,6 as g2,7 as g3
union all
select '002','生梨','西瓜','西瓜',1,4,1
) a
group by bh
order by bh
SELECT bh
,if(n1='苹果',g1,0)+if(n2='苹果',g2,0)+if(n3='苹果',g3,0) As '苹果'
,if(n1='生梨',g1,0)+if(n2='生梨',g2,0)+if(n3='生梨',g3,0) As '生梨'
,if(n1='西瓜',g1,0)+if(n2='西瓜',g2,0)+if(n3='西瓜',g3,0) As '西瓜'
FROM tb;