《Mysql應用MySQL存儲過程中使用動態行轉列》要點：
本文介紹了Mysql應用MySQL存儲過程中使用動態行轉列，希望對您有用。如果有疑問，可以聯系我們。
MYSQL實例本文介紹的實例成功的實現了動態行轉列.下面我以一個簡單的數據庫為例子,說明一下.
MYSQL實例數據表結構
MYSQL實例這里我用一個比較簡單的例子來說明,也是行轉列的經典例子,就是學生的成績
三張表：學生表、課程表、成績表
MYSQL實例學生表
就簡單一點,學生學號、學生姓名兩個字段
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;
MYSQL實例課程表
課程編號、課程名
MYSQL實例
CREATE TABLE `courses` (
`courseno` VARCHAR(20) NOT NULL,
`coursenm` VARCHAR(100) NOT NULL,
PRIMARY KEY (`courseno`)
)
COMMENT='課程表'
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
MYSQL實例成績表
學生學號、課程號、成績
MYSQL實例
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;
MYSQL實例以上就是數據庫表的結構了,這里沒有建立外鍵,但是根據表的結構,可以清楚的看到成績表中的學號和課程號是與學生表、課程表分別關聯起來的.
MYSQL實例數據準備
MYSQL實例
/*學生表數據*/
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實例
MYSQL實例這是我們進行成績查詢的時候看到的這種縱列的結果,但是一般的時候,我們想要看到下圖這種結果
MYSQL實例
MYSQL實例那么需要這樣的結果就要進行行轉列來操作了.
MYSQL實例怎么行轉列
MYSQL實例像得到上圖的結果,一般的行轉列,我們只需要這么做
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 st
Left Join score s On st.stuid = s.stuid
Left Join courses c On c.courseno = s.courseno
Group by st.stuid
MYSQL實例看上面的語句可以看出,我們是在知道固定的幾門課程之后,可以使用
MYSQL實例
MAX(CASE c.coursenm WHEN '線性代數' THEN ifnull(s.scores,0) ELSE 0 END ) '線性代數',
MYSQL實例這樣的語句來實現行轉列
MYSQL實例但我們都知道,課程不僅僅這幾門,如果用上面的語句去寫,第一要確定有多少課程,這么多課程的課程名要再拿出來,那樣的話寫一個查詢語句下來,可是要寫很多了.那么就想能不能動態進行行轉列的操作?答案當然是肯定的了!
MYSQL實例動態行轉列
MYSQL實例那么如何進行動態行轉列呢?
MYSQL實例首先我們要動態獲取這樣的語句
MYSQL實例
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 ) '離散數學'
MYSQL實例而不是像上面那樣一句句寫出來,那如何得到這樣的語句呢?
MYSQL實例這里就要用到SQL語句拼接了.具體就是下面的語句
MYSQL實例
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'MAX(IF(c.coursenm = ''',
c.coursenm,
''', s.scores, 0)) AS ''',
c.coursenm, ''''
)
)
FROM courses c;
MYSQL實例得到的結果就是
MYSQL實例
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 '高等數學(二)'
MYSQL實例對,沒錯,就是我們上面進行行轉列查詢要用的語句,那樣就不用知道多少課程和這些課程的名字,只要這樣幾行代碼便可以得到動態的列了.
MYSQL實例動態的列是拿到了,那如何再結合SQL語句進行查詢得到結果呢?
這里要說明一點,因為用到了拼接函數,如果像上面的查詢語句,只是把那幾行語句替換掉,也就是下面這樣
MYSQL實例
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 st
Left Join score s On st.stuid = s.stuid
Left Join courses c On c.courseno = s.courseno
Group by st.stuid;
MYSQL實例然而得到的結果卻是這樣的
MYSQL實例
MYSQL實例這里我就不多做贅述了,想必大家也明白.那么既然這樣不行,那該怎么做呢?
MYSQL實例沒錯,這里就要像普通的那些語句那樣,進行聲明,將語句拼接完整之后,再執行,也就是下面這樣
MYSQL實例
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'MAX(IF(c.coursenm = ''',
c.coursenm,
''', s.scores, 0)) AS ''',
c.coursenm, ''''
)
) INTO @sql
FROM 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實例直接執行這些語句,得到如下結果.
MYSQL實例
MYSQL實例沒錯,和開始的時候那種全部拼出來的語句一樣,這樣就實現了動態行轉列的目的了.而且我們不用知道多少課程,也無需把這些課程名一一列出來.
MYSQL實例當然這個語句拼接中的查詢可以加入條件查詢,比如我們要查詢學號是1003的成績
也就是下面這樣
MYSQL實例
MYSQL實例語句則如下
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 @sql
FROM 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;
MYSQL實例對比前面的語句,我們可以看到在第二行的Left join后面我改了一些,還有就是前面的變量加了一個@stuid [ 注：這里的 @ 符號是在SQL語句定義變量習慣用法,我個人理解應該是用來區分吧!]
MYSQL實例那么問題來了,行轉列的查詢已經實現了,怎么標題中還寫著存儲過程?對,沒錯,就是存儲過程!
MYSQL實例像上面的語句,我們如果直接在MySQL中操作是沒問題的,但如果用到項目中,那么這個語句顯然我們沒法用,而且我這次做的項目是結合使用MyBatis,大家都知道在MyBatis中的XML文件中可以自己寫SQL語句,但是這樣的很顯然我們沒法放到XML文件中.
MYSQL實例而且最關鍵的是,這里不能用 If 條件,好比我們要判斷學號是否為空或者等于0再加上條件進行查詢,可是這里不支持.
沒錯就是下面這樣
MYSQL實例
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 @sql
FROM 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 then
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;
MYSQL實例對,我就是加上 if 之后人家就是不支持,就是這么任性.
MYSQL實例所以就要用到存儲過程啦,而且用存儲過程的好處是,方便我們調用,相當于一個函數,其他可能也是類似的查詢不需再重復寫代碼,直接調存儲過程就好,還能隨心所欲的加上if條件判斷,多么美好的事情,哈哈~.
MYSQL實例那么說到存儲過程,這里該如何寫呢?
創建存儲過程的語句我就不多寫了,這里呢把上面的查詢語句直接放到創建存儲過程的begin和end直接就可以了,如下：
MYSQL實例
DELIMITER &&
drop procedure if exists SP_QueryData;
Create Procedure SP_QueryData(IN stuid varchar(16))
READS SQL DATA
BEGIN
SET @sql = NULL;
SET @stuid = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'MAX(IF(c.coursenm = ''',
c.coursenm,
''', s.scores, 0)) AS ''',
c.coursenm, '\''
)
) INTO @sql
FROM 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 <> '' then
SET @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 ;
MYSQL實例嗯,對比上面簡單的SQL語句可以看出,這里使用了 if 語句,對學號進行了判斷
MYSQL實例不過這里要注意一點,這里的if語句不像我們平時java啊那種寫法也就是下面
MYSQL實例if(條件)
{
??? 要執行的語句塊
}
對,在SQL里面的if語句不一樣,不需要括號啊什么的,就像直接說英文一樣
MYSQL實例
IF @stuid is not null and @stuid != 0 then
SET @sql = CONCAT(@sql, ' Where st.stuid = ''', @stuid, '''');
END IF;
MYSQL實例?嗯,就是這么簡單明了,如果條件滿足,那么就怎么樣,然后結束.
MYSQL實例然后我們就可以傳參數調用這個SP了
MYSQL實例
CALL `SP_QueryData`('1001');
MYSQL實例得到如下結果
MYSQL實例
MYSQL實例當然我們也可以直接傳個空串過去
MYSQL實例
CALL `SP_QueryData`('');
MYSQL實例同樣得到我們想要的結果
MYSQL實例
MYSQL實例好了,以上就是這次我在MySQL進行動態行轉列的實現過程.
MYSQL實例總結及問題
MYSQL實例開始的時候,只想到要行轉列,寫著寫著突然發現要動態的,因為我不確定到底有多少列.
在網上各種找資料,然而看不太懂!
MYSQL實例后來,參考了Pivot table with dynamic columns in MySQL這個,才寫出來的.
MYSQL實例然后是各種問題,先是SQL語句中加入if條件,我像平時寫java那樣,發現并沒有什么用,網上也說就是這種
MYSQL實例
IF(stuid is not null && stuid <> '') then
SET @stuid = stuid;
SET @sql = CONCAT(@sql, ' Where st.stuid = \'', @stuid, '\'');
END IF;
MYSQL實例可是我這么寫了之后并沒有什么用,還是報錯,找了不少之后才發現原來不是這么寫的,然后改了過來.
MYSQL實例改完之后我以為可以了,可是,發現依舊不行.然后我就在想是不是這里不能用if判斷,因為不是一個function或者procedure,于是我就寫創建procedure的語句.
MYSQL實例改造完之后,procedure成功的創建了.那創建完我就試試能不能,調用procedure之后,當當當當,結果出來了.
MYSQL實例嗯,這個過程還是收獲很多的,對MySQL的行轉列,以及存儲過程,還有在SQL語句中的使用不一樣的地方等.
而且,這個行轉列的實現了之后,這個項目基本上沒啥大問題了對數據的處理,相當好啊,哈哈~
MYSQL實例以上就是我在行轉列實現的過程中所有的內容,相對來說,我覺得,這里寫的很清楚很明了了,所以只要你有耐心看完并認真研究的話,這個內容對你的行轉列還是有很大裨益的.
轉載請注明本頁網址：
http://www.snjht.com/jiaocheng/1178.html
