name zf amount
厂家测试 A 71
厂家测试 B 58
厂家测试 C 270
厂家测试 D 25
交友 B 5
交友 B 35怎么将上面的数据通过group by 转换成如下格式:
A B C D
厂家测试 71 58 270 25
交友 0 5 0 35
厂家测试 A 71
厂家测试 B 58
厂家测试 C 270
厂家测试 D 25
交友 B 5
交友 B 35怎么将上面的数据通过group by 转换成如下格式:
A B C D
厂家测试 71 58 270 25
交友 0 5 0 35
MySQL交叉表
+----------+------+--------+
| name | zf | amount |
+----------+------+--------+
| 厂家测试 | A | 71 |
| 厂家测试 | B | 58 |
| 厂家测试 | C | 270 |
| 厂家测试 | D | 25 |
| 交友 | B | 5 |
| 交友 | B | 35 |
+----------+------+--------+
6 rows in set (0.00 sec)mysql> select name,
-> sum(if(zf='A',amount,0)) as A,
-> sum(if(zf='B',amount,0)) as B,
-> sum(if(zf='D',amount,0)) as D,
-> sum(if(zf='D',amount,0)) as D
-> from t_xuyu000000
-> group by name;
+----------+------+------+------+------+
| name | A | B | D | D |
+----------+------+------+------+------+
| 厂家测试 | 71 | 58 | 25 | 25 |
| 交友 | 0 | 40 | 0 | 0 |
+----------+------+------+------+------+
2 rows in set (0.00 sec)mysql>MS SQL SERVER 中你可以用 case when 替换 IF
select `name`,sum(if(zf='A',amount,0)) as A,sum(if(zf='B',amount,0)) as B,sum(if(zf='D',amount,0)) as D,sum(if(zf='D',amount,0)) as D from tt
group by `name`;
select
name,
sum(case zf when 'A' then amount end) as 'A',
sum(case zf when 'B' then amount end) as 'B',
sum(case zf when 'C' then amount end) as 'C',
sum(case zf when 'D' then amount end) as 'D'
from tablename
group by name