mysql数据行转列

2019独角兽企业重金招聘Python工程师标准>>>

在你找工作的经历当中，是否有面试官问过你：数据库行转列如何实现？ 
一个典型的例子如下： 
    有这样一个订单表（om_order）一条记录包含订单号、创建日期、订单总金额； 
让你统计不同年份对应各月份的销售情况，要求每一年的销售情况一行展示，效果如下： 


+--------+-------------+--------+----------+----------+----------+----------+----------+----------+----------+----------+-----------+--------------+ 
| 年份   | 一月        | 二月   | 三月     | 四月     | 五月     | 六月     | 七月     | 八月     | 九月     | 十月     | 十一月    | 十二月       | 
+--------+-------------+--------+----------+----------+----------+----------+----------+----------+----------+----------+-----------+--------------+ 
|   2011 |        0.00 |   0.00 |     0.00 |     0.00 |     0.00 |     0.00 |     0.00 |     0.00 |     0.00 |     0.00 |      0.00 | 157791865.93 | 
|   2012 | 59793317.04 |   0.00 | 79342.20 | 38757.74 | 45519.50 | 24669.33 | 49387.64 | 23206.87 | 10314.14 | 26005.00 |  60958.59 |      6386.90 | 
+--------+-------------+--------+----------+----------+----------+----------+----------+----------+----------+----------+-----------+--------------+ 


// 表结构如下： 
create table if not exists om_order( 
Order_No int primary key, 
Create_Date datetime, 
Order_Sum decimal(18,2) 
); 

好了，如何实现想要的效果呢？ 
要一步实现确实很有点困难，我们一步一步分析，先不考虑一行展示所有月份数据，我们先按照常规统计（列的形式）来展示每月销售情况，sql如下： 

select year(o.`Create_Date`) as '年份',month(o.`Create_Date`) as '月份',sum(`Order_Sum`) as '订单销售额' from `om_order` o 
-- where year(o.`Create_Date`) = '2012' 
group by year(o.`Create_Date`),month(o.`Create_Date`); 

有了这样的数据后结果后在处理就是单纯的行转列了，sql如下： 
select tmp.o_year as '年份', 
       sum(case when tmp.o_month = 1 then tmp.o_total else 0 end) as '一月', 
       sum(case when tmp.o_month = 2 then tmp.o_total else 0 end) as '二月', 
       sum(case when tmp.o_month = 3 then tmp.o_total else 0 end) as '三月', 
       sum(case when tmp.o_month = 4 then tmp.o_total else 0 end) as '四月', 
       sum(case when tmp.o_month = 5 then tmp.o_total else 0 end) as '五月', 
       sum(case when tmp.o_month = 6 then tmp.o_total else 0 end) as '六月', 
       sum(case when tmp.o_month = 7 then tmp.o_total else 0 end) as '七月', 
       sum(case when tmp.o_month = 8 then tmp.o_total else 0 end) as '八月', 
       sum(case when tmp.o_month = 9 then tmp.o_total else 0 end) as '九月', 
       sum(case when tmp.o_month = 10 then tmp.o_total else 0 end) as '十月', 
       sum(case when tmp.o_month = 11 then tmp.o_total else 0 end) as '十一月', 
       sum(case when tmp.o_month = 12 then tmp.o_total else 0 end) as '十二月'/*, 
       sum(tmp.o_total) as '总计'*/ 
  from     
(       
select year(o.`Create_Date`) as o_year,month(o.`Create_Date`) as o_month,sum(`Order_Sum`) as o_total from `om_order` o 
group by year(o.`Create_Date`),month(o.`Create_Date`) 
) tmp 
group by tmp.o_year; 

好了，大功告成！ 

转载于:https://my.oschina.net/focusme/blog/133873