sql行转列、列转行的方法

创新互联自成立以来，一直致力于为企业提供从网站策划、网站设计、网站建设、成都网站制作、电子商务、网站推广、网站优化到为企业提供个性化软件开发等基于互联网的全面整合营销服务。公司拥有丰富的网站建设和互联网应用系统开发管理经验、成熟的应用系统解决方案、优秀的网站开发工程师团队及专业的网站设计师团队。

如题：有一张表EMP，里面有两个字段：name，chengji  有三条记录，分别表示语文（name） 70分，数学（name） 80分，英语（name） 58分，请用一条sql查询出这三条记录并以条件显示出来，大于等于80表示优秀，大于等于60表示及格，小于60分表示不及格！要求显示格式如上！

首先我们创建表，添加如题数据！

CREATE TABLE emp(NAME VARCHAR(20),chengji INT);

INSERT INTO emp VALUES('语文',70),('数学',80),('英语',58);

根据题目要求，我们需要将这三行的结果做判断，然后以列的形式显示，这其中有一个行转列的操作。

第一种sql写法:

SELECT MAX(CASE WHEN  NAME='语文' THEN (CASE WHEN chengji>=80  THEN '优秀' WHEN   chengji<80 AND chengji>=60 THEN '及格' ELSE  '不及格' END)  ELSE '' END) '语文'  ,

MAX(CASE WHEN  NAME='数学' THEN (CASE WHEN chengji>=80  THEN '优秀' WHEN   chengji<80 AND chengji>=60 THEN '及格' ELSE  '不及格' END)  ELSE '' END)  '数学',

MAX(CASE WHEN  NAME='英语' THEN (CASE WHEN chengji>=80  THEN '优秀' WHEN   chengji<80 AND chengji>=60 THEN '及格' ELSE  '不及格' END) ELSE '' END ) '英语' FROM  emp

执行结果如下：

  备注：上述sql中使用了max（case）这种用法，max这里的主要作用是为了在3次判断中，取到不为空字符串''的标题，语文，数学，英语！

 第二种写法采用group_concat函数也是可以拼接出如图所有要的结果写法如下：

SELECT GROUP_CONCAT(NAME SEPARATOR '|')  FROM   emp  UNION ALL   SELECT   

GROUP_CONCAT(CASE WHEN chengji>=80  THEN '优秀' WHEN   chengji<80 AND chengji>=60 THEN '及格' ELSE  '不及格' END   SEPARATOR '|' ) FROM  emp

执行结果如下：

这样看，这个结果也还是可以接受， 这里采用了group_concat函数，将列的类容连接起来，作为行！不过这样的结果有点生硬的感觉！

补充一点：这里的sql写法我们可以看出，如果想要通过第一种写法。我们前面必须要知道列的内容如语文，数学，英语，但是第二种我们却不用知道！ 这里我们想到了一种方法，通过存储过程，将想要的第一种方法的sql拼出来，然后执行这样的话，后面如果我们的表再添加列，或者减少列，也不会报错！

写法如下：

DELIMITER $$

USE `yhtest`$$

DROP PROCEDURE IF EXISTS `yhtest`$$

CREATE DEFINER=`root`@`%` PROCEDURE `yhtest`()

BEGIN

SET @sql = NULL;

SELECT

  GROUP_CONCAT(DISTINCT

    CONCAT(

       'MAX(CASE WHEN  NAME=','\'',emp.name,'\'','THEN (CASE WHEN chengji>=80  THEN ', '\'' ,'优秀','\'' ,' WHEN  

        chengji<80 AND chengji>=60 THEN ', '\'' ,'及格' ,'\'' ,' ELSE ', '\'' ,'不及格' ,'\'' ,' END)  ELSE ', '\'','\'',' END) ','\'',emp.name,'\'' 

    )

  )

INTO @sql

FROM emp ;

SET @sql = CONCAT('select  ',@sql, ' from  emp');

PREPARE stmt1 FROM @sql;

EXECUTE stmt1;

DEALLOCATE PREPARE stmt1;

  END$$

DELIMITER ;

调用一下： call yhtest（）；

插入几条数据！我们假设提前不知道有多少个科目！

INSERT INTO emp VALUES('物理',72),('体育',84);

这里有个问题！由于我们在存储过程中使用了group_concat函数，这个拼接函数最大拼接长度为1024（默认） 超过固定长度，截断处理！ 由数据库参数group_concat_max_len 控制！我们可以根据需要认为调整！

group_concat  调整拼接符号 ：group_concat(name  separator  '_')

group_concat  排序：group_concat(name order by name  separator  '_')