MySql 动态行转列整理

在开发过程中，我们或许经常碰到这样的需求，即将某些sql查询数据实现动态行转列。

举例来说：一个学生参加过多次考试，如果想知道该学生最近几次考试语文的成绩，如下图：

对于用户来说，我们希望看到的如下图（即数据动态实现行转列）：

下面看数据表结构：

CREATE TABLE `e_exam` (
  `exam_id` int(11) NOT NULL DEFAULT '0' COMMENT '考试名称',
  `exam_name` varchar(50) DEFAULT NULL,
  `schoolbaseinfo_id` varchar(36) DEFAULT NULL COMMENT '归属学校',
  `gradeinfo_id` varchar(36) DEFAULT NULL COMMENT '年级',
  `exam_date` date DEFAULT NULL,
  `status` varchar(10) DEFAULT NULL COMMENT '状态：new, open, close',
  `last_update_by` varchar(36) DEFAULT NULL,
  `last_update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`exam_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='考试信息表'

CREATE TABLE `e_exam_schedule` (
  `exam_schedule_id` int(11) NOT NULL DEFAULT '0',
  `exam_id` int(11) DEFAULT NULL,
  `subject_id` int(11) DEFAULT NULL COMMENT '学科',
  `subject_name` varchar(20) DEFAULT NULL,
  `full_marks` int(11) DEFAULT NULL COMMENT '卷面值(满分成绩)',
  `begin_time` datetime DEFAULT NULL COMMENT '开始时间',
  `end_time` datetime DEFAULT NULL COMMENT '结束时间',
  `last_update_by` varchar(36) DEFAULT NULL,
  `last_update_time` datetime DEFAULT NULL,
  `zuowen` tinyint(4) DEFAULT '0' COMMENT '是否含作文  0：不含   1： 含',
  `duration` int(11) DEFAULT NULL COMMENT '考试持续时间  分钟',
  PRIMARY KEY (`exam_schedule_id`),
  KEY `exam_id` (`exam_id`),
  CONSTRAINT `e_exam_schedule_ibfk_1` FOREIGN KEY (`exam_id`) REFERENCES `e_exam` (`exam_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `e_score` (
  `score_id` int(11) NOT NULL DEFAULT '0',
  `exam_schedule_id` int(11) DEFAULT NULL COMMENT '考试计划(学科)',
  `studentbaseinfo_id` varchar(36) DEFAULT NULL COMMENT '学生',
  `score` double DEFAULT NULL COMMENT '分数',
  `remark` varchar(20) DEFAULT NULL COMMENT '备注',
  `last_update_by` varchar(36) DEFAULT NULL,
  `last_update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`score_id`),
  KEY `exam_schedule_id` (`exam_schedule_id`),
  KEY `studentbaseinfo_id` (`studentbaseinfo_id`),
  CONSTRAINT `e_score_ibfk_1` FOREIGN KEY (`exam_schedule_id`) REFERENCES `e_exam_schedule` (`exam_schedule_id`),
  CONSTRAINT `e_score_ibfk_2` FOREIGN KEY (`studentbaseinfo_id`) REFERENCES `t_student` (`studentbaseinfo_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

下面是存储过程的实现：

DELIMITER $$

USE `homework_test`$$

DROP PROCEDURE IF EXISTS `score_statistical`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `score_statistical`(IN grade_id VARCHAR(50), IN subject_id INT, IN class_id VARCHAR(50), IN date_range INT)
BEGIN
	DECLARE $rowcnt INT; /*  row count */
	DECLARE $i INT;      /*  temp variable  */
	SET @EE='';          /*  the final column，such as: SUM(IF(exam_id=330573,score,0)) AS a330573,SUM(IF(exam_id=330574,score,0)) AS a330574,  */
	SET $i=1;
	SET @daterange= IF(date_range=1, ' ', ' t1.exam_date >= DATE_SUB(NOW(), INTERVAL 3 MONTH) AND ' ); 
	SET @table1=CONCAT('SELECT t4.studentbaseinfo_id AS studentbaseinfo_id,t4.xuhao,t4.real_name as real_name,IFNULL(t1.exam_id,\'average\') AS exam_id,AVG(t3.score) AS score FROM
		e_exam t1,e_exam_schedule t2,e_score t3,t_student t4 WHERE t2.exam_id=t1.exam_id AND 
		t2.exam_schedule_id = t3.exam_schedule_id AND t3.studentbaseinfo_id=t4.studentbaseinfo_id AND t1.status!=\'new\' AND',
		@daterange,    
		 't1.gradeinfo_id=\'', grade_id, '\' AND t2.subject_id=',subject_id, ' AND t4.classinfo_id=\'',class_id,  
		'\' GROUP BY t4.studentbaseinfo_id, t1.exam_id WITH ROLLUP HAVING t4.studentbaseinfo_id IS NOT NULL'); /*the final sql */
	
	/* count the row number */
	SELECT COUNT(DISTINCT(t.exam_id)) INTO $rowcnt FROM e_exam t,e_exam_schedule k WHERE t.exam_id=k.exam_id AND IF(date_range=1, 1=1, t.exam_date >= DATE_SUB(NOW(), INTERVAL 3 MONTH)) 
		AND t.gradeinfo_id=grade_id AND t.status!='new' AND k.subject_id=subject_id; 
	
	/* make up the final column */
	WHILE $i <= $rowcnt DO
		SET @i:=0;
		SELECT col FROM (SELECT (@i:=@i+1) AS iden, CONCAT(@EE,'SUM(IF(exam_id=', exam_id, ',score,0)) AS a',exam_id,',') AS col  
			FROM (SELECT DISTINCT(m.exam_id) FROM e_exam m,e_exam_schedule n WHERE m.exam_id=n.exam_id AND 
			IF(date_range=1, 1=1, m.exam_date>=DATE_SUB(NOW(), INTERVAL 3 MONTH)) AND m.gradeinfo_id=grade_id AND m.status!='new'
			AND n.subject_id=subject_id) AS tb)
			AS tb1 WHERE iden=$i INTO @EE; 
		SET $i:=$i+1; 
	END WHILE;
	/*			
	SELECT @EE:=CONCAT(@EE,'SUM(IF(exam_id=',exam_id,',score,0)) AS a',exam_id,',') FROM (
		SELECT DISTINCT(exam_id) FROM e_exam WHERE gradeinfo_id='F710895E-E648-483E-8C0B-76EAB79CCD17') A;  
	*/
	SET @QQ=CONCAT('SELECT xuhao, real_name,', @EE, 'SUM(IF(exam_id=\'average\',score, 0)) AS average FROM (', @table1,') tx GROUP BY tx.studentbaseinfo_id HAVING tx.studentbaseinfo_id IS NOT NULL');
     
    PREPARE stmt2 FROM @QQ;
    EXECUTE stmt2;
		
    END$$

DELIMITER ;

调用：

CALL `score_statistical`('F710895E-E648-483E-8C0B-76EAB79CCD17', 1, '7D2F41ED-2148-418E-85C9-5AFC43E21C37', 1)

下面是实现过程中参考的文章：

文章来源一(感谢ACMAIN_CHM的征集帖子)：

http://blog.csdn.net/acmain_chm/article/details/4283943

http://bbs.csdn.net/topics/310045927

http://bbs.csdn.net/topics/390275035

数据脚本：


CREATE TABLE `tx` (
  `id` int(11) NOT NULL,
  `c1` char(2) DEFAULT NULL,
  `c2` char(2) DEFAULT NULL,
  `c3` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

insert  into `tx`(`id`,`c1`,`c2`,`c3`) values (1,'A1','B1',9),(2,'A2','B1',7),(3,'A3','B1',4),(4,'A4','B1',2),(5,'A1','B2',2),(6,'A2','B2',9),(7,'A3','B2',8),(8,'A4','B2',5),(9,'A1','B3',1),(10,'A2','B3',8),(11,'A3','B3',8),(12,'A4','B3',6),(13,'A1','B4',8),(14,'A2','B4',2),(15,'A3','B4',6),(16,'A4','B4',9),(17,'A1','B4',3),(18,'A2','B4',5),(19,'A3','B4',2),(20,'A4','B4',5);

mysql> select * from tx;
+----+------+------+------+
| id | c1   | c2   | c3   |
+----+------+------+------+
|  1 | A1   | B1   |    9 |
|  2 | A2   | B1   |    7 |
|  3 | A3   | B1   |    4 |
|  4 | A4   | B1   |    2 |
|  5 | A1   | B2   |    2 |
|  6 | A2   | B2   |    9 |
|  7 | A3   | B2   |    8 |
|  8 | A4   | B2   |    5 |
|  9 | A1   | B3   |    1 |
| 10 | A2   | B3   |    8 |
| 11 | A3   | B3   |    8 |
| 12 | A4   | B3   |    6 |
| 13 | A1   | B4   |    8 |
| 14 | A2   | B4   |    2 |
| 15 | A3   | B4   |    6 |
| 16 | A4   | B4   |    9 |
| 17 | A1   | B4   |    3 |
| 18 | A2   | B4   |    5 |
| 19 | A3   | B4   |    2 |
| 20 | A4   | B4   |    5 |
+----+------+------+------+
20 rows in set (0.00 sec)

期望结果

+------+-----+-----+-----+-----+------+
|C1    |B1   |B2   |B3   |B4   |Total |
+------+-----+-----+-----+-----+------+
|A1    |9    |2    |1    |11   |23    |
|A2    |7    |9    |8    |7    |31    |
|A3    |4    |8    |8    |8    |28    |
|A4    |2    |5    |6    |14   |27    |
|Total |22   |24   |23   |40   |109   |
+------+-----+-----+-----+-----+------+

1. 利用SUM(IF()) 生成列 + WITH ROLLUP 生成汇总行,并利用 IFNULL将汇总行标题显示为 Total

mysql> SELECT
    ->     IFNULL(c1,'total') AS total,
    ->     SUM(IF(c2='B1',c3,0)) AS B1,
    ->     SUM(IF(c2='B2',c3,0)) AS B2,
    ->     SUM(IF(c2='B3',c3,0)) AS B3,
    ->     SUM(IF(c2='B4',c3,0)) AS B4,
    ->     SUM(IF(c2='total',c3,0)) AS total
    -> FROM (
    ->     SELECT c1,IFNULL(c2,'total') AS c2,SUM(c3) AS c3
    ->     FROM tx
    ->     GROUP BY c1,c2
    ->     WITH ROLLUP
    ->     HAVING c1 IS NOT NULL
    -> ) AS A
    -> GROUP BY c1
    -> WITH ROLLUP;
+-------+------+------+------+------+-------+
| total | B1   | B2   | B3   | B4   | total |
+-------+------+------+------+------+-------+
| A1    |    9 |    2 |    1 |   11 |    23 |
| A2    |    7 |    9 |    8 |    7 |    31 |
| A3    |    4 |    8 |    8 |    8 |    28 |
| A4    |    2 |    5 |    6 |   14 |    27 |
| total |   22 |   24 |   23 |   40 |   109 |
+-------+------+------+------+------+-------+
5 rows in set, 1 warning (0.00 sec)

 

 

2. 利用SUM(IF()) 生成列 + UNION 生成汇总行,并利用 IFNULL将汇总行标题显示为 Total
mysql> select c1,
    -> sum(if(c2='B1',C3,0)) AS B1,
    -> sum(if(c2='B2',C3,0)) AS B2,
    -> sum(if(c2='B3',C3,0)) AS B3,
    -> sum(if(c2='B4',C3,0)) AS B4,SUM(C3) AS TOTAL
    -> from tx
    -> group by C1
    -> UNION
    -> SELECT 'TOTAL',sum(if(c2='B1',C3,0)) AS B1,
    -> sum(if(c2='B2',C3,0)) AS B2,
    -> sum(if(c2='B3',C3,0)) AS B3,
    -> sum(if(c2='B4',C3,0)) AS B4,SUM(C3) FROM TX
    -> ;
+-------+------+------+------+------+-------+
| c1    | B1   | B2   | B3   | B4   | TOTAL |
+-------+------+------+------+------+-------+
| A1    |    9 |    2 |    1 |   11 |    23 |
| A2    |    7 |    9 |    8 |    7 |    31 |
| A3    |    4 |    8 |    8 |    8 |    28 |
| A4    |    2 |    5 |    6 |   14 |    27 |
| TOTAL |   22 |   24 |   23 |   40 |   109 |
+-------+------+------+------+------+-------+
5 rows in set (0.00 sec)

mysql>

 

3.  利用SUM(IF()) 生成列,直接生成结果不再利用子查询
mysql> select ifnull(c1,'total'),
    -> sum(if(c2='B1',C3,0)) AS B1,
    -> sum(if(c2='B2',C3,0)) AS B2,
    -> sum(if(c2='B3',C3,0)) AS B3,
    -> sum(if(c2='B4',C3,0)) AS B4,SUM(C3) AS TOTAL
    -> from tx
    -> group by C1 with rollup ;
+--------------------+------+------+------+------+-------+
| ifnull(c1,'total') | B1   | B2   | B3   | B4   | TOTAL |
+--------------------+------+------+------+------+-------+
| A1                 |    9 |    2 |    1 |   11 |    23 |
| A2                 |    7 |    9 |    8 |    7 |    31 |
| A3                 |    4 |    8 |    8 |    8 |    28 |
| A4                 |    2 |    5 |    6 |   14 |    27 |
| total              |   22 |   24 |   23 |   40 |   109 |
+--------------------+------+------+------+------+-------+
5 rows in set (0.00 sec)

mysql>

4. 动态，适用于列不确定情况，

mysql> SET @EE=''; 
mysql> SELECT @EE:=CONCAT(@EE,'SUM(IF(C2=/'',C2,'/'',',C3,0)) AS ',C2,',') FROM (SELECT DISTINCT C2 FROM TX) A;

 

mysql> SET @QQ=CONCAT('SELECT ifnull(c1,/'total/'),',LEFT(@EE,LENGTH(@EE)-1),' ,SUM(C3) AS TOTAL FROM TX GROUP BY C1 WITH ROLLUP');
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE stmt2 FROM @QQ;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE stmt2;
+--------------------+------+------+------+------+-------+
| ifnull(c1,'total') | B1   | B2   | B3   | B4   | TOTAL |
+--------------------+------+------+------+------+-------+
| A1                 |    9 |    2 |    1 |   11 |    23 |
| A2                 |    7 |    9 |    8 |    7 |    31 |
| A3                 |    4 |    8 |    8 |    8 |    28 |
| A4                 |    2 |    5 |    6 |   14 |    27 |
| total              |   22 |   24 |   23 |   40 |   109 |
+--------------------+------+------+------+------+-------+
5 rows in set (0.00 sec)

数据库中也可以用 CASE WHEN / DECODE 代替 IF

文章来源二(感谢hrb2008)：

http://blog.csdn.net/hrb2008/article/details/1899746

mysql 中设置为支持中文
vi etc / my.cnf   
[ mysqld ]    
# add
default - character - set   = gb2312   
建表
drop   table  table_name
create   table  table_name( mon  varchar ( 10 ),per  varchar ( 10 ),value  int )
插入数据
insert   into  table_name
select   ' 一月 ' , ' 张三 ' , 10   UNION   all  
select   ' 二月 ' , ' 李四 ' , 30   UNION   all  
select   ' 三月 ' , ' 王五 ' , 50   UNION   all  
select   ' 四月 ' , ' 陈六 ' , 60   UNION   all  
select   ' 五月 ' , ' 刘七 ' , 78   UNION   all  
select   ' 一月 ' , ' 陈八 ' , 90  

存储过程aa

DELIMITER $$


DROP PROCEDURE IF EXISTS `radius`.`aa`$$


CREATE DEFINER=`root`@`%` PROCEDURE `aa`()
BEGIN
#tangwf  2007-11-23
        declare $stm varchar(500);
      declare $rowcnt int;
      declare $mycnt int;
      declare $i int;
      set $mycnt = 0;
      set $i = 1;
       set $stm='select per';
      select count(distinct mon) into $rowcnt from table_name;
      WHILE $i<=$rowcnt DO
         set @i :=0;
          select col from (select (@i := @i +1 ) as iden,CONCAT($stm,',SUM(case mon when ''',mon,''' then value else 0 end) as ''',mon,'''') as col from (select distinct mon from table_name) as tb) as tb1  where iden=$i into $stm;
      Set $i:=$i+1;
      END WHILE;
       Set @stm=concat($stm,' from table_name group by per;');
     prepare s from @stm;
    execute s;
    deallocate prepare s;
        END$$

执行存储过程
call aa()
结果
per    一月    二月    三月    四月    五月
李四     0      30      0      0      0
刘七     0      0      0      0      78
王五     0      0      50      0      0
张三     10      0      0      0      0
陈六     0      0      0      60      0
陈八     90      0      0      0      0

文章来源三(感谢yueliangdao0608)：

http://blog.csdn.net/yueliangdao0608/article/details/2306766

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`sp_row_column_wrap`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_row_column_wrap`(IN $schema_name varchar(64),
IN $table_name varchar(64))
BEGIN
  declare cnt int(11);
  declare $table_rows int(11);
  declare i int(11);
  declare j int(11);
  declare s int(11);
  declare str varchar(255);
  -- Get the column number of the table
  select count(1) from information_schema.columns where table_schema=$schema_name andtable_name=$table_name into cnt;
  -- Get the row number of the table
  select table_rows from information_schema.tables where table_schema = $schema_name andtable_name=$table_name into $table_rows;
  -- Check whether the table exists or not
  drop table if exists test.temp;
  create table if not exists test.temp (`1` varchar(255) not null);
  -- loop1 start
  set i = 0;
  loop1:loop
    if i = $table_rows-1 then
      leave loop1;
    end if;
    set @stmt1 = concat('alter table test.temp add `',i+2,'` varchar(255) not null');
    prepare s1 from @stmt1;
    execute s1;
    deallocate prepare s1;
    set @stmt1 = '';
    set i = i + 1;
  end loop loop1;
  -- loop1 end;
  set s = 0;
  -- loop2 start
  loop2:loop
  -- leave loop2
    if s=cnt then
      leave loop2;
    end if;
    set @stmt2 = concat('select column_name from information_schema.columns where table_schema="',$schema_name,
                        '" and table_name="',$table_name,'" limit ',s,',1 into @temp;');
    prepare s2 from @stmt2;
    execute s2;
    deallocate prepare s2;
    set @stmt2 = '';
    set j=0;
    set str = ' select ';
    -- Loop3 start
    loop3:loop 
      if j = $table_rows then
        leave loop3;
      end if;
      set @stmt3 = concat('select ',@temp,' from ',$schema_name,'.',$table_name,' limit ',j,',1 into @temp2;');
      prepare s3 from @stmt3;
      execute s3;
      set str = concat(str,'"',@temp2,'"',',');
      deallocate prepare s3;
      set @stmt3 = '';
      set j = j+1;
    end loop loop3;
    set str = left(str,length(str)-1);
    -- insert new data into table
    set @stmt4 = concat('insert into test.temp',str,';');
    prepare s4 from @stmt4;
    execute s4;
    deallocate prepare s4;
    set @stmt4 = '';
    set s=s+1;
  end loop loop2;
END$$

DELIMITER ;



以下是测试结果：
======
select * from a;
select * from b;
select * from salary;

call sp_row_column_wrap('test','a');
select * from test.temp;
call sp_row_column_wrap('test','b');
select * from test.temp;
call sp_row_column_wrap('test','salary');
select * from test.temp;

query result(2 records)

aid	title
1	111
2	222

query result(3 records)

bid	aid	image	time
1	2	1.gif	2007-08-08
2	2	2.gif	2007-08-09
3	2	3.gif	2007-08-08

query result(7 records)

id	cost	des	Autoid
1	10	aaaa	1
1	15	bbbb	2
1	20	cccc	3
2	80	aaaa	4
2	100	bbbb	5
2	60	dddd	6
3	500	dddd	7

query result(2 records)

1	2
1	2
111	222

query result(4 records)

1	2	3
1	2	3
2	2	2
1.gif	2.gif	3.gif
2007-08-08	2007-08-09	2007-08-08

query result(4 records)

1	2	3	4	5	6	7
1	1	1	2	2	2	3
10	15	20	80	100	60	500
aaaa	bbbb	cccc	aaaa	bbbb	dddd	dddd
1	2	3	4	5	6	7

其他参考url：

http://stackoverflow.com/questions/17964078/mysql-query-to-dynamically-convert-rows-to-columns-on-the-basis-of-two-columns

http://dba.stackexchange.com/questions/47902/how-to-transpose-convert-rows-as-columns-in-mysql

http://hancang2010.blog.163.com/blog/static/1824602612011119101416357/ mysql prepare语句使用


http://blog.csdn.net/meirenxing/article/details/6590968 mysql 存储过程 要点
http://blog.csdn.net/meirenxing/article/details/6585700  mysql的prepare应用


mysql的left，right,substr,instr截取字符串，截取小数点float
http://hi.baidu.com/bcpxqz/item/c68d8435b5500c20b2c0c5b3


使用 GROUP BY WITH ROLLUP 改善统计性能
http://blog.csdn.net/id19870510/article/details/6254358


MySQL中的WITH ROLLUP
http://blog.csdn.net/lxz3000/article/details/6174678


关于Mysql group by和with rollup的用法小记
http://www.sphinxsearch.org/archives/100

mysql行转列  
http://blog.163.com/weizi_mm/blog/static/31554420122544629224/
http://bbs.csdn.net/topics/320204897
http://bbs.csdn.net/topics/310045927
http://blog.csdn.net/zhoushengchao/article/details/7321688
http://hi.baidu.com/fbuacikdutemp/item/ed4f960eccd47ad71ef04614
http://pmandy-163-com.iteye.com/blog/789326