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