mysql表格合并

我有一个表，数据如下
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里面实现。大家有什么好的建议没有。

解决方案 »

  1.   

    select XXCON_PART,
    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
      

  2.   

    如果需要动态实现，则可以参考下贴中多种方法。http://blog.csdn.net/ACMAIN_CHM/archive/2009/06/19/4283943.aspx
      

  3.   

    select XXCON_PART,group_concat(concat(XXCON_QTY_CHK,XXCON_QTY_REJ,QTY_AVG))
    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
      

  4.   

    重新排版：
    试试
    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