MySQL开发技巧——行列转换

第1关 使用CASE语句实现行转列

任务描述

本关任务：使用CASE语句将学生成绩表中的所有成绩转换成具体每科的成绩进行多列显示。

相关知识

统计打怪数问题

观察下面数据，我们需要将左边hunt表中的信息转换成右边info表，那么我们应该如何编写SQL来实现呢？

SQL分析

现hunt表结构如下：

要转换成右边info表格式，我们首先需要两个步骤：

• 数据汇总；

• 行转列。

现在我们得到的数据和最终数据还存在差异，那么我们要怎么进行转换呢？我们其实可以获取单独的每个人的打怪总数：

那么我们只要能将三组数据合并成一组数据并按行的形式来显示，不就可以得到右边info表了吗。

现在是不是都想到了使用inner join来将SQL进行连接，再将得到的sum(kills)字段名改成对应的名称就完美了。

注意：效果我们虽然达到了，但是同学们也都发现了使用这种方式来进行行转列是有缺点的。

例如，我们的连接次数会随着name的增加而增加，用户数量越多我们需要连接的表也就越多，总的来说效率不高，且也会给我们编写SQL带来复杂性。

SQL编写

下面我们来看另外一种方式，相当于inner join来说使用**CASE语句来进行行列转换会更加通用简洁**，下面我们就一起来看看如何使用CASE语句转换：

那么为什么要使用SUM聚合函数呢？那就来看看我们去掉SUM后的结果：

看了上面的查询结果，应该会猜到原因，执行SQL时，查询表中第一条记录时，name为孙悟空，kills为20，然后就被case语句转成kills即20。那么后面两个case因为在这一行中没被匹配到，因此都输出NULL。

或者我们还可以使用IF函数：

编程要求

请仔细阅读代码，根据方法内的提示，在Begin - End区域内进行代码补充，具体任务如下：

• 将下面左表的信息转换成右表的格式进行查询输出。

score表结构如下：

预期输出：

1. | s_name | 语文  | 数学  | 英语 |
2. +----------+--------+--------+--------+
3. | Ashley | 74   | 88   | 90  |
4. | Jennifer | 85  | 89   | 99  |
5. | Nicole | 80   | 83   | 79  |
6. +----------+--------+--------+--------+

---

#请在此添加实现代码
########## Begin ##########
select s_name,
SUM(case c_name when '语文' then s_score end) '语文',
SUM(case c_name when '数学' then s_score end) '数学',
SUM(case c_name when '英语' then s_score end) '英语'
from score 
group by s_name;
 
########## End ##########

第2关 序列化表的方法实现列转行（一）

任务描述

本关任务：将学生成绩表中的scores列中信息转换成行显示。

相关知识

问题

列转行有两种情况，一种是源数据只有一列并通过某种符号分隔的字符串，我们需要将字符串分成多行显示；另一种是表本身是多列的数据，我们需要转成多行。

情景一

首先我们来看第一种情况，有上表转换成下表：

SQL分析及编写

这种类型问题我们如何进行列转行呢？可以利用序列化表来解决。所谓序列表，顾名思义就是存着序列号的一张表（表中的数量需要和你），如下sequence表：

下面就是利用序列化实现的SQL：

初步看上去这个SQL显得很复杂，下面我们就将其一步一步分解来看：

• 首先我们来看看子查询做了些什么：

这个查询的目的很简单，就是通过计算mobile列中,的个数+1来得到有多少个电话号码。

• 使用序列化表inner join来对上面结果集进行关联，注意关联条件a.id<=b.size，也就对应每个user_name的size行数，例如不进行数据的截取，直接输出的结果如下：

• 那么得到如下数据后，最后的结果输出就简单了，只要进行字符串的截取我们就大功告成了。

编程要求

请仔细阅读代码，根据方法内的提示，在Begin - End区域内进行代码补充，具体任务如下：

• 将下面左表的信息转换成右表的格式进行查询输出：

左表tb_score表结构如下：

tb_sequence序列表结构：

 
#请在此添加实现代码
########## Begin ##########
select b.name,
substring_index(replace(substring(substring_index(b.scores,',',s.id),char_length(substring_index(b.scores,',',s.id-1))+1),',',''),':',1) course,
substring_index(replace(substring(substring_index(b.scores,',',s.id),char_length(substring_index(b.scores,',',s.id-1))+1),',',''),':',-1) score
from tb_sequence s inner join 
(select name,scores as course,scores,length(scores)-length(replace(scores,',',''))+1 size
from tb_score) b 
on s.id <= b.size;
########## End ##########
 
 
 

第3关 序列化表的方法实现列转行（二）

任务描述

本关任务：将学生成绩表中chinese、math和english三列中的成绩转换成一列s_score显示学生成绩。

相关知识

情景二

接着上一章节，现在我们来看第二种情况，表中本身就有多列数据，需要你将其转为多行：

SQL分析

首先我们将问题简单化一下，若我们不需要这么多列，只需要将其中某列数据转成行的形式，下面以取出每个人mobile1的电话为例，则SQL如下：

但是这个显示中我们并不能看出是mobile1的值，为了和所需更接近，我们需要再添加一列：

以此类推，我们同样可以将mobile2和mobile3列表，那么我们是不是只需要将这三组数据成一个就能实现列转行的转换了，我们就使用union来进行连接为了达到和我们预期的结果一样，我们通过姓名排序得到最终结果：

观察SQL我们可能都发现了，使用union的方式实现列转行并不太方便，如果还有更多的mobilen的话，我们所需要连接的表就会随着增加，长度不好控制。

SQL编写

那么我们是否可以使用上一章节中提到过的序列化表来实现呢？显然是可以的，下面我们就一起来使用序列化表的方式实现一行转三行。

• 首先，我们需要通过序列表进行笛卡尔积连接，这里我们只需要将一行转成三行，因此使用id<=3即可，为了使大家更清晰观察数据我们通过姓名进行排序；

• 但是上面表中的三条数据我们对每个用户都只需要其中的一条，然后进行组装就行，那么这个时候就要用到我们的CASE语句了；

  

• 那么接下来我们就可以使用coalesce()函数来只取非空值；

• 此时和我们所要的结果就比较相近了，只差一列数据，我们仍使用CASE语句来进行最后一列的SQL编写。

编程要求

请仔细阅读代码，根据方法内的提示，在Begin - End区域内进行代码补充，具体任务如下：

• 将下面左表的信息转换成右表的格式进行查询输出：

左表tb_score表结构如下：

tb_sequence序列表结构：

 
#请在此添加实现代码
########## Begin ##########
select s_name,
case when s.id=1 then '语文'
when s.id=2 then '数学'
when s.id=3 then '英语'
end s_cource,
coalesce(
    case when s.id=1 then chinese end,
    case when s.id=2 then math end,
    case when s.id=3 then english end
)
s_score
from tb_score t
inner join tb_sequence s
where s.id <=3
order by s_name,field(s_cource,'数学','英语','语文');
 
########## End ##########