我有一个表,数据如下
XXCON_PART XXCON_OP XXCON_QTY_CHK XXCON_QTY_REJ QTY_AVG
D1301-028-295-5531 HA 18 4 0.77
D1301-028-295-5531 W3 9 0 1.00
D1301-028-295-5531 FA 19 1 0.94
D1301-028-295-5531 FB 22 4 0.81
1011404 FA 184 33 0.82
1011404 W1 197820 14 0.99
1011404 HA 76 7 0.90
1011404 W2 2700 18 0.99
1011404 W3 6507 16 0.99希望合并成以下形式
XXCON_PART HA W3 FA FB W1 W2
D1301-028-295-5531 18 4 0.77 9 0 1.00 19 1 0.94 22 4 0.81
1011404 76 7 0.90 6507 16 0.99 184 33 0.82 197820 14 0.99 2700 18 0.99
这个合并需要在mysql中实现,不在php里面实现。大家有什么好的建议没有。
XXCON_PART XXCON_OP XXCON_QTY_CHK XXCON_QTY_REJ QTY_AVG
D1301-028-295-5531 HA 18 4 0.77
D1301-028-295-5531 W3 9 0 1.00
D1301-028-295-5531 FA 19 1 0.94
D1301-028-295-5531 FB 22 4 0.81
1011404 FA 184 33 0.82
1011404 W1 197820 14 0.99
1011404 HA 76 7 0.90
1011404 W2 2700 18 0.99
1011404 W3 6507 16 0.99希望合并成以下形式
XXCON_PART HA W3 FA FB W1 W2
D1301-028-295-5531 18 4 0.77 9 0 1.00 19 1 0.94 22 4 0.81
1011404 76 7 0.90 6507 16 0.99 184 33 0.82 197820 14 0.99 2700 18 0.99
这个合并需要在mysql中实现,不在php里面实现。大家有什么好的建议没有。
解决方案 »
- 求mysql教程视频
- MySQL的索引的一些疑问
- SQL 有个关于 update 正则替换的问题请教!谢谢解答
- 探讨一下,分库设计的优缺点?
- MySQL &Java 中文处理问题
- 怎样查看mysql所有权限
- 在VC下怎么用ADO访问MYSQL数据库
- 我安装了mysql for win2000可是 怎么建库啊,不想想象中的access,sql server ,oracle那样简单?
- 坛上所有的中文问题的贴子都看完了,还是不解决问题:(((
- INSERT INTO pre_forum_post_tableid SET `pid`=''; #分表
- mysql每次都可以正常启动,今天突然无法启动了
- 随机问题
sum(if(XXCON_OP='HA',XXCON_QTY_CHK,0)) as HA,
Sum(if(XXCON_OP='W3',XXCON_QTY_CHK,0)) as W3,
sum(if(XXCON_OP='FA',XXCON_QTY_CHK,0)) as FA,
Sum(if(XXCON_OP='FB',XXCON_QTY_CHK,0)) as FB,
sum(if(XXCON_OP='W1',XXCON_QTY_CHK,0)) as W1,
Sum(if(XXCON_OP='W2',XXCON_QTY_CHK,0)) as W2
FROM 一个表
Group by XXCON_PART
from tb
group by XXCON_PART形式大概这样 页面解析吧D1301-028-295-5531 18,4,0.77,9,0,1.00,19,1,0.94,22,4,0.81
试试
select XXCON_PART,
sum(if(XXCON_OP='HA',XXCON_QTY_CHK,0)) as HA,
Sum(if(XXCON_OP='W3',XXCON_QTY_REJ,0)) as W3,
sum(if(XXCON_OP='FA',QTY_AVG,0)) as FA,
Sum(if(XXCON_OP='FB',XXCON_QTY_CHK,0)) as FB,
sum(if(XXCON_OP='W1',XXCON_QTY_CHK,0)) as W1,
Sum(if(XXCON_OP='W2',XXCON_QTY_CHK,0)) as W2
FROM tt Group by XXCON_PART