mysql行列转换问题_Mysql行列转换

Table pivot行翻转

2019年6月6日

9:16

标准化的数据库是非常强大的，在数据库的开发中，开发者需要竭尽所能的完善的数据保存的方法，同时保证读写的速度，最终一般通过三泛式来解决大多数问题，有时候我们需要信息以行的方法表现但是其分布在多个列中，这是我们就需要使用行翻转来解决问题了；

假设数据库存在以下三个表

Users

Items

User_Items

User_Items是用户以物品之间的一个多对多的关系，为了简单起见，我们不通过唯一的id来进行关连，而是人名与物品名称进行关联，第四个字段代表物品的价格；

假设如果需要统计每个人所持有的单个物品的价格，类似下图

那么表中的数据需要经过一番加工才能以这种形式表现，我们一般可以通过编程语言比如java或php来实现，或者直接通过sql来完成；

通过sql实现行翻转需要四步

1.选择需要的列

2.通过选择的列对表进行扩展

3.将表进行聚合

4.美化

步骤一：选择需要的列

在此例子中我们需要找出item_type中的字段作为列的名称

Item_amount作为行的值

步骤二：通过选择的列对表进行扩展

create view User_Items_Extended as (

select

User_Items.Cust_Names,

case when Item_Type = "Computer" then Item_Amount end as Computer,

case when Item_Type = "Monitor" then Item_Amount end as Monitor,

case when Item_Type = "Software" then Item_Amount end as Software

from User_Items

);

通过以上步骤我们创建了一个表的视图；

步骤三：将表进行聚合

create view User_Items_Extended_Pivot as (

select

Cust_Names,

sum(Computer) as Computer,

sum(Monitor) as Monitor,

sum(Software) as Software

from User_Items_Extended

group by Cust_Names

);

通过聚合函数group我们实现了每个user都有对应的行

步骤四：美化

create view User_Items_Extended_Pivot_Pretty as (

select

Cust_Names,

coalesce(Computer, 0) as Computer,

coalesce(Monitor, 0) as Monitor,

coalesce(Software, 0) as Software

from User_Items_Extended_Pivot

);