mysql 技巧二篇

技巧一：行转列：用cross join或者case方法进行行列转换

用的场景如下：

1. 报表

2.汇总表

eg:

步骤1：要查出每个人打怪的总的个数

select a.user_name,sum(kills) from test1 a join user_kills b on a.id=b.user_id group by a.user_name;   ==>查询出每个人打怪的总的个数，但是显示是列的显示格式

步骤2：cross join 进行行列转换。

可以先查出每个人的打怪总数，再用迪卡尔集相乘，因为每个人都只有一条数据，故迪卡尔出来也是一条数据。把sum(kills)字段别名为‘sun wu kong‘,这样查询的话，若分类比较多的话，则要cross join很多记录。

select * from 
(select sum(kills) as 'sun wu kong' from test1 a join user_kills b on a.id=b.user_id where a.user_name='sun wu kong') a cross join
(select sum(kills) as 'zhu ba jie' from test1 a join user_kills b on a.id=b.user_id where a.user_name='zhu ba jie') b cross join
(select sum(kills) as 'sha seng' from test1 a join user_kills b on a.id=b.user_id where a.user_name='sha seng') c

也可以用序列号及case方法进行行列转换

select sum(case when user_name='sun wu kong' then kills end) as 'sun wu kong',
    sum(case when user_name='zhu ba jie' then kills end) as 'zhu ba jie',
  sum(case when user_name='sha seng' then kills END) as 'sha seng'
from test1 a join user_kills b on a.id=b.user_id;

     返回的结果是：

技巧二:将列转换成行

用的场景如下：

1.属性拆分

2.ETL数据处理：上层数据是excel等的数据要入数据仓库

这上面两种情况就要将原先(左边的表)转换成列(右边的表)

eg:将上图的表通过行转列转成右边的表。sql语句如下

select user_name,replace(SUBSTRING(substring_index(mobile,',',a.`id`),CHAR_LENGTH(substring_index(mobile,',',a.id-1))+1),',','') as mobile from tab_sequence a cross join(
select user_name,concat(mobile,',')as mobile,length(mobile)-length(replace(mobile,',',''))+1 size
from test1 b)b on a.id<=b.size;

分解里面的子查询：

#子查询算出逗号的个数，即多少个号码

select user_name,concat(mobile,',')as mobile,length(mobile),length(replace(mobile,',','')),length(mobile)-length(replace(mobile,',',''))+1 size
from test1 b;

迪卡尔集每个用户多少个号码产生多少条数据

select * from tab_sequence a cross join(
select user_name,concat(mobile,',')as mobile,length(mobile)-length(replace(mobile,',',''))+1 size
from test1 b)b on a.id<=b.size;

3.场景3：直接将行转换成多列

方法一：使用cross join连接

步骤1：查看表test_equipment表结构，select * from test1_equipment; 

步骤2：要将左表转换右表形式，需要username字段，故应关联test1表，生成左表的列表

select user_name,arms,clothing,shoe from test1 a join test1_equipment b on a.id=b.user_id;

步骤3：要将左表的一列转成行，例如将左表的arms一列转成行，

select user_name,'arms' as equipment,arms from test1 a join test1_equipment b on a.id=b.user_id;

步骤4.再将其它列转为行(clothing,shoe)，后面用union all 连接，union all是去重，若不去重则直接用union

select user_name,'arms' as equipment,arms from test1 a join test1_equipment b on a.id=b.user_id
union all
select user_name,'clothing' as equipment,clothing from test1 a join test1_equipment b on a.id=b.user_id
union all
select user_name,'shoe' as equipment,shoe from test1 a join test1_equipment b on a.id=b.user_id order by user_name; ==>已经转换为右表

方法二：利用序列号及case when来进行列转行

步骤1：先生成左表：要将左表转换右表形式，需要username字段，故应关联test1表，生成左表的列表

select user_name,arms,clothing,shoe from test1 a join test1_equipment b on a.id=b.user_id;    ====》生成左表

步骤2：左表变成右表先要将一行转为三行，例如唐僧的有三条信息：arms,clothing,shoe,利用序列表迪卡尔集，但是序列表id要控制到3行

select user_name,'arms' as equipment,arms from test1 a join test1_equipment b on a.id=b.user_id cross join tab_sequence c      

  where c.id<=3 order by `user_name`;

步骤3：上面的表每条数据都有3行，则我们只要取出一行即可，用case when语句，现在是取出的一条记录，但是其它的都显示为空。

select user_name,case when c.id=1 then arms end,case when c.id=2 then clothing end,case when c.id=3 then shoe end
from test1 a join test1_equipment b on a.id=b.user_id cross join tab_sequence c
  where c.id<=3 order by `user_name`;

步骤4：将上面的空值去掉，用函数coalesce，它的意思是如果是第一组非空取第一组数据，继续取第二组非空值

select user_name,
coalesce(case when c.id=1 then arms end,
case when c.id=2 then clothing end,
case when c.id=3 then shoe end) as eq_name
from test1 a join test1_equipment b on a.id=b.user_id cross join tab_sequence c
  where c.id<=3 order by `user_name`;

 ===>与右边的表很像，只差了一列装备类型equipment

步骤5：将装备类型加上，生成右表

  select user_name,
case when c.id=1 then 'arms'
    when c.id=2 then 'clothing'
      when c.id=3 then 'shoe'
end as equipment,
coalesce(case when c.id=1 then arms end,
case when c.id=2 then clothing end,
case when c.id=3 then shoe end) as eq_name
from test1 a join test1_equipment b on a.id=b.user_id cross join tab_sequence c
            where c.id<=3 order by `user_name`;