MySQL 行轉(zhuǎn)列詳情
MySQL行轉(zhuǎn)列,對經(jīng)常處理數(shù)據(jù)的同學(xué)們來說,一定是不陌生的,甚至是印象深刻,因為它大概率困擾過你,讓你為之一愣~ 但當(dāng)你看到本文后,這個問題就不在是問題,及時收藏,以后誰再問你這個問題,直接甩他臉上,粘貼即用。
首先,我們看一下咱們的測試表數(shù)據(jù)和預(yù)期查詢的結(jié)果:
mysql> SELECT * FROM t_gaokao_score; +----+--------------+--------------+-------+ | id | student_name | subject ? ? ?| score | +----+--------------+--------------+-------+ | ?1 | 林磊兒 ? ? ? | 語文 ? ? ? ? | ? 148 | | ?2 | 林磊兒 ? ? ? | 數(shù)學(xué) ? ? ? ? | ? 150 | | ?3 | 林磊兒 ? ? ? | 英語 ? ? ? ? | ? 147 | | ?4 | 喬英子 ? ? ? | 語文 ? ? ? ? | ? 121 | | ?5 | 喬英子 ? ? ? | 數(shù)學(xué) ? ? ? ? | ? 106 | | ?6 | 喬英子 ? ? ? | 英語 ? ? ? ? | ? 146 | | ?7 | 方一凡 ? ? ? | 語文 ? ? ? ? | ? ?70 | | ?8 | 方一凡 ? ? ? | 數(shù)學(xué) ? ? ? ? | ? ?90 | | ?9 | 方一凡 ? ? ? | 英語 ? ? ? ? | ? ?59 | | 10 | 方一凡 ? ? ? | 特長加分 ? ? | ? 200 | | 11 | 陳哈哈 ? ? ? | 語文 ? ? ? ? | ? 109 | | 12 | 陳哈哈 ? ? ? | 數(shù)學(xué) ? ? ? ? | ? ?92 | | 13 | 陳哈哈 ? ? ? | 英語 ? ? ? ? | ? ?80 | +----+--------------+--------------+-------+ 13 rows in set (0.00 sec)
看看我們行轉(zhuǎn)列轉(zhuǎn)完后的結(jié)果:
+--------------+--------+--------+--------+--------------+ | student_name | 語文 ? | 數(shù)學(xué) ? | 英語 ? | 特長加分 ? ? | +--------------+--------+--------+--------+--------------+ | 林磊兒 ? ? ? | ? ?148 | ? ?150 | ? ?147 | ? ? ? ? ? ?0 | | 喬英子 ? ? ? | ? ?121 | ? ?106 | ? ?146 | ? ? ? ? ? ?0 | | 方一凡 ? ? ? | ? ? 70 | ? ? 90 | ? ? 59 | ? ? ? ? ?200 | | 陳哈哈 ? ? ? | ? ?109 | ? ? 92 | ? ? 80 | ? ? ? ? ? ?0 | +--------------+--------+--------+--------+--------------+ 4 rows in set (0.00 sec)
好,下面我們一起來看看SQL是如何編寫的,對了,創(chuàng)建表結(jié)構(gòu)和導(dǎo)入測試數(shù)據(jù)的SQL放到文章末尾了,自取~
一、行轉(zhuǎn)列SQL寫法
方法一、使用case..when..then進(jìn)行 行轉(zhuǎn)列
ELECT student_name, ?? ?SUM(CASE `subject` WHEN '語文' THEN score ELSE 0 END) as '語文', ?? ?SUM(CASE `subject` WHEN '數(shù)學(xué)' THEN score ELSE 0 END) as '數(shù)學(xué)', ?? ?SUM(CASE `subject` WHEN '英語' THEN score ELSE 0 END) as '英語', ?? ?SUM(CASE `subject` WHEN '特長加分' THEN score ELSE 0 END) as '特長加分'? FROM t_gaokao_score? GROUP BY student_name;
這里如果不使用SUM()
會報sql_mode=only_full_group_by
相關(guān)錯誤,需要聚合函數(shù)和group by
連用或使用distinct
才可以解決。
其實,加了SUM()是為了能夠使用GROUP BY
根據(jù)student_name
進(jìn)行分組,每一個student_name
對應(yīng)的subject="語文"的記錄畢竟只有一條,所以SUM() 的值就等于對應(yīng)那一條記錄的score
的值。當(dāng)然,也可以換成MAX()。
方法二、使用IF()進(jìn)行 行轉(zhuǎn)列:
ELECT student_name, ?? ?SUM(IF(`subject`='語文',score,0)) as '語文', ?? ?SUM(IF(`subject`='數(shù)學(xué)',score,0)) as '數(shù)學(xué)', ?? ?SUM(IF(`subject`='英語',score,0)) as '英語', ?? ?SUM(IF(`subject`='特長加分',score,0)) as '特長加分'? FROM t_gaokao_score? GROUP BY student_name;
該方法將IF(subject='語文',score,0)
作為條件,通過student_name
進(jìn)行分組,對分組后所有subject='語文’的記錄的score字段進(jìn)行SUM()操作,如果score沒有值則默認(rèn)為0。
這種方式和case..when..then方法原理相同,相比更加簡潔明了,建議使用。
二、如果領(lǐng)導(dǎo)@你,讓你在結(jié)果集中加上總數(shù)列呢?
友情提示:我們工作中處理行轉(zhuǎn)列數(shù)據(jù)時,盡量都把總數(shù)、平均數(shù)等加上,方便領(lǐng)導(dǎo)查閱,省得他循環(huán)BB你。
話說,你還記得上學(xué)時的成績表是啥樣的么?你一般從上往下看還是從下往上看呢?文末投票,快來給大家樂呵樂呵!
寫法:利用SUM(IF()) 生成列,WITH ROLLUP 生成匯總列和行,并利用 IFNULL將匯總行標(biāo)題顯示為總數(shù)
SELECT IFNULL(student_name,'總數(shù)') AS student_name, ?? ?SUM(IF(`subject`='語文',score,0)) AS '語文', ?? ?SUM(IF(`subject`='數(shù)學(xué)',score,0)) AS '數(shù)學(xué)', ?? ?SUM(IF(`subject`='英語',score,0)) AS '英語', ?? ?SUM(IF(`subject`='特長加分',score,0)) AS '特長加分', ?? ?SUM(score) AS '總數(shù)'? FROM t_gaokao_score GROUP BY student_name WITH ROLLUP;
查詢結(jié)果:
+--------------+--------+--------+--------+--------------+--------+ | student_name | 語文 ? | 數(shù)學(xué) ? | 英語 ? | 特長加分 ? ? | 總數(shù) ? | +--------------+--------+--------+--------+--------------+--------+ | 喬英子 ? ? ? | ? ?121 | ? ?106 | ? ?146 | ? ? ? ? ? ?0 | ? ?373 | | 方一凡 ? ? ? | ? ? 70 | ? ? 90 | ? ? 59 | ? ? ? ? ?200 | ? ?419 | | 林磊兒 ? ? ? | ? ?148 | ? ?150 | ? ?147 | ? ? ? ? ? ?0 | ? ?445 | | 陳哈哈 ? ? ? | ? ?113 | ? ?116 | ? ? 80 | ? ? ? ? ? ?0 | ? ?309 | | 總數(shù) ? ? ? ? | ? ?452 | ? ?462 | ? ?432 | ? ? ? ? ?200 | ? 1546 | +--------------+--------+--------+--------+--------------+--------+ 5 rows in set, 1 warning (0.00 sec)
三、領(lǐng)導(dǎo)又雙叒叕@你改需求
讓你把分值轉(zhuǎn)化為具體內(nèi)容顯示(優(yōu)秀、良好、普通、差),430分以上重點大學(xué),400分以上一本,350分及以上二本,350以下搬磚,該怎么寫呢?
這里我們就需要case when嵌套一下了,看著高大上,其實就是普通的嵌套而已。在第一層查出分組后的各科分?jǐn)?shù),在第二層替換成等級即可。
SELECT student_name, MAX( ? ? ? ? ? CASE subject ? ? ? ? ? WHEN '語文' THEN ? ? ? ? ? ? ? ( ? ? ? ? ? ? ? ? ? CASE ? ? ? ? ? ? ? ? ? WHEN score - (select avg(score) from t_gaokao_score where subject='語文') > 20 THEN ? ? ? ? ? ? ? ? ? ? ? '優(yōu)秀' ? ? ? ? ? ? ? ? ? WHEN score - (select avg(score) from t_gaokao_score where subject='語文') > 10 THEN ? ? ? ? ? ? ? ? ? ? ? '良好' ? ? ? ? ? ? ? ? ? WHEN score - (select avg(score) from t_gaokao_score where subject='語文') >= 0 THEN ? ? ? ? ? ? ? ? ? ? ? '普通' ? ? ? ? ? ? ? ? ? ELSE ? ? ? ? ? ? ? ? ? ? ? '差' ? ? ? ? ? ? ? ? ? END ? ? ? ? ? ? ? ) ? ? ? ? ? END ? ? ? ) as '語文',? MAX( ? ? ? ? ? CASE subject ? ? ? ? ? WHEN '數(shù)學(xué)' THEN ? ? ? ? ? ? ? ( ? ? ? ? ? ? ? ? ? CASE ? ? ? ? ? ? ? ? ? WHEN score - (select avg(score) from t_gaokao_score where subject='數(shù)學(xué)') > 20 THEN ? ? ? ? ? ? ? ? ? ? ? '優(yōu)秀' ? ? ? ? ? ? ? ? ? WHEN score - (select avg(score) from t_gaokao_score where subject='數(shù)學(xué)') > 10 THEN ? ? ? ? ? ? ? ? ? ? ? '良好' ? ? ? ? ? ? ? ? ? WHEN score - (select avg(score) from t_gaokao_score where subject='數(shù)學(xué)') >= 0 THEN ? ? ? ? ? ? ? ? ? ? ? '普通' ? ? ? ? ? ? ? ? ? ELSE ? ? ? ? ? ? ? ? ? ? ? '差' ? ? ? ? ? ? ? ? ? END ? ? ? ? ? ? ? ) ? ? ? ? ? END ? ? ? ) as '數(shù)學(xué)', MAX( ? ? ? ? ? CASE subject ? ? ? ? ? WHEN '英語' THEN ? ? ? ? ? ? ? ( ? ? ? ? ? ? ? ? ? CASE ? ? ? ? ? ? ? ? ? WHEN score - (select avg(score) from t_gaokao_score where subject='英語') > 20 THEN ? ? ? ? ? ? ? ? ? ? ? '優(yōu)秀' ? ? ? ? ? ? ? ? ? WHEN score - (select avg(score) from t_gaokao_score where subject='英語') > 10 THEN ? ? ? ? ? ? ? ? ? ? ? '良好' ? ? ? ? ? ? ? ? ? WHEN score - (select avg(score) from t_gaokao_score where subject='英語') >= 0 THEN ? ? ? ? ? ? ? ? ? ? ? '普通' ? ? ? ? ? ? ? ? ? ELSE ? ? ? ? ? ? ? ? ? ? ? '差' ? ? ? ? ? ? ? ? ? END ? ? ? ? ? ? ? ) ? ? ? ? ? END ? ? ? ) as '英語', SUM(score) as '總分', (CASE WHEN SUM(score) > 430 THEN '重點大學(xué)' ? ?? ? ?WHEN SUM(score) > 400 THEN '一本' ? ?? ? ?WHEN SUM(score) > 350 THEN '二本' ? ?? ? ?ELSE '工地搬磚'? ?? ? ?END ) as '結(jié)果' FROM t_gaokao_score? GROUP BY student_name? ORDER BY SUM(score) desc;
我們來看一下輸出結(jié)果:
+--------------+--------+--------+--------+--------+--------------+ | student_name | 語文 ? | 數(shù)學(xué) ? | 英語 ? | 總分 ? | 結(jié)果 ? ? ? ? | +--------------+--------+--------+--------+--------+--------------+ | 林磊兒 ? ? ? | 優(yōu)秀 ? | 優(yōu)秀 ? | 優(yōu)秀 ? | ? ?445 | 重點大學(xué) ? ? | | 方一凡 ? ? ? | 差 ? ? | 差 ? ? | 差 ? ? | ? ?419 | 一本 ? ? ? ? | | 喬英子 ? ? ? | 普通 ? | 差 ? ? | 優(yōu)秀 ? | ? ?373 | 二本 ? ? ? ? | | 陳哈哈 ? ? ? | 普通 ? | 普通 ? | 差 ? ? | ? ?309 | 工地搬磚 ? ? | +--------------+--------+--------+--------+--------+--------------+ 4 rows in set (0.00 sec)
過來人的經(jīng)驗來看,老實孩子最吃虧,早知道他娘的走藝體了~
四、結(jié)束語
好了,SQL方面就是以上這些內(nèi)容了,有疑問可以寫在評論區(qū),哈哥會在摸魚的時候回復(fù)你~~`
附錄:創(chuàng)建表結(jié)構(gòu)&測試數(shù)據(jù)SQL
表結(jié)構(gòu):
DROP TABLE IF EXISTS `t_gaokao_score`; CREATE TABLE `t_gaokao_score` ?( ? `id` int(0) NOT NULL AUTO_INCREMENT, ? `student_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '學(xué)生姓名', ? `subject` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '科目', ? `score` double NULL DEFAULT NULL COMMENT '成績', ? PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
導(dǎo)入測試數(shù)據(jù):
INSERT INTO `t_gaokao_score` VALUES? (1, '林磊兒', '語文', 148), (2, '林磊兒', '數(shù)學(xué)', 150), (3, '林磊兒', '英語', 147), (4, '喬英子', '語文', 121), (5, '喬英子', '數(shù)學(xué)', 106), (6, '喬英子', '英語', 146), (7, '方一凡', '語文', 70), (8, '方一凡', '數(shù)學(xué)', 90), (9, '方一凡', '英語', 59), (10, '方一凡', '特長加分', 200), (11, '陳哈哈', '語文', 109), (12, '陳哈哈', '數(shù)學(xué)', 92), (13, '陳哈哈', '英語', 80);
到此這篇關(guān)于MySQL 行轉(zhuǎn)列詳情的文章就介紹到這了,更多相關(guān)MySQL 行轉(zhuǎn)列內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
給MySQL表中的字段設(shè)置默認(rèn)值的兩種方法
在MySQL中,我們可以為表的字段設(shè)置默認(rèn)值,以確保在插入新記錄時,如果沒有為該字段指定值,將使用默認(rèn)值,要為MySQL表中的字段設(shè)置默認(rèn)值,我們可以在創(chuàng)建表時或者在已存在的表上使用ALTER TABLE語句進(jìn)行修改,下面將展示兩種設(shè)置默認(rèn)值的方法,需要的朋友可以參考下2023-11-11mysql 主從數(shù)據(jù)不一致,提示: Slave_SQL_Running: No 的解決方法
這篇文章主要介紹了mysql 主從數(shù)據(jù)不一致,提示: Slave_SQL_Running: No 的解決方法,總結(jié)分析了MySQL主從數(shù)據(jù)不一致的原因與常見處理技巧,需要的朋友可以參考下2020-02-02innodb_index_stats導(dǎo)入備份數(shù)據(jù)時報錯表主鍵沖突的解決方法
下面小編就為大家?guī)硪黄猧nnodb_index_stats導(dǎo)入備份數(shù)據(jù)時報錯表主鍵沖突的解決方法。小編覺得挺不錯的,現(xiàn)在就分享給大家,也給大家做個參考。一起跟隨小編過來看看吧2017-03-03在Hadoop集群環(huán)境中為MySQL安裝配置Sqoop的教程
這篇文章主要介紹了在Hadoop集群環(huán)境中為MySQL安裝配置Sqoop的教程,Sqoop一般被用于數(shù)據(jù)庫軟件之間的數(shù)據(jù)遷移,需要的朋友可以參考下2015-12-12總結(jié)MySQL建表、查詢優(yōu)化的一些實用小技巧
本篇文章是對MySQL建表以及查詢優(yōu)化的一些實用小技巧進(jìn)行了詳細(xì)的分析介紹,需要的朋友參考下2013-07-07ARM64架構(gòu)下安裝mysql5.7.22的全過程
這篇文章主要介紹了ARM64架構(gòu)下安裝mysql5.7.22的全過程,本文給大家介紹的非常詳細(xì),對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值,需要的朋友可以參考下2021-07-07