MySQL存儲過程中使用動態行轉列

本文介紹的實例成功的實現了動態行轉列。下面我以一個簡單的數據庫為例子，說明一下。

數據表結構

這里我用一個比較簡單的例子來說明，也是行轉列的經典例子，就是學生的成績
三張表：學生表、課程表、成績表

學生表
就簡單一點，學生學號、學生姓名兩個字段

CREATE TABLE `student` (  `stuid` VARCHAR(16) NOT NULL COMMENT '學號',  `stunm` VARCHAR(20) NOT NULL COMMENT '學生姓名',  PRIMARY KEY (`stuid`))COLLATE='utf8_general_ci'ENGINE=InnoDB;

課程表
課程編號、課程名

CREATE TABLE `courses` (  `courseno` VARCHAR(20) NOT NULL,  `coursenm` VARCHAR(100) NOT NULL,  PRIMARY KEY (`courseno`))COMMENT='課程表'COLLATE='utf8_general_ci'ENGINE=InnoDB;

成績表
學生學號、課程號、成績

CREATE TABLE `score` (  `stuid` VARCHAR(16) NOT NULL,  `courseno` VARCHAR(20) NOT NULL,  `scores` FLOAT NULL DEFAULT NULL,  PRIMARY KEY (`stuid`, `courseno`))COLLATE='utf8_general_ci'ENGINE=InnoDB;

以上就是數據庫表的結構了，這里沒有建立外鍵，但是根據表的結構，可以清楚的看到成績表中的學號和課程號是與學生表、課程表分別關聯起來的。

數據準備

/*學生表數據*/Insert Into student (stuid, stunm) Values('1001', '張三');Insert Into student (stuid, stunm) Values('1002', '李四');Insert Into student (stuid, stunm) Values('1003', '趙二');Insert Into student (stuid, stunm) Values('1004', '王五');Insert Into student (stuid, stunm) Values('1005', '劉青');Insert Into student (stuid, stunm) Values('1006', '周明');/*課程表數據*/Insert Into courses (courseno, coursenm) Values('C001', '大學語文');Insert Into courses (courseno, coursenm) Values('C002', '新視野英語');Insert Into courses (courseno, coursenm) Values('C003', '離散數學');Insert Into courses (courseno, coursenm) Values('C004', '概率論與數理統計');Insert Into courses (courseno, coursenm) Values('C005', '線性代數');Insert Into courses (courseno, coursenm) Values('C006', '高等數學(一)');Insert Into courses (courseno, coursenm) Values('C007', '高等數學(二)');/*成績表數據*/Insert Into score(stuid, courseno, scores) Values('1001', 'C001', 67);Insert Into score(stuid, courseno, scores) Values('1002', 'C001', 68);Insert Into score(stuid, courseno, scores) Values('1003', 'C001', 69);Insert Into score(stuid, courseno, scores) Values('1004', 'C001', 70);Insert Into score(stuid, courseno, scores) Values('1005', 'C001', 71);Insert Into score(stuid, courseno, scores) Values('1006', 'C001', 72);Insert Into score(stuid, courseno, scores) Values('1001', 'C002', 87);Insert Into score(stuid, courseno, scores) Values('1002', 'C002', 88);Insert Into score(stuid, courseno, scores) Values('1003', 'C002', 89);Insert Into score(stuid, courseno, scores) Values('1004', 'C002', 90);Insert Into score(stuid, courseno, scores) Values('1005', 'C002', 91);Insert Into score(stuid, courseno, scores) Values('1006', 'C002', 92);Insert Into score(stuid, courseno, scores) Values('1001', 'C003', 83);Insert Into score(stuid, courseno, scores) Values('1002', 'C003', 84);Insert Into score(stuid, courseno, scores) Values('1003', 'C003', 85);Insert Into score(stuid, courseno, scores) Values('1004', 'C003', 86);Insert Into score(stuid, courseno, scores) Values('1005', 'C003', 87);Insert Into score(stuid, courseno, scores) Values('1006', 'C003', 88);Insert Into score(stuid, courseno, scores) Values('1001', 'C004', 88);Insert Into score(stuid, courseno, scores) Values('1002', 'C004', 89);Insert Into score(stuid, courseno, scores) Values('1003', 'C004', 90);Insert Into score(stuid, courseno, scores) Values('1004', 'C004', 91);Insert Into score(stuid, courseno, scores) Values('1005', 'C004', 92);Insert Into score(stuid, courseno, scores) Values('1006', 'C004', 93);Insert Into score(stuid, courseno, scores) Values('1001', 'C005', 77);Insert Into score(stuid, courseno, scores) Values('1002', 'C005', 78);Insert Into score(stuid, courseno, scores) Values('1003', 'C005', 79);Insert Into score(stuid, courseno, scores) Values('1004', 'C005', 80);Insert Into score(stuid, courseno, scores) Values('1005', 'C005', 81);Insert Into score(stuid, courseno, scores) Values('1006', 'C005', 82);Insert Into score(stuid, courseno, scores) Values('1001', 'C006', 77);Insert Into score(stuid, courseno, scores) Values('1002', 'C006', 78);Insert Into score(stuid, courseno, scores) Values('1003', 'C006', 79);Insert Into score(stuid, courseno, scores) Values('1004', 'C006', 80);Insert Into score(stuid, courseno, scores) Values('1005', 'C006', 81);Insert Into score(stuid, courseno, scores) Values('1006', 'C006', 82);

為什么要行轉列

MySQL存儲過程中使用動態行轉列

這是我們進行成績查詢的時候看到的這種縱列的結果，但是一般的時候，我們想要看到下圖這種結果

MySQL存儲過程中使用動態行轉列

那么需要這樣的結果就要進行行轉列來操作了。

怎么行轉列

像得到上圖的結果，一般的行轉列，我們只需要這么做

靜態行轉列

Select st.stuid, st.stunm,   MAX(CASE c.coursenm WHEN '大學語文' THEN s.scores ELSE 0 END ) '大學語文',  MAX(CASE c.coursenm WHEN '新視野英語' THEN ifnull(s.scores,0) ELSE 0 END ) '新視野英語',   MAX(CASE c.coursenm WHEN '離散數學' THEN ifnull(s.scores,0) ELSE 0 END ) '離散數學',  MAX(CASE c.coursenm WHEN '概率論與數理統計' THEN ifnull(s.scores,0) ELSE 0 END ) '概率論與數理統計',  MAX(CASE c.coursenm WHEN '線性代數' THEN ifnull(s.scores,0) ELSE 0 END ) '線性代數',  MAX(CASE c.coursenm WHEN '高等數學(一)' THEN ifnull(s.scores,0) ELSE 0 END ) '高等數學(一)',  MAX(CASE c.coursenm WHEN '高等數學(二)' THEN ifnull(s.scores,0) ELSE 0 END ) '高等數學(二)'From Student stLeft Join score s On st.stuid = s.stuidLeft Join courses c On c.courseno = s.coursenoGroup by st.stuid

看上面的語句可以看出，我們是在知道固定的幾門課程之后，可以使用

MAX(CASE c.coursenm WHEN '線性代數' THEN ifnull(s.scores,0) ELSE 0 END ) '線性代數',

這樣的語句來實現行轉列

但我們都知道，課程不僅僅這幾門，如果用上面的語句去寫，第一要確定有多少課程，這么多課程的課程名要再拿出來，那樣的話寫一個查詢語句下來，可是要寫很多了。那么就想能不能動態進行行轉列的操作？答案當然是肯定的了！

動態行轉列

那么如何進行動態行轉列呢？

首先我們要動態獲取這樣的語句

MAX(CASE c.coursenm WHEN '大學語文' THEN s.scores ELSE 0 END ) '大學語文',MAX(CASE c.coursenm WHEN '線性代數' THEN ifnull(s.scores,0) ELSE 0 END ) '線性代數', MAX(CASE c.coursenm WHEN '離散數學' THEN ifnull(s.scores,0) ELSE 0 END ) '離散數學'

而不是像上面那樣一句句寫出來，那如何得到這樣的語句呢？

這里就要用到SQL語句拼接了。具體就是下面的語句

SELECT GROUP_CONCAT(DISTINCT  CONCAT(   'MAX(IF(c.coursenm = ''',   c.coursenm,   ''', s.scores, 0)) AS ''',   c.coursenm, ''''  ) )FROM courses c;

得到的結果就是

MAX(IF(c.coursenm = '大學語文', s.scores, 0)) AS '大學語文',MAX(IF(c.coursenm = '新視野英語', s.scores, 0)) AS '新視野英語',MAX(IF(c.coursenm = '離散數學', s.scores, 0)) AS '離散數學',MAX(IF(c.coursenm = '概率論與數理統計', s.scores, 0)) AS '概率論與數理統計',MAX(IF(c.coursenm = '線性代數', s.scores, 0)) AS '線性代數',MAX(IF(c.coursenm = '高等數學(一)', s.scores, 0)) AS '高等數學(一)',MAX(IF(c.coursenm = '高等數學(二)', s.scores, 0)) AS '高等數學(二)'

對，沒錯，就是我們上面進行行轉列查詢要用的語句，那樣就不用知道多少課程和這些課程的名字，只要這樣幾行代碼便可以得到動態的列了。

動態的列是拿到了，那如何再結合SQL語句進行查詢得到結果呢？
這里要說明一點，因為用到了拼接函數，如果像上面的查詢語句，只是把那幾行語句替換掉，也就是下面這樣

Select st.stuid, st.stunm, (  SELECT   GROUP_CONCAT(DISTINCT    CONCAT(     'MAX(IF(c.coursenm = ''',     c.coursenm,     ''', s.scores, NULL)) AS ',     c.coursenm    )   )  FROM courses c)From Student stLeft Join score s On st.stuid = s.stuidLeft Join courses c On c.courseno = s.coursenoGroup by st.stuid;

然而得到的結果卻是這樣的

MySQL存儲過程中使用動態行轉列

這里我就不多做贅述了，想必大家也明白。那么既然這樣不行，那該怎么做呢？

沒錯，這里就要像普通的那些語句那樣，進行聲明，將語句拼接完整之后，再執行，也就是下面這樣

SET @sql = NULL;SELECT GROUP_CONCAT(DISTINCT  CONCAT(   'MAX(IF(c.coursenm = ''',   c.coursenm,   ''', s.scores, 0)) AS ''',   c.coursenm, ''''  ) ) INTO @sqlFROM courses c;SET @sql = CONCAT('Select st.stuid, st.stunm, ', @sql,             ' From Student st             Left Join score s On st.stuid = s.stuid            Left Join courses c On c.courseno = s.courseno            Group by st.stuid');PREPARE stmt FROM @sql;EXECUTE stmt;DEALLOCATE PREPARE stmt;

直接執行這些語句，得到如下結果。

MySQL存儲過程中使用動態行轉列

沒錯，和開始的時候那種全部拼出來的語句一樣，這樣就實現了動態行轉列的目的了。而且我們不用知道多少課程，也無需把這些課程名一一列出來。

當然這個語句拼接中的查詢可以加入條件查詢，比如我們要查詢學號是1003的成績
也就是下面這樣

MySQL存儲過程中使用動態行轉列

語句則如下

SET @sql = NULL;SET @stuid = '1003';SELECT GROUP_CONCAT(DISTINCT  CONCAT(   'MAX(IF(c.coursenm = ''',   c.coursenm,   ''', s.scores, 0)) AS ''',   c.coursenm, ''''  ) ) INTO @sqlFROM courses c;SET @sql = CONCAT('Select st.stuid, st.stunm, ', @sql,             ' From Student st             Left Join score s On st.stuid = s.stuid            Left Join courses c On c.courseno = s.courseno            Where st.stuid = ''', @stuid, '''            Group by st.stuid');PREPARE stmt FROM @sql;EXECUTE stmt;DEALLOCATE PREPARE stmt;

對比前面的語句，我們可以看到在第二行的Left join后面我改了一些，還有就是前面的變量加了一個@stuid [ 注：這里的 @ 符號是在SQL語句定義變量習慣用法，我個人理解應該是用來區分吧！]

那么問題來了，行轉列的查詢已經實現了，怎么標題中還寫著存儲過程？對，沒錯，就是存儲過程！

像上面的語句，我們如果直接在MySQL中操作是沒問題的，但如果用到項目中，那么這個語句顯然我們沒法用，而且我這次做的項目是結合使用MyBatis，大家都知道在MyBatis中的XML文件中可以自己寫SQL語句，但是這樣的很顯然我們沒法放到XML文件中。

而且最關鍵的是，這里不能用 If 條件，好比我們要判斷學號是否為空或者等于0再加上條件進行查詢，可是這里不支持。
沒錯就是下面這樣

SET @sql = NULL;SET @stuid = '1003';SET @courseno = 'C002';SELECT GROUP_CONCAT(DISTINCT  CONCAT(   'MAX(IF(c.coursenm = ''',   c.coursenm,   ''', s.scores, 0)) AS ''',   c.coursenm, ''''  ) ) INTO @sqlFROM courses c;SET @sql = CONCAT('Select st.stuid, st.stunm, ', @sql,             ' From Student st             Left Join score s On st.stuid = s.stuid            Left Join courses c On c.courseno = s.courseno');            IF @stuid is not null and @stuid != 0 thenSET @sql = CONCAT(@sql, ' Where st.stuid = ''', @stuid, '''');END IF;  SET @sql = CONCAT(@sql, ' Group by st.stuid');PREPARE stmt FROM @sql;EXECUTE stmt;DEALLOCATE PREPARE stmt;

對，我就是加上 if 之后人家就是不支持，就是這么任性。

所以就要用到存儲過程啦，而且用存儲過程的好處是，方便我們調用，相當于一個函數，其他可能也是類似的查詢不需再重復寫代碼，直接調存儲過程就好，還能隨心所欲的加上if條件判斷，多么美好的事情，哈哈~。

那么說到存儲過程，這里該如何寫呢？
創建存儲過程的語句我就不多寫了，這里呢把上面的查詢語句直接放到創建存儲過程的begin和end直接就可以了，如下：

DELIMITER && drop procedure if exists SP_QueryData;Create Procedure SP_QueryData(IN stuid varchar(16))READS SQL DATA BEGINSET @sql = NULL;SET @stuid = NULL;SELECT GROUP_CONCAT(DISTINCT  CONCAT(   'MAX(IF(c.coursenm = ''',   c.coursenm,   ''', s.scores, 0)) AS ''',   c.coursenm, '/''  ) ) INTO @sqlFROM courses c;SET @sql = CONCAT('Select st.stuid, st.stunm, ', @sql,             ' From Student st             Left Join score s On st.stuid = s.stuid            Left Join courses c On c.courseno = s.courseno');            IF stuid is not null and stuid <> '' thenSET @stuid = stuid;SET @sql = CONCAT(@sql, ' Where st.stuid = /'', @stuid, '/'');END IF;  SET @sql = CONCAT(@sql, ' Group by st.stuid');PREPARE stmt FROM @sql;EXECUTE stmt;DEALLOCATE PREPARE stmt;END && DELIMITER ;

嗯，對比上面簡單的SQL語句可以看出，這里使用了 if 語句，對學號進行了判斷

不過這里要注意一點，這里的if語句不像我們平時java啊那種寫法也就是下面

if(條件)
{
要執行的語句塊
}
對，在SQL里面的if語句不一樣，不需要括號啊什么的，就像直接說英文一樣

IF @stuid is not null and @stuid != 0 thenSET @sql = CONCAT(@sql, ' Where st.stuid = ''', @stuid, '''');END IF;

嗯，就是這么簡單明了，如果條件滿足，那么就怎么樣，然后結束。

然后我們就可以傳參數調用這個SP了

CALL `SP_QueryData`('1001');

得到如下結果

MySQL存儲過程中使用動態行轉列

當然我們也可以直接傳個空串過去

CALL `SP_QueryData`('');

同樣得到我們想要的結果

MySQL存儲過程中使用動態行轉列

好了，以上就是這次我在MySQL進行動態行轉列的實現過程。

總結及問題

開始的時候，只想到要行轉列，寫著寫著突然發現要動態的，因為我不確定到底有多少列。
在網上各種找資料，然而看不太懂！

后來，參考了Pivot table with dynamic columns in MySQL這個，才寫出來的。

然后是各種問題，先是SQL語句中加入if條件，我像平時寫java那樣，發現并沒有什么用，網上也說就是這種

IF(stuid is not null && stuid <> '') thenSET @stuid = stuid;SET @sql = CONCAT(@sql, ' Where st.stuid = /'', @stuid, '/'');END IF;

可是我這么寫了之后并沒有什么用，還是報錯，找了不少之后才發現原來不是這么寫的，然后改了過來。

改完之后我以為可以了，可是，發現依舊不行。然后我就在想是不是這里不能用if判斷，因為不是一個function或者procedure，于是我就寫創建procedure的語句。

改造完之后，procedure成功的創建了。那創建完我就試試能不能，調用procedure之后，當當當當，結果出來了。

嗯，這個過程還是收獲很多的，對MySQL的行轉列，以及存儲過程，還有在SQL語句中的使用不一樣的地方等。
而且，這個行轉列的實現了之后，這個項目基本上沒啥大問題了對數據的處理，相當好啊，哈哈~

以上就是我在行轉列實現的過程中所有的內容，相對來說，我覺得，這里寫的很清楚很明了了，所以只要你有耐心看完并認真研究的話，這個內容對你的行轉列還是有很大裨益的。