sql行轉(zhuǎn)列、列轉(zhuǎn)行的方法

如題：有一張表EMP，里面有兩個字段：name，chengji  有三條記錄，分別表示語文（name） 70分，數(shù)學(xué)（name） 80分，英語（name） 58分，請用一條sql查詢出這三條記錄并以條件顯示出來，大于等于80表示優(yōu)秀，大于等于60表示及格，小于60分表示不及格！要求顯示格式如上！

首先我們創(chuàng)建表，添加如題數(shù)據(jù)！

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

INSERT INTO emp VALUES('語文',70),('數(shù)學(xué)',80),('英語',58);

根據(jù)題目要求，我們需要將這三行的結(jié)果做判斷，然后以列的形式顯示，這其中有一個行轉(zhuǎn)列的操作。

第一種sql寫法:

SELECT MAX(CASE WHEN  NAME='語文' THEN (CASE WHEN chengji>=80  THEN '優(yōu)秀' WHEN   chengji<80 AND chengji>=60 THEN '及格' ELSE  '不及格' END)  ELSE '' END) '語文'  ,

MAX(CASE WHEN  NAME='數(shù)學(xué)' THEN (CASE WHEN chengji>=80  THEN '優(yōu)秀' WHEN   chengji<80 AND chengji>=60 THEN '及格' ELSE  '不及格' END)  ELSE '' END)  '數(shù)學(xué)',

MAX(CASE WHEN  NAME='英語' THEN (CASE WHEN chengji>=80  THEN '優(yōu)秀' WHEN   chengji<80 AND chengji>=60 THEN '及格' ELSE  '不及格' END) ELSE '' END ) '英語' FROM  emp

執(zhí)行結(jié)果如下：

  備注：上述sql中使用了max（case）這種用法，max這里的主要作用是為了在3次判斷中，取到不為空字符串''的標(biāo)題，語文，數(shù)學(xué)，英語！

 第二種寫法采用group_concat函數(shù)也是可以拼接出如圖所有要的結(jié)果寫法如下：

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

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

執(zhí)行結(jié)果如下：

這樣看，這個結(jié)果也還是可以接受， 這里采用了group_concat函數(shù)，將列的類容連接起來，作為行！不過這樣的結(jié)果有點生硬的感覺！

補充一點：這里的sql寫法我們可以看出，如果想要通過第一種寫法。我們前面必須要知道列的內(nèi)容如語文，數(shù)學(xué)，英語，但是第二種我們卻不用知道！ 這里我們想到了一種方法，通過存儲過程，將想要的第一種方法的sql拼出來，然后執(zhí)行這樣的話，后面如果我們的表再添加列，或者減少列，也不會報錯！

寫法如下：

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 ', '\'' ,'優(yōu)秀','\'' ,' 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 ;

調(diào)用一下： call yhtest（）；

插入幾條數(shù)據(jù)！我們假設(shè)提前不知道有多少個科目！

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

這里有個問題！由于我們在存儲過程中使用了group_concat函數(shù)，這個拼接函數(shù)最大拼接長度為1024（默認(rèn)） 超過固定長度，截斷處理！ 由數(shù)據(jù)庫參數(shù)group_concat_max_len 控制！我們可以根據(jù)需要認(rèn)為調(diào)整！

group_concat  調(diào)整拼接符號 ：group_concat(name  separator  '_')

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