把数据从行转列，从列转成行是常常遇到的经典问题，这种问题需要用特定的方法去解决，只要搞懂了就能解决这一类问题。总的来说，从行转列大多是要使用if语句或casewhen语句，从列转成行要使用的是union或union all语句。在实际工作中，我们常常要根据需求进行这样重新格式化表格数据，也叫透视或逆透视。如果你会使用PowerBI就会知道里面有个透视或逆透视的功能选项卡。
解决这类问题的思路很简单但是要写的代码量很多而容易出错，下面用实际案例数据还原真实取数场景，帮助你在实战中理解如何实现分组从行转列取数的过程，总结思路和规律。
需求：写一个SQL语句，需要将表格数据转换为每种货币为一列，每一列下是使用这种货币的区域或国家名字，并且区域和国家需按照字母顺序排列，需要列出所有情况。货币代码代表分别为：100代表USD，36代表EU，6代表AU，19代表CAU。
背景：数据来源于微软示例数据库，一家销售自行车制造公司的销售数据，这家公司的业务主营在美国，但也跨很多区域和国家销售，不同的区域和国家使用不同的货币。通过DimOrganization这张表来记录分公司（Organizationname）和货币代码（CurrencyKey），共14行数据，货币代码（CurrencyKey）这一列中有100，36,19,6四种取值。
分析：拿到需求最先搞清楚要取哪几个字段数据。这里要处理的是分公司（Organizationname）和货币代码（CurrencyKey）,但是要得到的是每种货币为一列，有四种货币，就是四列数据，而且数据字段不是原数据字段名，这就意味需要计算或者判断生成新的字段。在这里要求每种货币一列，那就把每种货币从CurrencyKey字段中筛选出来不就可以了。
思路一：简单查询。通过上面的分析，我要把每种情况筛选出来再拼接，显然要生成四张表，这就不是简单查询，而是连接查询了。
解法一 连接查询+自定义变量
如果不是熟练的数据分析师，可能没办法一下找到巧妙的解决办法。但是如果我们通过拆解再拼接，也能把问题解决。以下是连接查询的代码：
select USD,EU,AU,CAU
from (select organizationname as USD,@r1:=@r1+1 as rk1
from DimOrganization,(select @r1:=0) a
where CurrencyKey=100
order by organizationname)USD
left join (select organizationname as EU,@r2:=@r2+1 as rk2
from DimOrganization,(select @r2:=0)b
where CurrencyKey=36
order by organizationname)EU
on USD.rk1=EU.rk2
left join (select organizationname as AU,@r3:=@r3+1 as rk3
from DimOrganization,(select @r3:=0)c
where CurrencyKey=19
order by organizationname) AU
on USD.rk1=AU.rk3
left join (select organizationname as CAU,@r4:=@r4+1 as rk4
from DimOrganization,(select @r4:=0)d
where CurrencyKey=6
order by organizationname) CAU
on USD.rk1=CAU.rk4;
这段代码写的很实在，一种情况一种情况去左连接，如果种类再多一点，这就不是好的解法。但是对于当前情况来说也是一种办法。这里代码的核心是自定义变量部分，为什么要使用自定义变量呢？写的代码显示这就是累加计数，为什么要计数呢？因为需求要求按照分公司字母排序，并且如果没有新生成的计数列，那么每张表之间就没有连接键，想要表连接只会形成笛卡尔积。如果没有这个计数列作为中继，而是使用下面这种解法（这也是很多面对这个问题的初学者自然而然会想到的解法）：
select if(CurrencyKey=100,organizationname, null) us,
if(CurrencyKey=36,organizationname, null) eu,
if(CurrencyKey=19,organizationname, null) au,
if(CurrencyKey=6,organizationname, null) cu
from DimOrganization
order by organizationname;
得到的结果如下：
虽然达到了要求，但数据展示很凌乱，而我们需要下面这种形式：
这里简单解释下这种行转列的特定问题的解法：
第一步，用if语句或者casewhen语句判断情况，每个if语句只判断一个情况，找到的所有数据作为一列；有多少种情况用多少个if语句。
第二步，如果对应情况是数值，且代表数值的含义一般用sum求和或者avg求平均，如果是文本，一般使用上面这种max或min函数。
回到解法一中的代码，我们看四种情况生成的表是什么样的：
可以看到，实际上是对每种情况里的每行数据进行排序，每张表都有一个排序字段就可以进行表连接了，也能实现按字母排序的需求。
思路二 窗口函数，既然使用到自定义变量累计计数，说明可以使用窗口函数。
解法二 窗口函数
下面是第二种解法代码：
select
max(if(CurrencyKey=100,organizationname, null)) us,
max(if(CurrencyKey=36,organizationname, null)) eu,
max(if(CurrencyKey=19,organizationname, null)) au,
max(if(CurrencyKey=6,organizationname, null)) cu
from (select *, row_number() over (partition by CurrencyKey order by organizationname) rk
from DimOrganization) t
group by t.rk;
这段代码实现的跟上一个解法原理是一样的。只不过它用分组排名来一次性解决排序。在主SQL语句中用if语句和max函数是什么意思呢？就就是先根据排名分组，分别取出排名都是1,2,3,4,5,6,7,8的所有数据,在每个排名组里取出符合货币代码的那一条（实际上每个排名是唯一的，只有一条），而组里其他的数据都被赋值为null,再用max函数取出有数据的那一条，就可以过滤掉null值。这里也可以使用min函数，效果是一样的。下面是上面子查询代码取的数据展示：
上面的代码还可以换成下面的写法：
解法三 自定义变量
直接上代码：
select
min(case when CurrencyKey=100 then organizationname else null end) us,
min(case when CurrencyKey=36 then organizationname else null end) eu,
min(case when CurrencyKey=19 then organizationname else null end) au,
min(case when CurrencyKey=6 then organizationname else null end) cu
from (select CurrencyKey,organizationname,
if(@c=CurrencyKey,if(@o:=organizationname,@r:=@r,@r:=@r+1),@r:=1) rk,
@c:=CurrencyKey,@o:=organizationname
from DimOrganization,(select @c:=0,@o:=0,@r:=0) init
order by CurrencyKey,organizationname) t
group by t.rk;
每个部分同一个功能还可以使用不同的方式实现。这段代码不解释，跟上面一样。
思路四：自连接。如果看了我之前的解决排名、连续、累加、比较问题之后应该知道我的解题套路，凡是查询问题，都来简单查询/复合查询、聚合函数/窗口函数、自定义变量、自连接四种思路往下顺，一般都能找出两种以上方法，百试不爽。多种思路就多条路，你也可以知道你的SQL语句是可以优化的，优化的途径是什么。
解法四 自连接
以下是自连接代码：
select
max(if(CurrencyKey=100,organizationname, null)) us,
max(if(CurrencyKey=36,organizationname, null)) eu,
max(if(CurrencyKey=19,organizationname, null)) au,
max(if(CurrencyKey=6,organizationname, null)) cu
from (select s1.CurrencyKey,s1.organizationname,s1.r,count(*) as rk
from (select s.*,@r:=(@r + 1) as row_id
from DimOrganization as s,(select @r:=0) as t) as s1
join (select s.*,@n_r:=(@n_r + 1) as n_r
from DimOrganization as s,(select @n_r:=0) as t) as s2
on (s1.CurrencyKey = s2.CurrencyKey and (s1.organizationname > s2.organizationname or (s1.organizationname = s2.organizationname and s1.r >= s2.n_r)))
group by s1.CurrencyKey,s1.organizationname,s1.r
order by s1.CurrencyKey,s1.organizationname) as a
group by a.rk;
自连接的方式就比较巧妙了，代码也很简短。它既没有用第一种解法里的四张表，也没有第二种解法里的窗口函数一张表，而是产生了两张表。在表自连接求排名的话，因为可能会存在多行相同的(CurrencyKey,organizationname)，所以无法确定每个(CurrencyKey,organizationname)分组的排名，所以再用一个临时中间变量做一个唯一性区分，也相当于给每行数据增加了行号；然后再将s1表自连接，你看到s2表和s1表取的内容是一样的，不同的是r改为n_r，两者值相等。但是由于mysql中，s1表中的用户变量@r，会在表s2中被共享。如果在表s2中继续用@r表示行号，其值显然不对。才新增了变量@n_r作为行号。
表自连接的条件是on后面长长的一串，它表示的是在同一个货币代码中，对每个区域A，找出所有区域B，这就跟我在（茴字有几种写法？SQL排名问题之全局排名的四种解法）中解释的如何用自定义变量实现排名原理是一样的，为了实现区域按字母排序，可以将每个区域找出小于这个区域的所有其他区域（在MySQL中不仅数字能比较大小，字母也能比较大小），每个区域后面接的数据行数代表它的排名。具体到on后面的语句表示的是：在同一个货币代码下区域名字典顺序小的区域排在前面，或者如果字典顺序也一样，行号小的区域排在前面。这样就实现了前面三种解法都在解决的排序问题。怎么样，是不是很巧妙呢？
行转列的进阶问题解决思路就在这里，还有一类是反过来列转行，无论什么问题，认准这四种解法，一个一个去套，总能解决。不过代码难度和执行效率是成反比的，除了有现成的窗口函数（内部已经优化好了），代码写得越多越简单执行效率越差，代码写得越少越抽象执行效率越好。当然也不是代码越少越好，而是以执行起来过程中涉及到计算的数据量越少越好，这就需要深刻理解每种解法怎么取数的才能判断。
最后欢迎大家关注我，我是拾陆，搜索公众号“二八Data”，更多技术干货持续奉献。
扫一扫
3896
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
