+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| bh | n1 | n2 | n3 | g1 | g2 | g3 |
+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 001 | 苹果 | 生梨 | 西瓜 | 5 | 6 | 7 |
+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 002 | 生梨 | 苹果 | 西瓜 | 3 | 4 | 1 |
+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
经过sql语句处理后变成
+-----------+-----------+-----------+
| 苹果 | 生梨 | 西瓜 |
+-----------+-----------+-----------+
| 8 | 9 | 8 |
+-----------+-----------+-----------+请高手帮忙
| bh | n1 | n2 | n3 | g1 | g2 | g3 |
+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 001 | 苹果 | 生梨 | 西瓜 | 5 | 6 | 7 |
+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 002 | 生梨 | 苹果 | 西瓜 | 3 | 4 | 1 |
+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
经过sql语句处理后变成
+-----------+-----------+-----------+
| 苹果 | 生梨 | 西瓜 |
+-----------+-----------+-----------+
| 8 | 9 | 8 |
+-----------+-----------+-----------+请高手帮忙
苹果怎么算得是8的呢?大概思路这样处理就可以了:select sum(case when n1='苹果' then g1 when n2='苹果' then g2 when n3='苹果' then g3 end) as 苹果,
sum(case when n1='生梨' then g1 when n2='生梨' then g2 when n3='生梨' then g3 end) as 生梨,
sum(case when n1='西瓜' then g1 when n2='西瓜' then g2 when n3='西瓜' then g3 end) as 西瓜
from
(
select '苹果' as n1 ,'生梨' as n2,'西瓜' as n3,5 as g1,6 as g2,7 as g3
union all
select '生梨','苹果','西瓜',3,4,1) a
果' then g3 end) as 苹果,
-> sum(case when n1='生梨' then g1 when n2='生梨' then g2 when n3='生梨' th
en g3 end) as 生梨,
-> sum(case when n1='西瓜' then g1 when n2='西瓜' then g2 when n3='西瓜' th
en g3 end) as 西瓜
-> from
-> (
-> select '苹果' as n1 ,'生梨' as n2,'西瓜' as n3,5 as g1,6 as g2,7 as g3
-> union all
-> select '生梨','苹果','西瓜',3,4,1) a
-> ;
+------+------+------+
| 苹果 | 生梨 | 西瓜 |
+------+------+------+
| 9 | 9 | 8 |
+------+------+------+
1 row in set (0.02 sec)mysql>
sum(if(fname='苹果',qty,0) s 苹果,
sum(if(fname='生梨',qty,0) s 生梨,
sum(if(fname='西瓜',qty,0) s 西瓜
from (
select hh,n1 as fname,g1 as qty from t_36397
union all
select hh,n2 as fname,g2 as qty from t_36397
union all
select hh,n3 as fname,g3 as qty from t_36397
) t