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 select DISTINCT name from user;这样设置游标之后，执行存储过程，报错提示没有获取任何数据。小小同志跟我解释说，游标是遍历用的，怎么能distinct呢 ，只能对取数做distinct。

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

 

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