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  '_')