MySQL之进行行列转换

内容介绍：
如何进行行列转换
如何生成唯一序列号
如何删除重复数据

一：如何进行行列转换
需要用到的场景：主要两个场景（报表统计+汇总显示）

使用自连接实现行列转换

行转列

比如成绩
1：分别查询出不同同学的成绩，并将字段名改为同学的名字。
2：通过交叉连接，将不同的语句连接起来

SELECT *FROM (SELECT SUM(KILLS) AS 'A'FROM A INNER JOIN B ON A.NAME=B.USER_NAMEWHERE A.NAME='A') AS A CROSS JOIN(SELECT SUM(KILLS) AS 'B'FROM A INNER JOIN B ON A.NAME=B.USER_NAMEWHERE A.NAME='B') AS B CROSS JOIN(SELECT SUM(KILLS) AS 'C'FROM A INNER JOIN B ON A.NAME=B.USER_NAMEWHERE A.NAME='C')AS C;

案例：

select * from (select sum(kills) as '孙悟空' from user1 a join user_kills b on a.id = b.use_id and a.user_name='孙悟空') a cross join (select sum(kills) as '猪八戒' from user1 a join user_kills b on a.id = b.use_id and a.user_name='猪八戒') b cross join(select sum(kills) as '沙僧' from user1 a join user_kills b on a.id = b.use_id and a.user_name='沙僧') c

注意：其中括号后面的a b c是给select语句结果表的命名

select sum(kills) as '孙悟空' from user1 a join user_kills b on a.id = b.use_id and a.user_name='孙悟空'

查询出悟空的打怪数量，沙僧猪八戒同理。

缺点：是将原来查询的结果每一行单独查询出来，再进行拼接，因此每增加一个同学就增加一个select语句，并且是通过交叉连接，要保证每个查询的结果只能是一个，不然没办法通过交叉连接实现转换。

使用case实现行列转换

select sum(case when user_name='孙悟空' then kills end) as '孙悟空',sum(case when user_name='猪八戒',thwn kills end) as '猪八戒'）sum(case when user_name='沙僧' then kills end) as '沙僧'from user_name a join user_kills b on a.id = b.user_id；

使用Case语句

SELECT SUM(CASE USER_NAME='A' THEN KILLS END) AS 'A',SUM(CASE USERNAME='B' THEN KILL END) AS 'B',SUM(CASE USERNAME='C' THEN KILL END) AS 'C'FROM A INNER JOIN B ON A.NAME=B.USERNAME;

通过CASE给满足WHEN语句的数据返回SUM聚合函数要统计的数据，再将SUM进行重命名。推荐。
一般形式

SELECT NAME AS '姓名',SUM(CASE SUBJECT WHEN '数学' THEN MARK ELSE NULL END) AS '数学',SUM(CASE SUBJECT WHEN '英语' THEN MARK ELSE NULL END) AS '英语',SUM(CASE SUBJECT WHEN '语文' THEN MARK ELSE NULL END) AS '语文'FROM SUBJECTGROUP BY NAME;

CASE语句中放原来表中要进行转换的字段（如学科），对其进行分类（如数学、英语、语文）。
注意：要用聚合函数，最后要GROUP BY。

列转行的实现
单列转多行：属性拆分，ETL数据处理

利用序列表处理列转行的数据

select user_name,replace(substring(substring_index(mobile,',',a_id),char_length(substring_index(mobile,',',a.id-1),',',")as mobile from tb_sequernce a cross join  select user_name,concat(mobile,',') as mobile,length(mobile)-length(replace(mobile,',',"))+1 size from user1 b ) b on a.id<=b.size;

序列表：存在序列号的表。tb_sequence是序列表。
直接替换user1

使用union all将结果集合并

select user_name,'arms' as equipment, arms from user1 a join user1_equipment b on a.id=user_idunion allselect user_name,'clothing' as equipment, clothing from user1 a join user1_equipment b on a.id=user_id;union allselect user_name,'shoe' as equipment, shoe from user1 a join user1_equipment b on a.id=user_id;

使用序列化的方式列转行

select username,(case when s.id=1 then 'arms' when s.id=2 then 'clothing' when s.id=3 then 'shoe' end) equipment,(case when s.id=1 then arms  when s.id=2 then clothing when s.id=3 then shoe end) eq_name from t_equipment e join t_user uon e.userid = u.userid  cross join t_sequence s where s.id<=3order by username

参考视频：http://www.imooc.com/learn/427