SQL 行转列查询的简易实现

开发中难免遇到一些非常灵活的设计,比如对SQL的行转列进行业务数据展现的.

最近在做系统设计的时候设计了一个公共对象的扩展表,然后需要实现不同业务对象的组合查询,因此借鉴了以前经典的学生科目成绩行转列的案例

在此自己做了修正,以供分享.

先晒图说效果

1、表的原始数据

 

2、转换后的数据

ok ,下面具体介绍如何实现

环境:mysql 5.5 

工具: SQLyogEnt

首先是创建的学生科目成绩表

CREATE TABLE `tb_subject` (
  `name` varchar(30) DEFAULT NULL,
  `Subject` varchar(30) DEFAULT NULL,
  `Result` varchar(30) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sex` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

然后插入一点数据

insert  into `tb_subject`(`name`,`Subject`,`Result`,`id`,`sex`) values ('张三','语文','85',1,NULL),('张三','数学','95',2,NULL),('张三','物理','90',3,NULL),('李四','语文','65',4,NULL),('李四','数学','100',5,NULL),('李四','物理','87',6,NULL),('李四','作文','优',7,NULL),('张三','作文','差',8,NULL);

之后的效果如下:

行转列的几种方式:

之类我采用了两种方式,直接SQL语句,另一种是通过存储过程

第一种,采用纯的sql语句,不过这种相对比较呆板

SELECT DISTINCT  a.name,
(SELECT result FROM tb_Subject b WHERE a.name=b.name AND b.SUBJECT='语文' ) AS '语文',
(SELECT result FROM tb_Subject b WHERE a.name=b.name AND b.SUBJECT='数学' ) AS '数学',
(SELECT result FROM tb_Subject b WHERE a.name=b.name AND b.SUBJECT='物理' ) AS '物理',
(SELECT result FROM tb_Subject b WHERE a.name=b.name AND b.SUBJECT='作文' ) AS '作文'
FROM tb_Subject a

第二种采用了创建存储过程方式:

DELIMITER &&
CREATE PROCEDURE row_column()
BEGIN
#课程名称
DECLARE sub_name VARCHAR(20);
#所有课程数量
DECLARE COUNT INT;
#计数器
DECLARE i INT DEFAULT 0;
#拼接SQL字符串
SET @s = 'SELECT a.name';
SET COUNT = (SELECT  COUNT(DISTINCT SUBJECT) FROM tb_Subject);
WHILE i < COUNT DO
SET sub_name = (SELECT  DISTINCT SUBJECT FROM tb_Subject LIMIT i,1);
# SET @s = CONCAT(@s, ', CASE  SUBJECT WHEN  ','\'', sub_name,'\'',' THEN result END',' AS ','\'',sub_name,'\'');

SET @s = CONCAT(@s, ',(select result from tb_Subject b where a.name=b.name and b.SUBJECT=','\'', sub_name,'\'',') AS \'',sub_name,'\'');
#用于调试
# SELECT @s;
SET i = i+1;
END WHILE;
SET @s = CONCAT(@s, ' FROM tb_Subject a GROUP BY a.name');
#用于调试
#SELECT @s;
PREPARE st FROM @s;
EXECUTE st;
END
&& CALL row_column();

以上两种最终的执行效果一样,如下:

ok,暂且整理到这里吧,希望能帮到有需要的朋友.