mysql分组,行转列

 

 

思路:

 

 SQL:

select tt.month as '月份',sum(tt.rp) as '实体商品',sum(tt.vp) as '虚拟商品' from (
        select month,
         sum(IF(goods_type =1,amount,0.00)) as 'rp',
         sum(IF(goods_type =2,amount,0.00)) as 'vp'
        from (
                select id,
                    substring(date_format(From_unixtime(sold_time),'%Y-%m-%d'),1,7) as month,
                    sum(amount) as amount,
                    goods_type
                from goods_count 
                group by substring(date_format(From_unixtime(sold_time),'%Y-%m-%d'),1,7),goods_type
        ) t
        group by month,goods_type ORDER BY month
) tt group BY tt.month

 

 结果图: