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
);

原文地址：http://stratosprovatopoulos.com/web-development/mysql/pivot-a-table-in-mysql/
相关文章：
1.https://stackoverflow.com/questions/22782932/mysql-get-first-non-null-value-after-group-by/22783112#22783112
2.https://stackoverflow.com/questions/1241178/mysql-rows-to-columns/9668036#9668036
3.Pivot table with dynamic columns in MySQL:http://stratosprovatopoulos.com/web-development/mysql/pivot-table-with-dynamic-columns/

转载于:https://www.cnblogs.com/fuzhiyue/p/10986327.html