一文弄懂MYSQL如何列轉行
一、需求:
有三張表,學生表、成績表和課程表,我們可以通過連表查詢出學生姓名、課程及對應的成績: 所需表sql
-- ---------------------------- -- Table structure for student -- ---------------------------- DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `s_id` varchar(20) NOT NULL DEFAULT '', `s_name` varchar(20) NOT NULL DEFAULT '', `s_birth` varchar(20) NOT NULL DEFAULT '', `s_sex` varchar(10) NOT NULL DEFAULT '', PRIMARY KEY (`s_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of student -- ---------------------------- INSERT INTO `student` VALUES ('01', '趙雷', '1990-01-01', '男'); INSERT INTO `student` VALUES ('02', '錢電', '1990-12-21', '男'); INSERT INTO `student` VALUES ('03', '孫風', '1990-05-20', '男'); INSERT INTO `student` VALUES ('04', '李雲', '1990-08-06', '男'); INSERT INTO `student` VALUES ('05', '周梅', '1991-12-01', '女'); INSERT INTO `student` VALUES ('06', '吳蘭', '1992-03-01', '女'); INSERT INTO `student` VALUES ('07', '鄭竹', '1989-07-01', '女'); INSERT INTO `student` VALUES ('08', '王菊', '1990-01-20', '女'); -- ---------------------------- -- Table structure for course -- ---------------------------- DROP TABLE IF EXISTS `course`; CREATE TABLE `course` ( `c_id` varchar(20) NOT NULL DEFAULT '', `c_name` varchar(20) NOT NULL DEFAULT '', `t_id` varchar(20) NOT NULL, PRIMARY KEY (`c_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of course -- ---------------------------- INSERT INTO `course` VALUES ('01', '語文', '02'); INSERT INTO `course` VALUES ('02', '數學', '01'); INSERT INTO `course` VALUES ('03', '英語', '03'); -- ---------------------------- -- Table structure for score -- ---------------------------- DROP TABLE IF EXISTS `score`; CREATE TABLE `score` ( `s_id` varchar(20) NOT NULL DEFAULT '', `c_id` varchar(20) NOT NULL DEFAULT '', `s_score` int(3) DEFAULT NULL, PRIMARY KEY (`s_id`,`c_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of score -- ---------------------------- INSERT INTO `score` VALUES ('01', '01', '80'); INSERT INTO `score` VALUES ('01', '02', '90'); INSERT INTO `score` VALUES ('01', '03', '99'); INSERT INTO `score` VALUES ('02', '01', '70'); INSERT INTO `score` VALUES ('02', '02', '60'); INSERT INTO `score` VALUES ('02', '03', '80'); INSERT INTO `score` VALUES ('03', '01', '80'); INSERT INTO `score` VALUES ('03', '02', '80'); INSERT INTO `score` VALUES ('03', '03', '80'); INSERT INTO `score` VALUES ('04', '01', '50'); INSERT INTO `score` VALUES ('04', '02', '30'); INSERT INTO `score` VALUES ('04', '03', '20'); INSERT INTO `score` VALUES ('05', '01', '76'); INSERT INTO `score` VALUES ('05', '02', '87'); INSERT INTO `score` VALUES ('06', '01', '31'); INSERT INTO `score` VALUES ('06', '03', '34'); INSERT INTO `score` VALUES ('07', '02', '89'); INSERT INTO `score` VALUES ('07', '03', '98');
SELECT s.s_id,s.s_name,c.c_name,sc.s_score FROM student s LEFT JOIN score sc on sc.s_id = s.s_id LEFT JOIN course c on c.c_id = sc.c_id
好的,現在呢我們要把課程名稱呢變成橫行呢?
二、如何實現
1)首先看我們的靜態SQL
關聯成績表課程表查詢學生各科課程成績
SELECT s.s_id,s.s_name,c.c_name,sc.s_score FROM student s LEFT JOIN score sc on sc.s_id=s.s_id LEFT JOIN course c on c.c_id = sc.c_id;
IF(s1,s2,s3)表達式,類似三木運算符取值,s1值為真取s2值,假取s3個值,最後可得到某一科成績
SELECT p.s_id,p.s_name, p.c_name,p.c_name = '數學', IF(p.c_name = '數學',p.c_name,NULL)c_name,IF(p.c_name = '數學',p.s_score,NULL)s_score FROM ( SELECT s.s_id,s.s_name,c.c_name,sc.s_score FROM student s LEFT JOIN score sc on sc.s_id=s.s_id LEFT JOIN course c on c.c_id = sc.c_id )p;
然後我們分組且用MAX函數獲取每個學生的數學課程的成績,替換這一課的字段名稱
SELECT p.s_id, p.s_name, MAX(IF(p.c_name = '數學', p.s_score, NULL)) AS 數學 FROM ( SELECT s.s_id,s.s_name,c.c_name,sc.s_score FROM student s LEFT JOIN score sc on sc.s_id=s.s_id LEFT JOIN course c on c.c_id = sc.c_id )p GROUP BY p.s_id;
獲取所有人各科成績
SELECT p.s_id, p.s_name, MAX(IF(p.c_name = '數學', p.s_score, NULL)) AS 數學, MAX(IF(p.c_name = '語文', p.s_score, NULL)) AS 語文, MAX(IF(p.c_name = '英語', p.s_score, NULL)) AS 英語 FROM ( SELECT s.s_id,s.s_name,c.c_name,sc.s_score FROM student s LEFT JOIN score sc on sc.s_id=s.s_id LEFT JOIN course c on c.c_id = sc.c_id )p GROUP BY p.s_id;
2)那麼就有人問瞭,如果我有100門課程不是要寫100次名稱,這也太麻煩瞭?
接下來請看動態SQL
我們的動態sql是拼接實現的, 主要就是拼接我們的課程成績那一句, 所以要先看一下CONCAT函數拼接課程語句
SELECT c_name,CONCAT( 'MAX(IF(p.c_name = ''', c_name, ''', c.s_score, NULL)) AS ', c_name ) FROM course c;
是的,結果就是上面要的MAX函數
然後我麼可以用GROUP_CONCAT()函數把這些內容拼接成一句
SELECT GROUP_CONCAT(DISTINCT c_name,CONCAT( 'MAX(IF(p.c_name = ''', c_name, ''', c.s_score, NULL)) AS ', c_name )) FROM course c;
接下來,拼接sql實現需求
-- 1.定義一個sql變量 SET @sql = NULL; -- 2.把我們的查詢課程的sql賦給變量 SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(p.c_name = ''',c_name,''', p.s_score, NULL)) AS ',c_name)) INTO @sql FROM course; -- 3.拼接sql SET @sql = CONCAT('SELECT p.s_id, p.s_name, ', @sql ,' FROM (SELECT s.s_id,s.s_name,c.c_name,sc.s_score FROM student s LEFT JOIN score sc on sc.s_id=s.s_id LEFT JOIN course c on c.c_id = sc.c_id)p GROUP BY p.s_id'); -- 預處理語句 PREPARE stmt FROM @sql; -- 執行 EXECUTE stmt; -- 銷毀 DEALLOCATE PREPARE stmt;
3)這樣每次都寫一長串sql也很麻煩?
好的 那麼我們來封裝成存儲過程
-- 1、創建無參存儲過程 delimiter $$ CREATE PROCEDURE getStudentRow() BEGIN ------把要執行的sql放在這裡就可以瞭 SET @sql = NULL; SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(p.c_name = ''',c_name,''', p.s_score, NULL)) AS ',c_name)) INTO @sql FROM course; SET @sql = CONCAT('SELECT p.s_id, p.s_name, ', @sql ,' FROM (SELECT s.s_id,s.s_name,c.c_name,sc.s_score FROM student s LEFT JOIN score sc on sc.s_id=s.s_id LEFT JOIN course c on c.c_id = sc.c_id)p GROUP BY p.s_id'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; ------把要執行的sql放在這裡就可以瞭 END$$; delimiter; -- 查詢存儲過程 SHOW PROCEDURE STATUS; -- 調用 CALL getStudentRow();
這樣每次直接調用就可以瞭?
總結
到此這篇關於MYSQL如何列轉行的文章就介紹到這瞭,更多相關MYSQL列轉行內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet!
推薦閱讀:
- MySQL 基礎常用命令總結
- MySQL 數據庫聚合查詢和聯合查詢操作
- MySQL去重中distinct和group by的區別淺析
- MySQL 行轉列詳情
- PostgreSQL 實現將多行合並轉為列