数据库：横表纵表互换

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

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 b
group 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  union
select 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 ;

FOR SQLSERVER(2005及更高版本)

SELECT *
FROM
(
SELECT 10 ids,'ab' rets 
UNION ALL
SELECT 20 ids,'cd' rets
) bb
PIVOT
(
	MAX(rets) FOR ids IN ([10],[20])
) aa

欢迎点评，期待您更好的解决方案。

转载于:https://my.oschina.net/wellsoschina/blog/204619