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;
課程表
課程編號、課程名
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);
為何要行轉列
這是我們停止成就查詢的時刻看到的這類縱列的成果,然則普通的時刻,我們想要看到下圖這類成果
那末須要如許的成果就要停止行轉列來操作了。
怎樣行轉列
像獲得上圖的成果,普通的行轉列,我們只須要這麼做
靜態行轉列
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
看下面的語句可以看出,我們是在曉得固定的幾門課程以後,可使用
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 st Left Join score s On st.stuid = s.stuid Left Join courses c On c.courseno = s.courseno Group by st.stuid;
但是獲得的成果倒是如許的
這裡我就不多做贅述了,想必年夜家也明確。那末既然如許不可,那該怎樣做呢?
沒錯,這裡就要像通俗的那些語句那樣,停止聲明,將語句拼接完全以後,再履行,也就是上面如許
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;
直接履行這些語句,獲得以下成果。
沒錯,和開端的時刻那種全體拼出來的語句一樣,如許就完成了靜態行轉列的目標了。並且我們不消曉得若干課程,也無需把這些課程名逐個列出來。
固然這個語句拼接中的查詢可以參加前提查詢,好比我們要查詢學號是1003的成就
也就是上面如許
語句則以下
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;
比較後面的語句,我們可以看到在第二行的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 @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;
對,我就是加上 if 以後人家就是不支撐,就是這麼率性。
所以就要用到存儲進程啦,並且用存儲進程的利益是,便利我們挪用,相當於一個函數,其他能夠也是相似的查詢不需再反復寫代碼,直接調存儲進程就好,還能為所欲為的加上if前提斷定,何等美妙的工作,哈哈~。
那末說到存儲進程,這裡該若何寫呢?
創立存儲進程的語句我就不多寫了,這裡呢把下面的查詢語句直接放到創立存儲進程的begin和end直接便可以了,以下:
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 ;
嗯,比較下面簡略的SQL語句可以看出,這裡應用了 if 語句,對學號停止了斷定
不外這裡要留意一點,這裡的if語句不像我們日常平凡java啊那種寫法也就是上面
if(前提)
{
要履行的語句塊
}
對,在SQL外面的if語句紛歧樣,不須要括號啊甚麼的,就像直接說英文一樣
IF @stuid is not null and @stuid != 0 then SET @sql = CONCAT(@sql, ' Where st.stuid = ''', @stuid, ''''); END IF;
嗯,就是這麼簡略清楚明了,假如前提知足,那末就怎樣樣,然後停止。
然後我們便可以傳參數挪用這個SP了
CALL `SP_QueryData`('1001');
獲得以下成果
固然我們也能夠直接傳個空串曩昔
CALL `SP_QueryData`('');
異樣獲得我們想要的成果
好了,以上就是此次我在MySQL停止靜態行轉列的完成進程。
總結及成績
開端的時刻,只想到要行轉列,寫著寫著忽然發明要靜態的,由於我不肯定究竟有若干列。
在網上各類找材料,但是看不太懂!
後來,參考了Pivot table with dynamic columns in MySQL這個,才寫出來的。
然後是各類成績,先是SQL語句中參加if前提,我像日常平凡寫java那樣,發明並沒有甚麼用,網上也說就是這類
IF(stuid is not null && stuid <> '') then SET @stuid = stuid; SET @sql = CONCAT(@sql, ' Where st.stuid = \'', @stuid, '\''); END IF;
可是我這麼寫了以後並沒有甚麼用,照樣報錯,找了很多以後才發明本來不是這麼寫的,然後改了過去。
改完以後我認為可以了,可是,發明照舊不可。然後我就在想是否是這裡不克不及用if斷定,由於不是一個function或許procedure,因而我就寫創立procedure的語句。
改革完以後,procedure勝利的創立了。那創立完我就嘗嘗能不克不及,挪用procedure以後,铛铛铛铛,成果出來了。
嗯,這個進程照樣收成許多的,對MySQL的行轉列,和存儲進程,還有在SQL語句中的應用紛歧樣的處所等。
並且,這個行轉列的完成了以後,這個項目根本上沒啥年夜成績了對數據的處置,相當好啊,哈哈~
以上就是我外行轉列完成的進程中一切的內容,絕對來講,我認為,這裡寫的很清晰很清楚明了了,所以只需你有耐煩看完並賣力研討的話,這個內容對你的行轉列照樣有很年夜裨益的。