做工程的人来说,技术通过服务业务来产生价值,所以在提高技术的基础上,更多的是需要业务知识,更好得体现技术的价值。
本文,主要结合业务中的需求点,来说明如何使用mysql实现表格的行列互换:
【行转列】
假设你有这样一个表,记录了每个销售部门,每一天的销售金额表1,每日各部门销售金额明细表
use analysis;
CREATE TABLE `tmp_department_sales` (
`id` int(11) NOT NULL AUTO_INCREMENT FIRST,
`sale_day` varchar(32) NULL,
`department` varchar(32) NULL,
`sales_mount` int(11) NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci;
这只是三天,四个部门的情况,如果是三个月,10个部门,那就是900行数据,看起来非常不方便,一般都会有这样的一个需求,如下:表2,按部门汇总
那么怎么用mysql代码实现呢,其实很简单:
select sale_day,
sum(if(department='销售一部',sales_mount,0)) as 'dep_01',
sum(if(department='销售二部',sales_mount,0)) as 'dep_02',
sum(if(department='销售三部',sales_mount,0)) as 'dep_03',
sum(if(department='销售四部',sales_mount,0)) as 'dep_04'
from tmp_department_sales
group by sale_day;
这样就是用了一个sum(if())的组合,以上就是行转列的过程。
【列转行】
如果反过来,要从列转为行,就是基于表2,转换成表1。
先得到一个表tmp_department_sale_rollup,可以实现表2的形式:
use analysis;
drop table if exists analysis.tmp_department_sale_rollup;
create table if not exists analysis.tmp_department_sale_rollup
select sale_day,
sum(if(department='销售一部',sales_mount,0)) as 'dep_01',
sum(if(department='销售二部',sales_mount,0)) as 'dep_02',
sum(if(department='销售三部',sales_mount,0)) as 'dep_03',
sum(if(department='销售四部',sales_mount,0)) as 'dep_04'
from tmp_department_sales
group by sale_day;
select * from tmp_department_sale_rollup;
然后用以下代码进行列转行,其实也很简单:
select sale_day ,dep_01 as sales_mount,'销售一部' as department
from tmp_department_sale_rollup
union all
select sale_day ,dep_02 as sales_mount,'销售二部' as department
from tmp_department_sale_rollup
union all
select sale_day ,dep_03 as sales_mount,'销售三部' as department
from tmp_department_sale_rollup
union all
select sale_day ,dep_04 as sales_mount,'销售四部' as department
from tmp_department_sale_rollup;
以上只是一种思路,肯定还会有其他的方案,欢迎小伙伴留言交流。