MySQL動(dòng)態(tài)行轉(zhuǎn)列(列值轉(zhuǎn)換列名)

作者：畢濤濤，致力于Java學(xué)習(xí)的踐行者笆呆。原創(chuàng)文章衷戈，轉(zhuǎn)載請(qǐng)注明出處。
參考鏈接：https://blog.csdn.net/wqc19920906/article/details/79791322

一稠通、創(chuàng)建表結(jié)構(gòu)衬衬、插入數(shù)據(jù)

1、三種表：學(xué)生表改橘、課程表滋尉、分?jǐn)?shù)表

-- 學(xué)生表
CREATE TABLE `student` (
    `stuid` VARCHAR(16) NOT NULL COMMENT '學(xué)號(hào)',
    `stunm` VARCHAR(20) NOT NULL COMMENT '學(xué)生姓名',
    PRIMARY KEY (`stuid`)
) COMMENT '學(xué)生表';
-- 課程表
CREATE TABLE `courses` (
    `courseno` VARCHAR(20) NOT NULL,
    `coursenm` VARCHAR(100) NOT NULL,
    PRIMARY KEY (`courseno`)
) COMMENT '課程表';
-- 成績(jī)表
CREATE TABLE `score` (
    `stuid` VARCHAR(16) NOT NULL,
    `courseno` VARCHAR(20) NOT NULL,
    `scores` FLOAT NULL DEFAULT NULL,
    PRIMARY KEY (`stuid`, `courseno`)
) COMMENT '成績(jī)表';

2、插入數(shù)據(jù)

/*學(xué)生表數(shù)據(jù)*/
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', '周明');

/*課程表數(shù)據(jù)*/
Insert Into courses (courseno, coursenm) Values('C001', '大學(xué)語文');
Insert Into courses (courseno, coursenm) Values('C002', '新視野英語');
Insert Into courses (courseno, coursenm) Values('C003', '離散數(shù)學(xué)');
Insert Into courses (courseno, coursenm) Values('C004', '概率論與數(shù)理統(tǒng)計(jì)');
Insert Into courses (courseno, coursenm) Values('C005', '線性代數(shù)');
Insert Into courses (courseno, coursenm) Values('C006', '高等數(shù)學(xué)(一)');

/*成績(jī)表數(shù)據(jù)*/
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);

二飞主、為什么要行轉(zhuǎn)列

1狮惜、查詢學(xué)生姓名、對(duì)應(yīng)課程以及相應(yīng)的分?jǐn)?shù)

SELECT
    A.stuid,
    A.stunm,
    B.coursenm,
    C.scores 
FROM
    student A
    INNER JOIN score C ON A.stuid = C.stuid
    INNER JOIN courses B ON C.courseno = B.courseno;

成績(jī)單1.png

2既棺、行轉(zhuǎn)列后的結(jié)果

上面是進(jìn)行成績(jī)查詢的時(shí)候看到的縱列的結(jié)果讽挟，但是我們想要的是下面的結(jié)果，那么需要這樣的結(jié)果就要進(jìn)行行轉(zhuǎn)列來操作了丸冕。

成績(jī)單2.png

三耽梅、實(shí)現(xiàn)過程

1、靜態(tài)行轉(zhuǎn)列

SELECT st.stuid, st.stunm, 
    SUM(CASE c.coursenm WHEN '大學(xué)語文' THEN ifnull(s.scores,0) ELSE 0 END ) '大學(xué)語文',
    SUM(CASE c.coursenm WHEN '新視野英語' THEN ifnull(s.scores,0) ELSE 0 END ) '新視野英語', 
    SUM(CASE c.coursenm WHEN '離散數(shù)學(xué)' THEN ifnull(s.scores,0) ELSE 0 END ) '離散數(shù)學(xué)',
    SUM(CASE c.coursenm WHEN '概率論與數(shù)理統(tǒng)計(jì)' THEN ifnull(s.scores,0) ELSE 0 END ) '概率論與數(shù)理統(tǒng)計(jì)',
    SUM(CASE c.coursenm WHEN '線性代數(shù)' THEN ifnull(s.scores,0) ELSE 0 END ) '線性代數(shù)',
    SUM(CASE c.coursenm WHEN '高等數(shù)學(xué)(一)' THEN ifnull(s.scores,0) ELSE 0 END ) '高等數(shù)學(xué)(一)'
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;

看上面的語句可以看出胖烛，我們是在知道固定的幾門課程之后眼姐，可以使用這樣的語句來實(shí)現(xiàn)行轉(zhuǎn)列。

SUM(CASE c.coursenm WHEN '大學(xué)語文' THEN ifnull(s.scores,0) ELSE 0 END ) '大學(xué)語文',

$\color{red}{這里使用了SUM聚合函數(shù)}$ 與后面的GROUP BY 語句結(jié)合使用佩番，主要目的是為了消除階梯狀數(shù)據(jù)众旗。（關(guān)聯(lián)學(xué)生表，分?jǐn)?shù)表后趟畏，你的結(jié)果集會(huì)和分?jǐn)?shù)表?xiàng)l數(shù)一致）

階梯狀數(shù)據(jù).png

但我們都知道贡歧，課程不僅僅這幾門，如果用上面的語句去寫赋秀，第一要確定有多少課程利朵，這么多課程的課程名要再拿出來，那樣的話寫一個(gè)查詢語句下來猎莲，可是要寫很多了绍弟。那么就想能不能動(dòng)態(tài)進(jìn)行行轉(zhuǎn)列的操作？答案當(dāng)然是肯定的了著洼！

2樟遣、動(dòng)態(tài)行轉(zhuǎn)列

那么如何進(jìn)行動(dòng)態(tài)行轉(zhuǎn)列呢而叼？首先我們要?jiǎng)討B(tài)獲取這樣的語句

    MAX(CASE c.coursenm WHEN '大學(xué)語文' THEN ifnull(s.scores,0) ELSE 0 END ) '大學(xué)語文',
    MAX(CASE c.coursenm WHEN '新視野英語' THEN ifnull(s.scores,0) ELSE 0 END ) '新視野英語', 
    MAX(CASE c.coursenm WHEN '離散數(shù)學(xué)' THEN ifnull(s.scores,0) ELSE 0 END ) '離散數(shù)學(xué)',

而不是像上面那樣一句句寫出來，那如何得到這樣的語句呢豹悬？這里就要用到SQL語句拼接了葵陵。具體就是下面的語句：

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

得到的結(jié)果就是

SQL拼接.png

對(duì)，沒錯(cuò)屿衅，就是我們上面進(jìn)行行轉(zhuǎn)列查詢要用的語句埃难，那樣就不用知道多少課程和這些課程的名字，只要這樣幾行代碼便可以得到動(dòng)態(tài)的列了涤久。
動(dòng)態(tài)的列是拿到了涡尘，那如何再結(jié)合SQL語句進(jìn)行查詢得到結(jié)果呢？
這里要說明一點(diǎn)响迂，因?yàn)橛玫搅似唇雍瘮?shù)考抄，如果像上面的查詢語句，只是把那幾行語句替換掉蔗彤，也就是下面這樣：

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;

然而事與愿違川梅，得到的結(jié)果卻是這樣的

事與愿違的結(jié)果.png

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

沒錯(cuò)待侵，這里就要像普通的那些語句那樣丢早，進(jìn)行聲明，將語句拼接完整之后秧倾，再執(zhí)行怨酝，也就是下面這樣：

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(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;

直接執(zhí)行這些語句，得到如下結(jié)果:

期望的結(jié)果.png

當(dāng)然這個(gè)語句拼接中的查詢可以加入條件查詢那先，比如我們要查詢學(xué)號(hào)是1003的成績(jī)
也就是下面這樣：

1003學(xué)生成績(jī).png

語句則如下：

SET @sql = NULL;
SET @stuid = '1003';
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(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;

對(duì)比前面的語句农猬，我們可以看到在第二行的LEFT JOIN 后面我改了一些，還有就是前面的變量加了一個(gè)@stuid [ 注：這里的 @ 符號(hào)是在SQL語句定義變量習(xí)慣用法售淡，我個(gè)人理解應(yīng)該是用來區(qū)分吧斤葱！]

像上面的語句，我們?nèi)绻苯釉贛ySQL中操作是沒問題的揖闸，但如果用到項(xiàng)目中苦掘，那么這個(gè)語句顯然我們沒法用，而且我這次做的項(xiàng)目是結(jié)合使用MyBatis楔壤，大家都知道在MyBatis中的XML文件中可以自己寫SQL語句，但是這樣的很顯然我們沒法放到XML文件中惯驼。

而且最關(guān)鍵的是蹲嚣，這里不能用 If 條件递瑰，好比我們要判斷學(xué)號(hào)是否為空或者等于0再加上條件進(jìn)行查詢，可是這里不支持隙畜。

沒錯(cuò)就是下面這樣

SET @sql = NULL;
SET @stuid = '1003';
SET @courseno = 'C002';
 
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(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;

對(duì)抖部，我就是加上 if 之后人家就是不支持，就是這么任性议惰。

所以就要用到存儲(chǔ)過程啦慎颗，而且用存儲(chǔ)過程的好處是，方便我們調(diào)用言询，相當(dāng)于一個(gè)函數(shù)俯萎，其他學(xué)生也是類似的查詢不需再重復(fù)寫代碼，直接調(diào)存儲(chǔ)過程就好运杭，還能隨心所欲的加上if條件判斷夫啊，多么美好的事情，哈哈~辆憔。

那么說到存儲(chǔ)過程撇眯，這里該如何寫呢？
創(chuàng)建存儲(chǔ)過程的語句我就不多寫了虱咧，這里呢把上面的查詢語句直接放到創(chuàng)建存儲(chǔ)過程的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(
      'SUM(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;
 
END &&  
DELIMITER ;

嗯，對(duì)比上面簡(jiǎn)單的SQL語句可以看出腕巡，這里使用了 if 語句玄坦，對(duì)學(xué)號(hào)進(jìn)行了判斷

不過這里要注意一點(diǎn)，這里的if語句不像我們平時(shí)Java那種寫法逸雹，也就是下面

if(條件)
{
    要執(zhí)行的語句塊
}

對(duì)营搅，在SQL里面的if語句不一樣，不需要括號(hào)啊什么的梆砸，就像直接說英文一樣

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

嗯转质，就是這么簡(jiǎn)單明了，如果條件滿足帖世，那么就去調(diào)用休蟹，然后結(jié)束。

然后我們就可以傳參數(shù)調(diào)用這個(gè)SP了日矫。

CALL `SP_QueryData`('1001');

同樣得到我們想要的結(jié)果：

期望的結(jié)果.png

好了赂弓，以上就是這次我在mysql進(jìn)行動(dòng)態(tài)行轉(zhuǎn)列的實(shí)現(xiàn)過程。

最后編輯于：2020.05.30 15:14:08

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者

人面猴
序言：七十年代末哪轿，一起剝皮案震驚了整個(gè)濱河市盈魁，隨后出現(xiàn)的幾起案子，更是在濱河造成了極大的恐慌窃诉，老刑警劉巖杨耙，帶你破解...
沈念sama閱讀 216,470評(píng)論 6贊 501
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件赤套，死亡現(xiàn)場(chǎng)離奇詭異，居然都是意外死亡珊膜，警方通過查閱死者的電腦和手機(jī)容握，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 92,393評(píng)論 3贊 392
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進(jìn)店門，熙熙樓的掌柜王于貴愁眉苦臉地迎上來车柠，“玉大人剔氏，你說我怎么就攤上這事≈竦唬” “怎么了谈跛？”我有些...
開封第一講書人閱讀 162,577評(píng)論 0贊 353
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵，是天一觀的道長(zhǎng)溶褪。經(jīng)常有香客問我币旧，道長(zhǎng)，這世上最難降的妖魔是什么猿妈？我笑而不...
開封第一講書人閱讀 58,176評(píng)論 1贊 292
?港島之戀（遺憾婚禮）
正文為了忘掉前任吹菱，我火速辦了婚禮，結(jié)果婚禮上彭则，老公的妹妹穿的比我還像新娘鳍刷。我一直安慰自己，他們只是感情好俯抖，可當(dāng)我...
茶點(diǎn)故事閱讀 67,189評(píng)論 6贊 388
惡毒庶女頂嫁案：這布局不是一般人想出來的
文/花漫我一把揭開白布输瓜。她就那樣靜靜地躺著，像睡著了一般芬萍。火紅的嫁衣襯著肌膚如雪尤揣。梳的紋絲不亂的頭發(fā)上，一...
開封第一講書人閱讀 51,155評(píng)論 1贊 299
城市分裂傳說
那天柬祠，我揣著相機(jī)與錄音北戏，去河邊找鬼。笑死漫蛔，一個(gè)胖子當(dāng)著我的面吹牛嗜愈，可吹牛的內(nèi)容都是我干的。我是一名探鬼主播莽龟，決...
沈念sama閱讀 40,041評(píng)論 3贊 418
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開眼蠕嫁，長(zhǎng)吁一口氣：“原來是場(chǎng)噩夢(mèng)啊……” “哼！你這毒婦竟也來了毯盈？” 一聲冷哼從身側(cè)響起剃毒，我...
開封第一講書人閱讀 38,903評(píng)論 0贊 274
萬榮殺人案實(shí)錄
序言：老撾萬榮一對(duì)情侶失蹤，失蹤者是張志新（化名）和其女友劉穎，沒想到半個(gè)月后迟赃，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體陪拘，經(jīng)...
沈念sama閱讀 45,319評(píng)論 1贊 310
?護(hù)林員之死
正文獨(dú)居荒郊野嶺守林人離奇死亡，尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點(diǎn)故事閱讀 37,539評(píng)論 2贊 332
?白月光啟示錄
正文我和宋清朗相戀三年纤壁，在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了。大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片捺信。...
茶點(diǎn)故事閱讀 39,703評(píng)論 1贊 348
活死人
序言：一個(gè)原本活蹦亂跳的男人離奇死亡酌媒，死狀恐怖，靈堂內(nèi)的尸體忽然破棺而出迄靠，到底是詐尸還是另有隱情秒咨，我是刑警寧澤，帶...
沈念sama閱讀 35,417評(píng)論 5贊 343
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布掌挚，位于F島的核電站雨席，受9級(jí)特大地震影響，放射性物質(zhì)發(fā)生泄漏吠式。R本人自食惡果不足惜陡厘，卻給世界環(huán)境...
茶點(diǎn)故事閱讀 41,013評(píng)論 3贊 325
男人毒藥：我在死后第九天來索命
文/蒙蒙一、第九天我趴在偏房一處隱蔽的房頂上張望特占。院中可真熱鬧糙置，春花似錦、人聲如沸是目。這莊子的主人今日做“春日...
開封第一講書人閱讀 31,664評(píng)論 0贊 22
一樁弒父案，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽懊纳。三九已至揉抵，卻和暖如春，著一層夾襖步出監(jiān)牢的瞬間嗤疯，已是汗流浹背冤今。一陣腳步聲響...
開封第一講書人閱讀 32,818評(píng)論 1贊 269
情欲美人皮
我被黑心中介騙來泰國(guó)打工，沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留身弊，地道東北人辟汰。一個(gè)月前我還...
沈念sama閱讀 47,711評(píng)論 2贊 368
代替公主和親
正文我出身青樓，卻偏偏與公主長(zhǎng)得像阱佛，于是被迫代替她去往敵國(guó)和親帖汞。傳聞我的和親對(duì)象是個(gè)殘疾皇子，可洞房花燭夜當(dāng)晚...
茶點(diǎn)故事閱讀 44,601評(píng)論 2贊 353