程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> 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;

課程表
課程編號、課程名

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語句中的應用紛歧樣的處所等。
並且,這個行轉列的完成了以後,這個項目根本上沒啥年夜成績了對數據的處置,相當好啊,哈哈~

以上就是我外行轉列完成的進程中一切的內容,絕對來講,我認為,這裡寫的很清晰很清楚明了了,所以只需你有耐煩看完並賣力研討的話,這個內容對你的行轉列照樣有很年夜裨益的。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved