MySQL基础-行转列、列转行实例解析

文档准备

要求：找出所有的用户没有安装的软件。

创建两个表，用户表app_install 和 app表app

建表语句：

# 创建app表，并插入数据
create table app(id int,app varchar(32));
insert into app(id,app) values (1,'微信'),(2,'QQ'),(3,'支付宝'),(4,'京东'),(5,'拼多多'),(6,'王者'),(7,'小红书');

# 创建用户表，并插入数据
create table app_install(uid int,app varchar(32));
insert into app_install(uid, app) values (1,'微信'),(1,'QQ'),(1,'支付宝'),(1,'京东'),(2,'微信'),(2,'平多多'),(3,'王者'),(3,'QQ'),(3,'支付宝');

app表：

app_install表：

列转行

第一步：要求统计所有用户没有安装的软件，那么我们首先就需要知道所有用户安装的软件。

select
       uid,
       case when app='微信' then 1 else 0 end as '微信',
       case when app='QQ' then 1 else 0 end as 'QQ',
       case when app='支付宝' then 1 else 0 end as '支付宝',
       case when app='京东' then 1 else 0 end as '京东',
       case when app='拼多多' then 1 else 0 end as '拼多多',
       case when app='王者' then 1 else 0 end as '王者',
       case when app='小红书' then 1 else 0 end as '小红书'
from app_install

第二步：上面这个表得到的行是重复的，每一行只有一条数据。我们需要按照用户将app进行相加。

select tb2.uid,max(微信) '微信',max(QQ) 'QQ',max(支付宝) '支付宝',max(京东) '京东',max(拼多多) '拼多多',max(王者) '王者',max(小红书) '小红书'
from
(select
       uid,
       case when app='微信' then 1 else 0 end as '微信',
       case when app='QQ' then 1 else 0 end as 'QQ',
       case when app='支付宝' then 1 else 0 end as '支付宝',
       case when app='京东' then 1 else 0 end as '京东',
       case when app='拼多多' then 1 else 0 end as '拼多多',
       case when app='王者' then 1 else 0 end as '王者',
       case when app='小红书' then 1 else 0 end as '小红书'
from app_install) tb2 group by uid

注1：第一步的表作为一个整体需要起别名(tb2)

注2：这里的max和sum没有本质的区别，都可以使用

第三步：将0和1转换成文字，同样使用case when...then...else...end as...

select tb1.uid,
       case when 微信=1 then '已安装' else '未安装' end as '微信',
       case when QQ=1 then '已安装' else '未安装' end as 'QQ',
       case when 支付宝=1 then '已安装' else '未安装' end as '支付宝',
       case when 京东=1 then '已安装' else '未安装' end as '京东',
       case when 拼多多=1 then '已安装' else '未安装' end as '拼多多',
       case when 王者=1 then '已安装' else '未安装' end as '王者',
       case when 小红书=1 then '已安装' else '未安装' end as '小红书'
       from
(select tb2.uid uid,max(微信) '微信',max(QQ) 'QQ',max(支付宝) '支付宝',max(京东) '京东',max(拼多多) '拼多多',max(王者) '王者',max(小红书) '小红书'
from
(select
       uid,
       case when app='微信' then 1 else 0 end as '微信',
       case when app='QQ' then 1 else 0 end as 'QQ',
       case when app='支付宝' then 1 else 0 end as '支付宝',
       case when app='京东' then 1 else 0 end as '京东',
       case when app='拼多多' then 1 else 0 end as '拼多多',
       case when app='王者' then 1 else 0 end as '王者',
       case when app='小红书' then 1 else 0 end as '小红书'
from app_install) tb2 group by uid) tb1

第四步：统计app的安装率。在第二步的基础上进行升级

select
       '安装率',
    sum(微信)/count(微信) 微信,
    sum(QQ)/count(QQ) QQ,
    sum(支付宝)/count(支付宝) 支付宝,
    sum(京东)/count(京东) 京东,
    sum(拼多多)/count(拼多多) 拼多多,
    sum(王者)/count(王者) 王者,
    sum(小红书)/count(小红书) 小红书
from
(select max(微信) '微信',max(QQ) 'QQ',max(支付宝) '支付宝',max(京东) '京东',max(拼多多) '拼多多',max(王者) '王者',max(小红书) '小红书'
from
(select
       uid,
       case when app='微信' then 1 else 0 end as '微信',
       case when app='QQ' then 1 else 0 end as 'QQ',
       case when app='支付宝' then 1 else 0 end as '支付宝',
       case when app='京东' then 1 else 0 end as '京东',
       case when app='拼多多' then 1 else 0 end as '拼多多',
       case when app='王者' then 1 else 0 end as '王者',
       case when app='小红书' then 1 else 0 end as '小红书'
from app_install) tb2 group by uid) tb1

如果想把这里的小数点变为xx.xx%的格式，将第三行改为如下：

concat(truncate(sum(微信)/count(微信)*100,2.4),'%') 微信

第五步：将第三步得到的表和第四步得到表进行合并

select tb.uid '用户',
       case when max(tb.微信)=1 then '已安装' else '未安装' end as '微信',
       case when max(tb.QQ)=1 then '已安装' else '未安装' end as 'QQ',
       case when max(tb.支付宝)=1 then '已安装' else '未安装' end as '支付宝',
       case when max(tb.京东)=1 then '已安装' else '未安装' end as '京东',
       case when max(tb.拼多多)=1 then '已安装' else '未安装' end as '拼多多',
       case when max(tb.王者)=1 then '已安装' else '未安装' end as '王者',
       case when max(tb.小红书)=1 then '已安装' else '未安装' end as '小红书'
from
(select
       uid,
       case when app='微信' then 1 else 0 end as '微信',
       case when app='QQ' then 1 else 0 end as 'QQ',
       case when app='支付宝' then 1 else 0 end as '支付宝',
       case when app='京东' then 1 else 0 end as '京东',
       case when app='拼多多' then 1 else 0 end as '拼多多',
       case when app='王者' then 1 else 0 end as '王者',
       case when app='小红书' then 1 else 0 end as '小红书'
from app_install ) tb group by tb.uid
union
select
       '安装率',concat(truncate(sum(微信)/count(微信)*100,2.4),'%') 微信,sum(QQ)/count(QQ) QQ,sum(支付宝)/count(支付宝) 支付宝,sum(京东)/count(京东) 京东,sum(拼多多)/count(拼多多) 拼多多,sum(王者)/count(王者) 王者,sum(小红书)/count(小红书) 小红书
from
(select max(微信) '微信',max(QQ) 'QQ',max(支付宝) '支付宝',max(京东) '京东',max(拼多多) '拼多多',max(王者) '王者',max(小红书) '小红书'
from
(select
       uid,
       case when app='微信' then 1 else 0 end as '微信',
       case when app='QQ' then 1 else 0 end as 'QQ',
       case when app='支付宝' then 1 else 0 end as '支付宝',
       case when app='京东' then 1 else 0 end as '京东',
       case when app='拼多多' then 1 else 0 end as '拼多多',
       case when app='王者' then 1 else 0 end as '王者',
       case when app='小红书' then 1 else 0 end as '小红书'
from app_install) tb2 group by uid) tb1;

列转行

第一步：由于在上面第三步的表中进行更改，于是将上面第三步的表创建视图

create view view_user2 as
select tb.uid '用户',
       case when max(tb.微信)=1 then '已安装' else '未安装' end as '微信',
       case when max(tb.QQ)=1 then '已安装' else '未安装' end as 'QQ',
       case when max(tb.支付宝)=1 then '已安装' else '未安装' end as '支付宝',
       case when max(tb.京东)=1 then '已安装' else '未安装' end as '京东',
       case when max(tb.拼多多)=1 then '已安装' else '未安装' end as '拼多多',
       case when max(tb.王者)=1 then '已安装' else '未安装' end as '王者',
       case when max(tb.小红书)=1 then '已安装' else '未安装' end as '小红书'
from
(select
       uid,
       case when app='微信' then 1 else 0 end as '微信',
       case when app='QQ' then 1 else 0 end as 'QQ',
       case when app='支付宝' then 1 else 0 end as '支付宝',
       case when app='京东' then 1 else 0 end as '京东',
       case when app='拼多多' then 1 else 0 end as '拼多多',
       case when app='王者' then 1 else 0 end as '王者',
       case when app='小红书' then 1 else 0 end as '小红书'
from app_install ) tb group by tb.uid;

第二步：啥也不说了，自己看吧

select u.用户,'微信' as 'app',微信 'status' from view_user2 u
union
select u.用户,'QQ' as 'app',QQ 'status' from view_user2 u
union
select u.用户,'支付宝' as 'app',支付宝 'status' from view_user2 u
union
select u.用户,'京东' as 'app',京东 'status' from view_user2 u
union
select u.用户,'拼多多' as 'app',拼多多 'status' from view_user2 u
union
select u.用户,'王者' as 'app',王者 'status' from view_user2 u
union
select u.用户,'小红书' as 'app',小红书 'status' from view_user2 u

第三步：按照用户id进行排序

select u.用户,'微信' as 'app',微信 'status' from view_user2 u
union
select u.用户,'QQ' as 'app',QQ 'status' from view_user2 u
union
select u.用户,'支付宝' as 'app',支付宝 'status' from view_user2 u
union
select u.用户,'京东' as 'app',京东 'status' from view_user2 u
union
select u.用户,'拼多多' as 'app',拼多多 'status' from view_user2 u
union
select u.用户,'王者' as 'app',王者 'status' from view_user2 u
union
select u.用户,'小红书' as 'app',小红书 'status' from view_user2 u
order by 用户 asc;