mysql数据行转列

在你找工作的经历当中，是否有面试官问过你：数据库行转列如何实现？
一个典型的例子如下：
    有这样一个订单表（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;


好了，大功告成！