如何重新格式化透视表格？SQL行列转换问题之行转列的四种解法
最新回答
一朵有毒の花
2026-10-25 09:19:52
解决这类问题虽然思路简单，但代码量较大，容易出错。以下通过实际案例，帮助理解如何在实战中实现分组从行转列取数的过程，总结思路和规律。
需求：编写一个SQL语句，将表格数据转换为每种货币为一列，每列下是使用这种货币的区域或国家名字，且区域和国家需按字母顺序排列。货币代码代表分别为：100代表USD，36代表EU，6代表AU，19代表CAU。
背景：数据来源于微软示例数据库，一家销售自行车制造公司的销售数据。该公司业务主要在美国，但也跨多个区域和国家销售，不同区域和国家使用不同的货币。通过DimOrganization这张表记录分公司（Organizationname）和货币代码（CurrencyKey），共有14行数据，货币代码（CurrencyKey）这一列中有100、36、19、6四种取值。
分析：要处理的是分公司（Organizationname）和货币代码（CurrencyKey），但需要得到的是每种货币为一列，共有四种货币，即四列数据，且数据字段不是原数据字段名，这意味着需要计算或判断生成新的字段。要求每种货币一列，只需从CurrencyKey字段中筛选出每种货币即可。
思路一：简单查询。通过分析，需要筛选出每种情况并拼接，显然要生成四张表，这不再是简单查询，而是连接查询。
解法一：连接查询+自定义变量
以下为连接查询的代码：
这段代码非常实在，一种情况一种情况地去左连接。如果种类再多一点，这并不是一个好的解法。但对于当前情况来说，这也是一种办法。这里代码的核心是自定义变量部分，为什么要使用自定义变量呢？代码显示这就是累加计数，为什么要计数呢？因为需求要求按分公司字母排序，如果没有新生成的计数列，那么每张表之间就没有连接键，想要表连接只会形成笛卡尔积。如果没有这个计数列作为中继，而是使用下面这种解法（这也是很多面对这个问题的初学者自然而然会想到的解法）：
得到的结果如下：
虽然达到了要求，但数据展示很凌乱，而我们需要下面这种形式：
这里简单解释一下这种行转列的特定问题的解法：
第一步，用if语句或casewhen语句判断情况，每个if语句只判断一个情况，找到的所有数据作为一列；有多少种情况，用多少个if语句。
第二步，如果对应情况是数值，且代表数值的含义一般用sum求和或avg求平均；如果是文本，一般使用max或min函数。
回到解法一中的代码，我们看四种情况生成的表是什么样的：
可以看到，实际上是对每种情况里的每行数据进行排序，每张表都有一个排序字段就可以进行表连接了，也能实现按字母排序的需求。
思路二：窗口函数，既然使用到自定义变量累计计数，说明可以使用窗口函数。
解法二：窗口函数
以下是第二种解法代码：
这段代码实现的原理与上一个解法相同，只不过它用分组排名来一次性解决排序。在主SQL语句中，用if语句和max函数是什么意思？就是先根据排名分组，分别取出排名都是1、2、3、4、5、6、7、8的所有数据，在每个排名组里取出符合货币代码的那一条（实际上每个排名是唯一的，只有一条），而组里其他的数据都被赋值为null，再用max函数取出有数据的那一条，就可以过滤掉null值。这里也可以使用min函数，效果是一样的。以下是上面子查询代码取的数据展示：
上面的代码还可以换成下面的写法：
解法三：自定义变量
直接上代码：
每个部分同一个功能还可以使用不同的方式实现。这段代码不解释，跟上面一样。
思路四：自连接。如果看了我之前的解决排名、连续、累加、比较问题之后应该知道我的解题套路，凡是查询问题，都来简单查询/复合查询、聚合函数/窗口函数、自定义变量、自连接四种思路往下顺，一般都能找出两种以上方法，百试不爽。多种思路就多条路，你也可以知道你的SQL语句是可以优化的，优化的途径是什么。
解法四：自连接
以下是自连接代码：
自连接的方式比较巧妙，代码也很简短。它既没有用第一种解法里的四张表，也没有第二种解法里的窗口函数一张表，而是产生了两张表。在表自连接求排名的话，因为可能会存在多行相同的(CurrencyKey, organizationname)，所以无法确定每个(CurrencyKey, organizationname)分组的排名，所以再用一个临时中间变量做一个唯一性区分，也相当于给每行数据增加了行号；然后再将s1表自连接，你看到s2表和s1表取的内容是一样的，不同的是r改为n_r，两者值相等。但是由于MySQL中，s1表中的用户变量@r，会在表s2中被共享。如果在表s2中继续用@r表示行号，其值显然不对。才新增了变量@n_r作为行号。
表自连接的条件是on后面长长的一串，它表示的是在同一个货币代码中，对每个区域A，找出所有区域B，这就跟我在（茴字有几种写法？SQL排名问题之全局排名的四种解法）中解释的如何用自定义变量实现排名原理是一样的，为了实现区域按字母排序，可以将每个区域找出小于这个区域的所有其他区域（在MySQL中不仅数字能比较大小，字母也能比较大小），每个区域后面接的数据行数代表它的排名。具体到on后面的语句表示的是：在同一个货币代码下区域名字典顺序小的区域排在前面，或者如果字典顺序也一样，行号小的区域排在前面。这样就实现了前面三种解法都在解决的排序问题。怎么样，是不是很巧妙呢？
行转列的进阶问题解决思路就在这里，还有一类是反过来列转行，无论什么问题，认准这四种解法，一个一个去套，总能解决。不过代码难度和执行效率是成反比的，除了有现成的窗口函数（内部已经优化好了），代码写得越多越简单执行效率越差，代码写得越少越抽象执行效率越好。当然也不是代码越少越好，而是以执行起来过程中涉及到计算的数据量越少越好，这就需要深刻理解每种解法怎么取数的才能判断。
热门标签
