SQL中进行转列的几种方式

sql中进行转列

在很多笔试的程序员中会有很多写sql的情况，其中很多时候会考察行转列。那么这个时候如果能写出来几种行转列的sql，会给面试官留下比较好的印象。

以下是这次sql转换的表结构以及数据

数据准备

1、学生表

create table `student` (

`stuid` varchar(16) not null comment '学号',

`stunm` varchar(20) not null comment '学生姓名',

primary key (`stuid`)

)

collate='utf8_general_ci'

engine=innodb;

2、课程表

create table `curriculum` (

`courseno` varchar(20) not null,

`coursenm` varchar(100) not null,

primary key (`courseno`)

)

comment='课程表'

collate='utf8_general_ci'

engine=innodb;

3、成绩表

create table `score` (

`stuid` varchar(16) not null,

`courseno` varchar(20) not null,

`scores` float null default null,

primary key (`stuid`, `courseno`)

)

collate='utf8_general_ci'

engine=innodb;

4、基本数据

/*学生表数据*/

insert into student (stuid, stunm) values('1001', '张三');

insert into student (stuid, stunm) values('1002', '李四');

insert into student (stuid, stunm) values('1003', '赵二');

insert into student (stuid, stunm) values('1004', '王五');

insert into student (stuid, stunm) values('1005', '刘青');

insert into student (stuid, stunm) values('1006', '周明');

/*课程表数据*/

insert into curriculum (courseno, coursenm) values('c001', '大学语文');

insert into curriculum (courseno, coursenm) values('c002', '新视野英语');

insert into curriculum (courseno, coursenm) values('c003', '离散数学');

insert into curriculum (courseno, coursenm) values('c004', '概率论与数理统计');

insert into curriculum (courseno, coursenm) values('c005', '线性代数');

insert into curriculum (courseno, coursenm) values('c006', '高等数学(一)');

insert into curriculum (courseno, coursenm) values('c007', '高等数学(二)');

/*成绩表数据*/

insert into number_result(stuid, courseno, scores) values('1001', 'c001', 67);

insert into number_result(stuid, courseno, scores) values('1002', 'c001', 68);

insert into number_result(stuid, courseno, scores) values('1003', 'c001', 69);

insert into number_result(stuid, courseno, scores) values('1004', 'c001', 70);

insert into number_result(stuid, courseno, scores) values('1005', 'c001', 71);

insert into number_result(stuid, courseno, scores) values('1006', 'c001', 72);

insert into number_result(stuid, courseno, scores) values('1001', 'c002', 87);

insert into number_result(stuid, courseno, scores) values('1002', 'c002', 88);

insert into number_result(stuid, courseno, scores) values('1003', 'c002', 89);

insert into number_result(stuid, courseno, scores) values('1004', 'c002', 90);

insert into number_result(stuid, courseno, scores) values('1005', 'c002', 91);

insert into number_result(stuid, courseno, scores) values('1006', 'c002', 92);

insert into number_result(stuid, courseno, scores) values('1001', 'c003', 83);

insert into number_result(stuid, courseno, scores) values('1002', 'c003', 84);

insert into number_result(stuid, courseno, scores) values('1003', 'c003', 85);

insert into number_result(stuid, courseno, scores) values('1004', 'c003', 86);

insert into number_result(stuid, courseno, scores) values('1005', 'c003', 87);

insert into number_result(stuid, courseno, scores) values('1006', 'c003', 88);

insert into number_result(stuid, courseno, scores) values('1001', 'c004', 88);

insert into number_result(stuid, courseno, scores) values('1002', 'c004', 89);

insert into number_result(stuid, courseno, scores) values('1003', 'c004', 90);

insert into number_result(stuid, courseno, scores) values('1004', 'c004', 91);

insert into number_result(stuid, courseno, scores) values('1005', 'c004', 92);

insert into number_result(stuid, courseno, scores) values('1006', 'c004', 93);

insert into number_result(stuid, courseno, scores) values('1001', 'c005', 77);

insert into number_result(stuid, courseno, scores) values('1002', 'c005', 78);

insert into number_result(stuid, courseno, scores) values('1003', 'c005', 79);

insert into number_result(stuid, courseno, scores) values('1004', 'c005', 80);

insert into number_result(stuid, courseno, scores) values('1005', 'c005', 81);

insert into number_result(stuid, courseno, scores) values('1006', 'c005', 82);

insert into number_result(stuid, courseno, scores) values('1001', 'c006', 77);

insert into number_result(stuid, courseno, scores) values('1002', 'c006', 78);

insert into number_result(stuid, courseno, scores) values('1003', 'c006', 79);

insert into number_result(stuid, courseno, scores) values('1004', 'c006', 80);

insert into number_result(stuid, courseno, scores) values('1005', 'c006', 81);

insert into number_result(stuid, courseno, scores) values('1006', 'c006', 82);

我们先看一下最基本的查询效果是什么样的

静态行转列

select st.stuid, st.stunm,

max(case c.coursenm when '大学语文' then s.scores else 0 end ) '大学语文',

max(case c.coursenm when '新视野英语' then ifnull(s.scores,0) else 0 end ) '新视野英语',

max(case c.coursenm when '离散数学' then ifnull(s.scores,0) else 0 end ) '离散数学',

max(case c.coursenm when '概率论与数理统计' then ifnull(s.scores,0) else 0 end ) '概率论与数理统计',

max(case c.coursenm when '线性代数' then ifnull(s.scores,0) else 0 end ) '线性代数',

max(case c.coursenm when '高等数学(一)' then ifnull(s.scores,0) else 0 end ) '高等数学(一)',

max(case c.coursenm when '高等数学(二)' then ifnull(s.scores,0) else 0 end ) '高等数学(二)'

from student st

left join number_result s on st.stuid = s.stuid

left join curriculum c on c.courseno = s.courseno

group by st.stuid

很多人肯定不理解为什么要使用max函数，实际上大家都知道聚合函数是和分组进行搭配使用的。这一点毋庸置疑，那么大家可以把max函数去掉看看会显示什么效果。切记去掉max函数记得把分组也去掉，这样才能看到本质。

这时大家会发现没列都出现了重复的数据，而且只有一列是有值得。其他列都是0.那么这个时候就应该能很清楚的认识到，为什么使用max函数了。在分组的同时取一组中的最大值。

静态行转列有一个弊端就是第一确定有多少个课程，然后再把课程名称拿出来再写查询语句。但是这样会写很多东西。

动态行转列

首先我们要动态的获取是列的数据 ：

max(case c.coursenm when ‘大学语文’ then s.scores else 0 end ) ‘大学语文’,

max(case c.coursenm when ‘线性代数’ then ifnull(s.scores,0) else 0 end ) ‘线性代数’,

max(case c.coursenm when ‘离散数学’ then ifnull(s.scores,0) else 0 end ) ‘离散数学’

这里想动态的获取到上面的就需要拼接sql列 ：

select

group_concat( distinct concat( ‘max(if(c.coursenm = ‘’’, c.coursenm, ‘’’, s.scores, 0)) as ‘’’, c.coursenm, ‘’’’ ) )

from

curriculum c;

在这里解释一下 ：

concat()函数 ： 将多个字符串连接成一个字符串。

语法：concat_ws(separator, str1, str2, …)

说明：第一个参数指定分隔符。需要注意的是分隔符不能为null，如果为null，则返回结果为null。

group_concat()函数 ：将group by产生的同一个分组中的值连接起来，返回一个字符串结果。

语法：group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator ‘分隔符’] )。

说明：通过使用distinct可以排除重复值；如果希望对结果中的值进行排序，可以使用order by子句；separator是一个字符串值，缺省为一个逗号。

动态的列是拿到了，那如何再结合sql语句进行查询得到结果呢？

这里要说明一点，因为用到了拼接函数，如果像上面的查询语句，只是把那几行语句替换掉，也就是下面这样

select st.stuid, st.stunm,

(

select

group_concat(distinct

concat(

'max(if(c.coursenm = ''',

c.coursenm,

''', s.scores, null)) as ',

c.coursenm

)

)

from curriculum c

)

from student st

left join number_result s on st.stuid = s.stuid

left join curriculum c on c.courseno = s.courseno

group by st.stuid;

最终结果如下 ：

set @sql = null;

select

group_concat( distinct concat( ‘max(if(c.coursenm = ‘’’, c.coursenm, ‘’’, s.scores, 0)) as ‘’’, c.coursenm, ‘’’’ ) ) into @sql

from

curriculum c;

set @sql = concat( 'select st.stuid, st.stunm, ‘, @sql, ’ from student st

left join number_result s on st.stuid = s.stuid

left join curriculum c on c.courseno = s.courseno

group by st.stuid’ );

prepare stmt

from

@sql;

execute stmt;

deallocate prepare stmt;

这里简单说一下，先生命一个变量赋值为null，把拼接的查询sql赋值给声明的变量表中，也可以理解为生成一个临时表，把查询出来的数据放到临时表中。预定义一个语句，并将它赋给 stmt。

存储过程–动态行转列

用存储过程的好处是，方便我们调用，相当于一个函数，其他可能也是类似的查询不需再重复写代码，直接调存储过程就好，还能随心所欲的加上if条件判断。创建存储过程的语句我就不多写了，这里把上面的查询语句直接放到创建存储过程的begin和end直接就可以了，如下：

delimiter &&

drop procedure if exists sp_querydata;

create procedure sp_querydata(in stuid varchar(16))

reads sql data

begin

set @sql = null;

set @stuid = null;

select

group_concat(distinct

concat(

'max(if(c.coursenm = ''',

c.coursenm,

''', s.scores, 0)) as ''',

c.coursenm, '''

)

) into @sql

from curriculum c;

set @sql = concat('select st.stuid, st.stunm, ', @sql,

' from student st

left join number_result s on st.stuid = s.stuid

left join curriculum c on c.courseno = s.courseno');

if stuid is not null and stuid <> '' then

set @stuid = stuid;

set @sql = concat(@sql, ' where st.stuid = '', @stuid, ''');

end if;

set @sql = concat(@sql, ' group by st.stuid');

prepare stmt from @sql;

execute stmt;

deallocate prepare stmt;

end &&

delimiter ;