mysql行转列

最新推荐文章于 2018-05-15 09:29:26 发布

原创最新推荐文章于 2018-05-15 09:29:26 发布 · 253 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#MySQL

数据库专栏收录该内容

5 篇文章

订阅专栏

本文介绍了如何在MySQL中将数据行转换为列，通过使用CASE语句进行条件求和，以及创建存储过程动态地处理行转列的过程。示例包括一个SQL查询，该查询按姓名分组并显示语文、数学、英语的总分，以及一个存储过程，它遍历用户表，根据科目获取分数并存储到临时表中，最后展示临时表的内容。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

准备表和数据：

CREATE TABLE `user` (

`name` varchar(50) DEFAULT NULL,

`subject` varchar(50) DEFAULT NULL,

`score` int(11) DEFAULT NULL

)；

insert into user values

('zhangsan' , 'chinese' , 10),

('zhangsan' , 'math' , 20),

('zhangsan' , 'english' , 30),

('lily' , 'chinese' , 40),

('lily' , 'math' , 50),

('lily' , 'english' , 60),

('mini' , 'chinese' , 70),

('mini' , 'math' , 80),

('mini' , 'english' , 90);

case when 实现行转列

select name,
sum(case subject when'chinese'then score else0end)as'chinese',
sum(case subject when'math'then score else0end)as'math',
sum(case subject when'english'then score else0end)as'english'
from user
groupby name;

存储过程实现行转列

create procedure line_to_col()
begin
declare i int;
declare _chinese int;
declare _math int;
declare _english int;
declare _name varchar(10);
declare test_cursor CURSOR forselect name from user;
select count(*)into i from user;
CREATE TEMPORARY TABLE tmp_tab(
name varchar(10),
chinese_score int,
math_socre int,
english_score int);
if i>0then
open test_cursor;
repeat fetch test_cursor into _name;
select score into _chinese from user where subject ='chinese'and name =_name;
select score into _math from user where subject ='math'and name =_name;
select score into _english from user where subject ='english'and name =_name;
insert into tmp_tab values(_name,_chinese,_math,_english);
set i=i-1;
until i=0end repeat;
close test_cursor;
endif;
select DISTINCT *from tmp_tab;
drop table tmp_tab;
end

在写存储过程的时候遇到了两个问题，分别是关于游标和临时表。

因为user表中有重复的name，在设置游标时，我想直接过滤掉重复的用户，所以将游标设置成declare test_cursor CURSOR for select DISTINCT name from user;这样设置游标之后，执行存储过程，报错提示没有获取任何数据。小小同志跟我解释说，游标是遍历用的，怎么能distinct呢，只能对取数做distinct。

为了拼接输出内容，我建了一个临时表，第一次调用line_to_col的时候可以正常执行，第二次调用时就报错提示tmp_tab已存在。所以在存储过程中创建临时表，执行完后，需要及时把临时表删除掉，避免重复调用时出错。另外，小小跟我强调，临时表是放在内存里的，会耗资源，所以在用完之后需要及时删除掉。