数据库：横表纵表互换

最新推荐文章于 2024-03-19 18:36:23 发布

转载最新推荐文章于 2024-03-19 18:36:23 发布 · 669 阅读

文章标签：

#数据库 #mysql #设计 #横表 #纵表

数据库专栏收录该内容

1 篇文章

订阅专栏

本文介绍了一种将数据库中的纵向表转换为横向表的方法，并提供了两种实现方案：使用聚合函数max/sum进行转换和通过存储过程结合临时表及左连接完成转换。此外，还展示了如何将横向表再转换回纵向表的过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

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




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 ;