数据库：横表纵表互换

下文是一个数据库纵表转横表的设计。感觉这个案例还是比较典型的。

对于我这种对于数据库不是很熟悉的人来说。还是很有指导意义的。

use test;/** * 创建表 */create table sales(id       int       primary key auto_increment,years    year(4)   not null,month    int       not null,sales    int       not null); /** * 初始化数据 */insert into sales(years, month, sales) values('2011', 1, 3);insert into sales(years, month, sales) values('2011', 2, 2);insert into sales(years, month, sales) values('2011', 3, 4);insert into sales(years, month, sales) values('2011', 4, 1); insert into sales(years, month, sales) values('2012', 1, 1);insert into sales(years, month, sales) values('2012', 2, 2);insert into sales(years, month, sales) values('2012', 3, 2);insert into sales(years, month, sales) values('2012', 4, 3); select * from sales;  /** * 纵表变横表(聚合函数max/sum) */select b.years,    max(case b.month when 1 then sales else 0 end) '一月',    max(case b.month when 2 then sales else 0 end) '二月',    max(case b.month when 3 then sales else 0 end) '三月',    max(case b.month when 4 then sales else 0 end) '四月'from sales as bgroup by b.years;  /** * 纵表变横表(存储过程+ 临时表+左联结) */drop procedure if exists sp_change;drop table if exists a1,a2,a3,a4;  delimiter //create procedure sp_change()    begin        create temporary table a1(years year(4),`一月` int) engine = Memory;        create temporary table a2(years year(4),`二月` int) engine = Memory;        create temporary table a3(years year(4),`三月` int) engine = Memory;        create temporary table a4(years year(4),`四月` int) engine = Memory;          insert into a1 select a.years, a.sales as '一月' from sales as a where a.month = 1 ;          insert into a2 select b.years, b.sales as '二月' from sales as b where b.month = 2 ;         insert into a3 select c.years, c.sales as '三月' from sales as c where c.month = 3 ;         insert into a4 select d.years, d.sales as '四月' from sales as d where d.month = 4 ;                 select a1.years as year,a1.`一月`,a2.`二月`,a3.`三月`,a4.`四月`        from a1         left join a2 on a1.years = a2.years         left join a3 on a2.years = a3.years         left join a4 on a3.years = a4.years        order by a1.years;           drop table if exists a1,a2,a3,a4;   end //delimiter ;call sp_change();    /** * 横表变纵表 */select a.years, a.sales as '一月' from sales as a where a.month = 1  unionselect b.years, b.sales as '二月' from sales as b where b.month = 2  union select c.years, c.sales as '三月' from sales as c where c.month = 3  union select d.years, d.sales as '四月' from sales as d where d.month = 4 ;