SQL之行转列专项练习

迷南。 2023-01-03 04:50 113阅读 0赞

### SQL之行转列专项练习 ###

*   *   *  前言
         *  例题
         *  练习一
         *  练习二
         *  练习三

### 前言 ###

最近发现自己SQL基础很差，平时写得少了，正在补这方面的知识。今天就来记录一下SQL的行转列学习过程。

### 例题 ###

所谓行转列，就是把原本同一列下**多行**的不同内容作为多个字段（**列**）进行输出。

![1.1][]

如图所示，左边是基本的数据表，type值表示的是本行value值的类型：1.姓名 2.性别 3.年龄，t\_id表示本行信息所属的用户：1.zs 2.ls 3.ww。因此要求我们查询输出为右边的格式，应该如何处理呢？

建表和数据插入语句如下：

create table test(
       id int(10) primary key,
       type int(10) ,
       t_id int(10),
       value varchar(5)
    );
    insert into test values(100,1,1,'zs');
    insert into test values(200,2,1,'m');
    insert into test values(300,3,1,'50');
    
    insert into test values(101,1,2,'ls');
    insert into test values(201,2,2,'m');
    insert into test values(301,3,2,'30');
    
    insert into test values(102,1,3,'ww');
    insert into test values(202,2,3,'f');
    insert into test values(302,3,3,'10');
    /** 请写出一条查询语句结果格式如下： 姓名 性别 年龄 ------ -------- ----- zs m 50 */

首先可以确定，我们需要查询出的数据有三列，分别是姓名、性别、年龄。那么现在的问题就转换成了：如何把这三种数据查询出来呢？很显然，这三种数据与type有关，自然而然能想到使用where来做判断。

select t.value from test t where t.type = 1;
    select t.value from test t where t.type = 2;
    select t.value from test t where t.type = 3;

这样，我们成功分离出来了每种不同的数据，虽然分到了三张表里面，但是可以再把他们连接起来嘛！由于连接的时候需要用到t\_id字段，因此每个表需要额外查询一下t\_id。

select t1.value as 姓名,t2.value as 性别,t3.value as 年龄 
    	from (select t.t_id,t.value from test t where t.type = 1) t1
        left join (select t.t_id,t.value from test t where t.type = 2) t2 on t1.t_id = t2.t_id
        left join (select t.t_id,t.value from test t where t.type = 3) t3 on t1.t_id = t3.t_id;

![1.2][]

当然，这么做肯定效率高不到哪去，有没有别的思路呢？

因为需要对type的值进行判断，其实可以使用case函数来实现。

case函数有两种格式，简单case函数只是对某个值进行判断，而case搜索函数可以实现比较复杂的逻辑判断，可以根据实际需求来选择。

-- 简单
    case sex
    	when '1' then '男'
    	when '2' then '女'
    	else '其他'
    end
    -- 复杂
    case when score >= 80 then '优秀'
    	 when score >= 60 then '及格'
    	 else '不及格'
    end

这里，我们只需要使用case对type进行判断，就可以对数据进行过滤了。需要注意的是，一个case可以获取到一种类型的数据，因此需要三个case语句，才能得到所需的全部类型。

select case test.type 
           when 1 then value
           end 姓名,
           case test.type
           when 2 then value 
           end 性别,
           case test.type
           when 3 then value
           end 年龄
    	from test;

![2][]

结果是出来了，怎么多了那么多null呢？这是因为我们查询每一列时，只设置了type符合要求的字段值为value，其他字段并没有赋值。例如第一列，只给type为1的字段设置值为value（此时的value值类型为姓名），type为2和3时没有赋值（因为本列要筛选出来的是姓名）。

现在需要解决掉的就是过滤这些null的问题。其实可以发现，列转行后的数据，每一行的t\_id是相同的，那么就可以通过group by 函数，将它们整合起来。

![2.1][]

select case test.type 
           when 1 then value 
           end 姓名,
           case test.type
           when 2 then value
           end 性别,
           case test.type
           when 3 then value
           end 年龄
    	from test group by test.t_id;

![3][]

怎么还有null？我们使用group by函数，将三行的信息合并到了一起，假如此时有一张虚拟表，表示的是group by后的结果，那么真实情况应该是这样的：

![3.1][]

也就是说，group by只是将三行的信息合并到一起后，默认返回了第一行的数据，我们现在还需要设定一些条件，从这张虚拟表中将真正需要的数据筛选出来。

比较常用的就是max和sum函数了，以年龄一列为例，假如使用max函数，会从null、null、50中选择最大的一个值返回，正好能得到我们想要的结果；使用sum函数则会将null、null、50的值相加后返回，也是一样的结果。

当然min、avg()等其他函数也可以达到效果，但是注意选择函数时，要根据数据的数据类型和实际需求来操作。例如假如对姓名一列使用sum，是得不到结果的。

select max(case test.type 
           when 1 then value 
           end) 姓名,
           max(case test.type
           when 2 then value
           end) 性别,
           max(case test.type
           when 3 then value
           end) 年龄
    	from test group by test.t_id;

![4][]

在MySQL中，有一个if函数，功能类似三目运算符，其表达式为：IF(expr1,expr2,expr3)。假如expr1为true，则返回expr2的值，否则返回expr3的值。显然，if函数的功能与case函数是相似的，这个题目也可以使用if函数实现，并且更为简洁。

select max(if(type=1,value,null)) 姓名,
    	   max(if(type=2,value,null)) 性别,
           max(if(type=3,value,null)) 年龄
    	from test group by test.t_id;

Oracle中则有一个decode函数，可以实现比if更复杂的操作，表达式为：decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值)。由于我电脑里没下Oracle，不方便做测试，就只简单提一下用法。

select max(decode(type,1,value)) 姓名
    	   max(decode(type,2,value)) 性别
    	   max(decode(type,3,value)) 年龄
    	from test group by test.t_id;

到这里，我们算是掌握了行转列的方法了，做几个练习加深印象吧。

### 练习一 ###

create table tmp(rq varchar(10),shengfu varchar(5));
    
    insert into tmp values('2005-05-09','胜');
    insert into tmp values('2005-05-09','胜');
    insert into tmp values('2005-05-09','负');
    insert into tmp values('2005-05-09','负');
    insert into tmp values('2005-05-10','胜');
    insert into tmp values('2005-05-10','负');
    insert into tmp values('2005-05-10','负');
    
    /** 请写出一条查询语句结果格式如下： 胜 负 2005-05-09 2 2 2005-05-10 1 2 ------------------------------------------ */

![5][]

这题比较简单，思路上与例题一致，只是注意需要使用sum函数才能统计到所有结果。

-- case 语句
    select rq,
    	   sum(case shengfu
           when '胜' then 1 else 0
           end) 胜,
           sum(case shengfu 
           when '负' then 1 else 0
           end) 负
    	from tmp group by rq;
    -- if 语句
    SELECT rq,
    	   sum(if(shengfu='胜',1,0)) 胜,
    	   sum(if(shengfu='负',1,0)) 负
    	FROM tmp group by rq;

![6][]

### 练习二 ###

create table STUDENT_SCORE
    (
      name    VARCHAR(20),
      subject VARCHAR(20),
      score   int
    );
    insert into student_score (NAME, SUBJECT, SCORE) values ('张三', '语文', 78.0);
    insert into student_score (NAME, SUBJECT, SCORE) values ('张三', '数学', 88.0);
    insert into student_score (NAME, SUBJECT, SCORE) values ('张三', '英语', 98.0);
    insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '语文', 89.0);
    insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '数学', 76.0);
    insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '英语', 90.0);
    insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '语文', 99.0);
    insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '数学', 66.0);
    insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '英语', 59.0);
    /**
    1.请写出一条查询语句结果格式如下：
    姓名   语文  数学  英语
    王五    99   66   59
    2.请写出一条查询语句结果格式如下：
    大于或等于80表示优秀，大于或等于60表示及格，小于60分表示不及格。    
    姓名       语文            数学             英语  
    王五       优秀            及格            不及格   
    /

![7][]

第一问思路与例题一致

-- case when else 语句
    select name,
    	   max(case subject
           when '语文' then score 
           end) 语文,
           max(case subject
           when '数学' then score 
           end) 数学,
           max(case subject
           when '英语' then score 
           end) 英语
    	from student_score group by name;
    -- if 语句
    select name,
    	   max(if(subject='语文',score,0)) 语文,
           max(if(subject='数学',score,0)) 数学,
           max(if(subject='英语',score,0)) 英语
    	from student_score group by name;

![8][]

第二问则复杂一些，但是if语句和case语句都是可以嵌套使用的，也可以互相嵌套，因此只需要在最外层嵌套一个case或者if语句即可，写法很多，本质上是一样的

-- 1
    select name,
    	   case 
           when max(case subject when '语文' then score end) >= 80 then '优秀'
           when max(case subject when '语文' then score end) < 60 then '不及格' else '及格'
           end 语文,
    	   case 
           when max(case subject when '数学' then score end) >= 80 then '优秀'
           when max(case subject when '数学' then score end) < 60 then '不及格' else '及格'
           end 数学,
    	   case 
           when max(case subject when '英语' then score end) >= 80 then '优秀'
           when max(case subject when '英语' then score end) < 60 then '不及格' else '及格'
           end 英语
           from student_score group by name;  
    -- 2
    select name,
    	   case 
           when max(if(subject='语文',score,0)) >= 80 then '优秀'
           when max(if(subject='语文',score,0)) < 60 then '不及格' else '及格'
           end 语文,
    	   case 
           when max(if(subject='数学',score,0)) >= 80 then '优秀'
           when max(if(subject='数学',score,0)) < 60 then '不及格' else '及格'
           end 数学,
    	   case 
           when max(if(subject='英语',score,0)) >= 80 then '优秀'
           when max(if(subject='英语',score,0)) < 60 then '不及格' else '及格'
           end 英语
           from student_score group by name;
    -- 3
    select name,
    	   if(max(if(subject='语文',score,0)) >= 80,'优秀',if(max(if(subject='语文',score,0)) < 60,'不及格','及格')) 语文,
           if(max(if(subject='数学',score,0)) >= 80,'优秀',if(max(if(subject='数学',score,0)) < 60,'不及格','及格')) 数学,
           if(max(if(subject='英语',score,0)) >= 80,'优秀',if(max(if(subject='英语',score,0)) < 60,'不及格','及格')) 英语
           from student_score group by name;
    -- 4
    select name,
    	   if(max(case subject when '语文' then score end) >= 80,'优秀',if(max(case subject when '语文' then score end) < 60,'不及格','及格')) 语文,
           if(max(case subject when '数学' then score end) >= 80,'优秀',if(max(case subject when '数学' then score end) < 60,'不及格','及格')) 数学,
           if(max(case subject when '英语' then score end) >= 80,'优秀',if(max(case subject when '英语' then score end) < 60,'不及格','及格')) 英语
           from student_score group by name;

![10][]

### 练习三 ###

create table yj01(
           month varchar(10),
           deptno int(10),
           yj int(10)
    )
    
    insert into yj01(month,deptno,yj) values('一月份',01,10);
    insert into yj01(month,deptno,yj) values('一月份',01,6);
    insert into yj01(month,deptno,yj) values('二月份',02,10);
    insert into yj01(month,deptno,yj) values('二月份',03,5);
    insert into yj01(month,deptno,yj) values('二月份',01,3);
    insert into yj01(month,deptno,yj) values('三月份',02,8);
    insert into yj01(month,deptno,yj) values('三月份',04,9);
    insert into yj01(month,deptno,yj) values('三月份',03,8);
    insert into yj01(month,deptno,yj) values('三月份',01,7);
    
    create table yjdept(
           deptno int(10),
           dname varchar(20)
    )
    
    insert into yjdept(deptno,dname) values(01,'国内业务一部');
    insert into yjdept(deptno,dname) values(02,'国内业务二部');
    insert into yjdept(deptno,dname) values(03,'国内业务三部');
    insert into yjdept(deptno,dname) values(04,'国际业务部');
    
    /** 请写出一条查询语句结果格式如下：注意提供的数据及结果不一定准确， 部门dep 一月份 二月份 三月份 -------------------------------------- 国内业务一部 10 国内业务二部 10 8 国内业务三部 5 8 国际业务部 9 ------------------------------------------ */

![11][]

其实这个题目思路上与例题是一致的，我们首先需要将yj01这个表转成需求的格式，然后通过链接yj01表yjdept表获取到部门的中文名称，展示即可。

1.行转列成需求格式

select deptno,
    		sum(if(month='一月份',yj,0)) 一月份,
            sum(if(month='二月份',yj,0)) 二月份,
            sum(if(month='三月份',yj,0)) 三月份
    	from yj01 group by deptno;

![12][]

2.表连接

select yd.dname,t.Jan 一月份,t.Feb 二月份,t.Mar 三月份 
    	from (select deptno,
    				 sum(if(month='一月份',yj,0)) Jan,
    				 sum(if(month='二月份',yj,0)) Feb,
    				 sum(if(month='三月份',yj,0)) Mar
    			from yj01 group by deptno) t 
    	left join yjdept yd on t.deptno = yd.deptno 
    	group by yd.deptno;

![13][]

[1.1]: /images/20221119/61290436f4d24aefaf6ecd255548ddc4.png
[1.2]: /images/20221119/3a6bbe2a21c24e6f94054a9a4d5f4bdc.png
[2]: /images/20221119/6b8ed5cde681442caf2ccbdf39b5ef11.png
[2.1]: /images/20221119/2594f11c84714764a13aae7f3389fa5a.png
[3]: /images/20221119/1d4052f13ccd408bbd025e060cc31843.png
[3.1]: /images/20221119/1514a8ac313c471bb3c3dc1cfeda2a14.png
[4]: /images/20221119/6c2a8ddf91054119aa8cbde44ffa24b2.png
[5]: /images/20221119/4c56abf50fce4c8ab520a76eb1426141.png
[6]: /images/20221119/9ee56b79410f4d13995e380fde3764fd.png
[7]: /images/20221119/d83ce4b143f448a590311db3af3e3dda.png
[8]: /images/20221119/da7ab58d5be745a1b6f3b78c922052e0.png
[10]: /images/20221119/d40818aad26f4852a54b4bc82504d75d.png
[11]: /images/20221119/7e4ab62026cf4f209f7c08f0188cefc2.png
[12]: /images/20221119/e306c3c5f1ce41159fcf1df904668a6a.png
[13]: /images/20221119/f0d66dc414b445f9b8ecc550d83a8af4.png