mysql createst_mysql行转列

//---------------------------------------------------------题1 start-------------------------------------------------------------

数据表：

CREATE TABLE `t_shcool` (

`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',

`userName` varchar(50) DEFAULT NULL COMMENT '姓名',

`course` varchar(50) DEFAULT NULL COMMENT '科目',

`score` int(20) DEFAULT NULL COMMENT '成绩',

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

insert into `t_shcool`

(`id`,`userName`,`course`,`score`)

values

(1,'张三','语文',66),

(2,'张三','数学',77),

(3,'张三','英文',88),

(4,'李四','语文',99),

(5,'李四','数学',100),

(6,'李四','英文',55),

(7,'王五','语文',44),

(8,'王五','数学',33),

(9,'马六','英文',22),

(10,'马六','语文',11);

如：

查询sql：

SELECT

userName AS '姓名',

SUM(CASE WHEN course='语文' THEN score ELSE 0 END) AS '语文',

SUM(CASE WHEN course='数学' THEN score ELSE 0 END) AS '数学',

SUM(CASE WHEN course='英文' THEN score ELSE 0 END) AS '英文'

FROM t_shcool GROUP BY userName

上面只能查询当前表，如果表中新增“物理”科目，则sql就无效了

//---------------------------------------------------------题1 end-------------------------------------------------------------

//---------------------------------------------------------题2 start-------------------------------------------------------------

CREATE TABLE `TabName` (

`Id` int(11) NOT NULL AUTO_INCREMENT,

`Name` varchar(20) DEFAULT NULL,

`Date` date DEFAULT NULL,

`Scount` int(11) DEFAULT NULL,

PRIMARY KEY (`Id`)

) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

INSERT INTO `TabName` VALUES ('1', '小说', '2013-09-01', '10000');

INSERT INTO `TabName` VALUES ('2', '微信', '2013-09-01', '20000');

INSERT INTO `TabName` VALUES ('3', '小说', '2013-09-02', '30000');

INSERT INTO `TabName` VALUES ('4', '微信', '2013-09-02', '35000');

INSERT INTO `TabName` VALUES ('5', '小说', '2013-09-03', '31000');

INSERT INTO `TabName` VALUES ('6', '微信', '2013-09-03', '36000');

INSERT INTO `TabName` VALUES ('7', '小说', '2013-09-04', '35000');

INSERT INTO `TabName` VALUES ('8', '微信', '2013-09-04', '38000');

-- ------------------------

-- 查看数据

-- ------------------------

SELECT * from TabName ;

-- ------------------------

-- 列转行统计数据

-- ------------------------

SELECT Date ,

MAX(CASE NAME WHEN '小说' THEN Scount ELSE 0 END ) 小说,

MAX(CASE NAME WHEN '微信' THEN Scount ELSE 0 END ) 微信

FROM TabName

GROUP BY Date

-- ------------------------

-- 行转列统计数据

-- ------------------------

select

Date, group_concat(NAME,'总量:',Scount) as b_str from TabName

group by Date

select Date,NAME, group_concat(NAME,'总量:',Scount) as b_str from TabName

group by Date ,NAME

//---------------------------------------------------------题2 end-------------------------------------------------------------

//---------------------------------------------------------题3 start-------------------------------------------------------------

CREATE TABLE `user` (

`name` varchar(50) DEFAULT NULL,

`subject` varchar(50) DEFAULT NULL,

`score` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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 ELSE 0 END) AS 'chinese',

SUM( CASE SUBJECT WHEN 'math' THEN score ELSE 0 END) AS 'math',

SUM( CASE SUBJECT WHEN 'english' THEN score ELSE 0 END) AS 'english'

FROM USER

GROUP BY 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 for select 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> 0 then

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=0 end repeat;

close test_cursor;

end if;

select DISTINCT * from tmp_tab;

drop table tmp_tab;

end

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

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

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

//---------------------------------------------------------题3 end-------------------------------------------------------------