點(diǎn)擊下載
本文文檔

當(dāng)前位置：首頁(yè) - 科技 - 知識(shí)百科 - 正文

[MySQL]行列轉(zhuǎn)換變化各種方法實(shí)現(xiàn)總結(jié)（行變列報(bào)表統(tǒng)計(jì)、列變行數(shù)據(jù)記錄統(tǒng)計(jì)等）_MySQL

來(lái)源：懂視網(wǎng) 責(zé)編：小采時(shí)間：2020-11-09 19:44:11

[MySQL]行列轉(zhuǎn)換變化各種方法實(shí)現(xiàn)總結(jié)（行變列報(bào)表統(tǒng)計(jì)、列變行數(shù)據(jù)記錄統(tǒng)計(jì)等）_MySQL

[MySQL]行列轉(zhuǎn)換變化各種方法實(shí)現(xiàn)總結(jié)（行變列報(bào)表統(tǒng)計(jì)、列變行數(shù)據(jù)記錄統(tǒng)計(jì)等）_MySQL:前言： mysql行列變化，最難的就是將多個(gè)列變成多行，使用的比較多的是統(tǒng)計(jì)學(xué)中行變列，列變行，沒(méi)有找到現(xiàn)成的函數(shù)或者語(yǔ)句，所以自己寫(xiě)了存儲(chǔ)過(guò)程，使用動(dòng)態(tài)sql來(lái)實(shí)現(xiàn)，應(yīng)用業(yè)務(wù)場(chǎng)景，用戶(hù)每個(gè)月都有使用記錄數(shù)錄入一張表，一個(gè)月一個(gè)字段，所以表的字段是

推薦度：

點(diǎn)擊下載本文 文檔為doc格式

導(dǎo)讀[MySQL]行列轉(zhuǎn)換變化各種方法實(shí)現(xiàn)總結(jié)（行變列報(bào)表統(tǒng)計(jì)、列變行數(shù)據(jù)記錄統(tǒng)計(jì)等）_MySQL:前言： mysql行列變化，最難的就是將多個(gè)列變成多行，使用的比較多的是統(tǒng)計(jì)學(xué)中行變列，列變行，沒(méi)有找到現(xiàn)成的函數(shù)或者語(yǔ)句，所以自己寫(xiě)了存儲(chǔ)過(guò)程，使用動(dòng)態(tài)sql來(lái)實(shí)現(xiàn)，應(yīng)用業(yè)務(wù)場(chǎng)景，用戶(hù)每個(gè)月都有使用記錄數(shù)錄入一張表，一個(gè)月一個(gè)字段，所以表的字段是

前言：

mysql行列變化，最難的就是將多個(gè)列變成多行，使用的比較多的是統(tǒng)計(jì)學(xué)中行變列，列變行，沒(méi)有找到現(xiàn)成的函數(shù)或者語(yǔ)句，所以自己寫(xiě)了存儲(chǔ)過(guò)程，使用動(dòng)態(tài)sql來(lái)實(shí)現(xiàn)，應(yīng)用業(yè)務(wù)場(chǎng)景，用戶(hù)每個(gè)月都有使用記錄數(shù)錄入一張表，一個(gè)月一個(gè)字段，所以表的字段是動(dòng)態(tài)增長(zhǎng)的，現(xiàn)在需要實(shí)時(shí)統(tǒng)計(jì)當(dāng)前用戶(hù)使用的總數(shù)量，如果你知道有多少個(gè)字段，那么可以用select c1+c2+c3+…. From tbname where tid=’111’;來(lái)實(shí)現(xiàn)，但是關(guān)鍵是這個(gè)都是動(dòng)態(tài)的，所以在應(yīng)用程序端來(lái)實(shí)現(xiàn)確實(shí)不適宜，可以放在數(shù)據(jù)庫(kù)后臺(tái)在存儲(chǔ)過(guò)程里實(shí)現(xiàn)。

而且在行變成列中，如果要寫(xiě)單個(gè)sql來(lái)實(shí)現(xiàn)，列的數(shù)目就需要寫(xiě)死，因?yàn)槿绻恢酪故境啥嗌倭械脑?，就需要用?dòng)態(tài)變量，而一條sql里面無(wú)法使用動(dòng)態(tài)變量。但是可以使用sql塊來(lái)實(shí)現(xiàn)動(dòng)態(tài)的效果。

一，列變成行例子演示

1，準(zhǔn)備測(cè)試數(shù)據(jù)

這是基礎(chǔ)數(shù)據(jù)表，里面有多個(gè)字段wm201403……，現(xiàn)在需要把N個(gè)這樣的列變成行數(shù)據(jù)。

USE csdn;
DROP TABLE IF EXISTS flow_table;
CREATE TABLE `flow_table` (
 `ID` INT(11) NOT NULL AUTO_INCREMENT,
 `Number` BIGINT(11) NOT NULL,
 `City` VARCHAR(10) NOT NULL,
 `wm201403` DECIMAL(7,2) DEFAULT NULL,
 `wm201404` DECIMAL(7,2) DEFAULT NULL,
 `wm201405` DECIMAL(7,2) DEFAULT NULL,
 `wm201406` DECIMAL(7,2) DEFAULT NULL,
 `wm201407` DECIMAL(7,2) DEFAULT NULL,
 `wm201408` DECIMAL(7,2) DEFAULT NULL,
 PRIMARY KEY (`ID`,`Number`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

錄入一批測(cè)試數(shù)據(jù)：

INSERT INTO flow_table(Number,City,wm201403,wm201404,wm201405,wm201406,wm201407,wm201408)SELECT 1,'shanghai',100.2,180.4,141,164,124,127;
INSERT INTO flow_table(Number,City,wm201403,wm201404,wm201405,wm201406,wm201407,wm201408)SELECT 2,'shanghai',110.23,180.34,141.23,104.78,124.67,127.45;
INSERT INTO flow_table(Number,City,wm201403,wm201404,wm201405,wm201406,wm201407,wm201408)SELECT 3,'beijing',123.23,110.34,131.33,154.58,154.67,167.45;
INSERT INTO flow_table(Number,City,wm201403,wm201404,wm201405,wm201406,wm201407,wm201408)SELECT 4,'hangzhou',0,110.34,131.33,154.58,154.67,0;
INSERT INTO flow_table(Number,City,wm201405,wm201406,wm201407,wm201408)SELECT 5,'hangzhou',131.33,154.58,154.67,0;

需要達(dá)到的統(tǒng)計(jì)效果是：

+--------+-----------+

| Number | total_num |

+--------+-----------+

| 1 | 836.60 |

| 2 | 788.70 |

| 3 | 841.60 |

| 4 | 550.92 |

| 5 | 440.58 |

+--------+-----------+

5 rows in set (0.00 sec)

2，存儲(chǔ)過(guò)程遍歷：

這個(gè)存儲(chǔ)過(guò)程建立了2張臨時(shí)表，查詢(xún)測(cè)試表數(shù)據(jù)形成游標(biāo)，遍歷游標(biāo)根據(jù)主鍵Number來(lái)調(diào)用pro_flow_modify存儲(chǔ)過(guò)程進(jìn)行行列變化。代碼如下：

DELIMITER $$
DROP PROCEDURE IF EXISTS csdn.`proc_all_changes`$$
CREATE PROCEDURE csdn.proc_all_changes()
BEGIN
 DECLARE v_number BIGINT;
 DECLARE v_city VARCHAR(10);
 DECLARE _done INT DEFAULT 0; 
 
 /*定義游標(biāo)*/
 DECLARE cur_all CURSOR FOR SELECT Number,City FROM csdn.`flow_table`;
 /**這里如果需要定義下當(dāng)NOT FOUND的時(shí)候，EXIT退出游標(biāo)遍歷，不然如果設(shè)置成CONTINUE會(huì)一直執(zhí)行下去。*/
 DECLARE EXIT HANDLER FOR NOT FOUND BEGIN SET _done=1;END; 
 
 /*建立臨時(shí)表，存放所有字段的臨時(shí)表*/
	DROP TABLE IF EXISTS flow_n_columns;
	CREATE TABLE `flow_n_columns` (
	 `column_name` VARCHAR(10) NOT NULL
	) ENGINE=INNODB DEFAULT CHARSET=utf8;

	/*存放最終變成行的數(shù)據(jù)表*/
	DROP TABLE IF EXISTS flow_tmp;
	CREATE TABLE `flow_tmp` (
	 `Number` INT(11) DEFAULT NULL,
	 `City` VARCHAR(10) DEFAULT NULL,
	 `wm_str` VARCHAR(10) DEFAULT NULL,
	 `Wm` DECIMAL(7,2) DEFAULT NULL
	) ENGINE=INNODB DEFAULT CHARSET=utf8;

 OPEN cur_all;
 REPEAT
 FETCH cur_all INTO v_number, v_city;
 IF NOT _done THEN 
	CALL csdn.pro_flow_modify(v_number,v_city);
 END IF; 
 UNTIL _done=1 END REPEAT;
 CLOSE cur_all; 
 	/*展示下所有的行轉(zhuǎn)列的數(shù)據(jù)**/
	SELECT * FROM csdn.flow_tmp; 
END$$ 
DELIMITER ;

3，行里變化存儲(chǔ)過(guò)程

通過(guò)查詢(xún)系統(tǒng)表information_schema.`COLUMNS`來(lái)獲取測(cè)試表flow_table的所有列，然后寫(xiě)動(dòng)態(tài)SQL，來(lái)把列的值錄入到臨時(shí)表flow_tmp中。

DELIMITER $$
DROP PROCEDURE IF EXISTS csdn.`pro_flow_modify`$$
CREATE PROCEDURE csdn.`pro_flow_modify`(p_Number INT,p_city VARCHAR(10))
BEGIN
	DECLARE v_column_name VARCHAR(10) DEFAULT '';
	DECLARE v_exe_sql VARCHAR(1000) DEFAULT '';
	DECLARE v_start_wm VARCHAR(10) DEFAULT '';
	DECLARE v_end_wm VARCHAR(10) DEFAULT '';
	DECLARE v_num DECIMAL(10,2) DEFAULT 0;
	
	DECLARE i INT DEFAULT 1;
	DECLARE v_Number INT DEFAULT 0;
	SET v_Number=p_Number;
	
	DELETE FROM csdn.flow_n_columns;
	DELETE FROM csdn.flow_tmp WHERE Number=v_Number;
	
	
	/*把測(cè)試表flow_table的所有字段都錄入字段臨時(shí)表中，這樣就達(dá)到了從列變成行的目的*/
	INSERT INTO flow_n_columns
	SELECT t.`COLUMN_NAME` FROM information_schema.`COLUMNS` t WHERE t.`TABLE_NAME`='flow_table' AND t.`TABLE_SCHEMA`='csdn' AND t.`COLUMN_NAME` NOT IN('ID','Number','City');
	SELECT column_name INTO v_column_name FROM csdn.flow_n_columns LIMIT 1;
	
	/*開(kāi)始循環(huán)遍歷字段臨時(shí)表的字段數(shù)據(jù)，并且把字段值放入臨時(shí)表flow_tmp里面*/
	WHILE i>0 DO
	SET v_exe_sql=CONCAT('INSERT INTO csdn.flow_tmp(Number,City,wm_str,Wm) select ',v_Number,',\'',p_city, '\',\'',v_column_name,'\',',v_column_name,' from csdn.flow_table WHERE flow_table.Number=',v_Number,';');
	SET @sql=v_exe_sql;
	PREPARE s1 FROM @sql;
	EXECUTE s1;
	DEALLOCATE PREPARE s1; 
	DELETE FROM csdn.flow_n_columns WHERE column_name=v_column_name;
	SELECT column_name INTO v_column_name FROM csdn.flow_n_columns LIMIT 1;
	SELECT COUNT(1) INTO i FROM csdn.flow_n_columns ;
	DELETE FROM csdn.flow_tmp WHERE Wm=0;
	END WHILE;

	/*由于觸發(fā)器是不支持動(dòng)態(tài)sql，所以不能使用while循環(huán)，動(dòng)態(tài)遍歷所有統(tǒng)計(jì)列的，只能寫(xiě)死列了，如下所示：
	現(xiàn)在一個(gè)個(gè)insert只能寫(xiě)死了, flow_table表有多少個(gè)統(tǒng)計(jì)列就寫(xiě)多少個(gè)insert sql，以后新添加一個(gè)列，就在這里新添加一條insertsql語(yǔ)句
	INSERT INTO flow_tmp(Number,City,wm_str,Wm) SELECT v_Number,p_city,'wm201403',wm201403 FROM flow_table WHERE Number=v_Number ;
	INSERT INTO flow_tmp(Number,City,wm_str,Wm) SELECT v_Number,p_city,'wm201404',wm201404 FROM flow_table WHERE Number=v_Number ;
	INSERT INTO flow_tmp(Number,City,wm_str,Wm) SELECT v_Number,p_city,'wm201405',wm201405 FROM flow_table WHERE Number=v_Number ;
	INSERT INTO flow_tmp(Number,City,wm_str,Wm) SELECT v_Number,p_city,'wm201406',wm201406 FROM flow_table WHERE Number=v_Number ;
	INSERT INTO flow_tmp(Number,City,wm_str,Wm) SELECT v_Number,p_city,'wm201407',wm201407 FROM flow_table WHERE Number=v_Number ;
	INSERT INTO flow_tmp(Number,City,wm_str,Wm) SELECT v_Number,p_city,'wm201408',wm201408 FROM flow_table WHERE Number=v_Number ;
	*/
	
	/*清除掉不數(shù)據(jù)=0的列*/
	DELETE FROM csdn.flow_tmp WHERE Wm=0 OR Wm IS NULL;
	
	SELECT wm_str INTO v_start_wm FROM csdn.flow_tmp WHERE Number=v_Number ORDER BY wm_str ASC LIMIT 1;
	SELECT wm_str INTO v_end_wm FROM csdn.flow_tmp WHERE Number=v_Number ORDER BY wm_str DESC LIMIT 1;
	SELECT SUM(Wm) INTO v_num FROM csdn.flow_tmp WHERE Number=v_Number;	

 END$$

DELIMITER ;

4，列變行結(jié)果展示

臨時(shí)表的所有數(shù)據(jù)：

mysql> SELECT * FROM csdn.flow_tmp;
+--------+----------+----------+--------+
| Number | City | wm_str | Wm |
+--------+----------+----------+--------+
| 1 | shanghai | wm201403 | 100.20 |
| 1 | shanghai | wm201404 | 180.40 |
| 1 | shanghai | wm201405 | 141.00 |
| 1 | shanghai | wm201406 | 164.00 |
| 1 | shanghai | wm201407 | 124.00 |
| 1 | shanghai | wm201408 | 127.00 |
| 2 | shanghai | wm201403 | 110.23 |
| 2 | shanghai | wm201404 | 180.34 |
| 2 | shanghai | wm201405 | 141.23 |
| 2 | shanghai | wm201406 | 104.78 |
| 2 | shanghai | wm201407 | 124.67 |
| 2 | shanghai | wm201408 | 127.45 |
| 3 | beijing | wm201403 | 123.23 |
| 3 | beijing | wm201404 | 110.34 |
| 3 | beijing | wm201405 | 131.33 |
| 3 | beijing | wm201406 | 154.58 |
| 3 | beijing | wm201407 | 154.67 |
| 3 | beijing | wm201408 | 167.45 |
| 4 | hangzhou | wm201404 | 110.34 |
| 4 | hangzhou | wm201405 | 131.33 |
| 4 | hangzhou | wm201406 | 154.58 |
| 4 | hangzhou | wm201407 | 154.67 |
| 5 | hangzhou | wm201405 | 131.33 |
| 5 | hangzhou | wm201406 | 154.58 |
| 5 | hangzhou | wm201407 | 154.67 |
+--------+----------+----------+--------+
25 rows in set (0.00 sec)
mysql>

統(tǒng)計(jì)每個(gè)用戶(hù)的使用總量為：

mysql> SELECT Number,SUM(Wm) 'total_num' FROM flow_tmp GROUP BY Number ORDER BY Number;
+--------+-----------+
| Number | total_num |
+--------+-----------+
| 1 | 836.60 |
| 2 | 788.70 |
| 3 | 841.60 |
| 4 | 550.92 |
| 5 | 440.58 |
+--------+-----------+
5 rows in set (0.00 sec)

mysql>

二，行變列例子演示

1，準(zhǔn)備測(cè)試數(shù)據(jù)

USE csdn;
DROP TABLE IF EXISTS csdn.tb;
CREATE TABLE tb(`cname` VARCHAR(10),cource VARCHAR(10),score INT) ENGINE=INNODB;

INSERT INTO tb VALUES('張三','語(yǔ)文',74);
INSERT INTO tb VALUES('張三','數(shù)學(xué)',83);
INSERT INTO tb VALUES('張三','物理',93);
INSERT INTO tb VALUES('李四','語(yǔ)文',74);
INSERT INTO tb VALUES('李四','數(shù)學(xué)',84);
INSERT INTO tb VALUES('李四','物理',94);

SELECT * FROM tb;

需要得到的結(jié)果是：

+--------------------+--------+--------+--------+-----------+--------------+

+--------------------+--------+--------+--------+-----------+--------------+

| 張三 | 74.00 | 83.00 | 93.00 | 250.00 | 83.33 |

| 李四 | 74.00 | 84.00 | 94.00 | 252.00 | 84.00 |

| 總成績(jī)平均數(shù) | 74.00 | 83.50 | 93.50 | 251.00 | 83.67 |

+--------------------+--------+--------+--------+-----------+--------------+

2，利用SUM(IF()) 生成列 + WITH ROLLUP 生成匯總行,并利用 IFNULL將匯總行標(biāo)題顯示為 Total_num

SQL代碼塊如下：

SELECT cname AS "姓名",
	SUM(IF(cource="語(yǔ)文",score,0)) AS "語(yǔ)文",
	SUM(IF(cource="數(shù)學(xué)",score,0)) AS "數(shù)學(xué)",
	SUM(IF(cource="物理",score,0)) AS "物理",
	SUM(score) AS "總成績(jī)",
	ROUND(AVG(score),2) AS "平均成績(jī)"
FROM tb 
GROUP BY cname
UNION ALL
SELECT
	"總成績(jī)平均數(shù)",
	ROUND(AVG(`語(yǔ)文`),2) , ROUND(AVG(`數(shù)學(xué)`),2), ROUND(AVG(`物理`),2), ROUND(AVG(`總成績(jī)`),2), ROUND(AVG(`平均成績(jī)`),2)
FROM(
	SELECT "all",cname AS "姓名",
	SUM(IF(cource="語(yǔ)文",score,0)) AS "語(yǔ)文",
	SUM(IF(cource="數(shù)學(xué)",score,0)) AS "數(shù)學(xué)",
	SUM(IF(cource="物理",score,0)) AS "物理",
	SUM(score) AS "總成績(jī)",
	AVG(score) AS "平均成績(jī)"
	FROM tb 
	GROUP BY cname
)tb2 
GROUP BY tb2.all;

執(zhí)行結(jié)果正確，如下所示：

+--------------------+--------+--------+--------+-----------+--------------+

+--------------------+--------+--------+--------+-----------+--------------+

| 張三 | 74.00 | 83.00 | 93.00 | 250.00 | 83.33 |

| 李四 | 74.00 | 84.00 | 94.00 | 252.00 | 84.00 |

| 總成績(jī)平均數(shù) | 74.00 | 83.50 | 93.50 | 251.00 | 83.67 |

+--------------------+--------+--------+--------+-----------+--------------+

3，利用max(CASE ... WHEN ... THEN .. ELSE END) AS "語(yǔ)文"的方式來(lái)實(shí)現(xiàn)

SQL代碼如下：

SELECT 
	cname AS "姓名",
	MAX(CASE cource WHEN "語(yǔ)文" THEN score ELSE 0 END) AS "語(yǔ)文", 
	MAX(CASE cource WHEN "數(shù)學(xué)" THEN score ELSE 0 END) AS "數(shù)學(xué)", 
	MAX(CASE cource WHEN "物理" THEN score ELSE 0 END) AS "物理", 
	SUM(score) AS "總成績(jī)",
	ROUND(AVG(score) ,2) AS "平均成績(jī)"
FROM tb 
GROUP BY `cname`
UNION ALL
SELECT
	"總成績(jī)平均數(shù)",
	ROUND(AVG(`語(yǔ)文`),2) , ROUND(AVG(`數(shù)學(xué)`),2), ROUND(AVG(`物理`),2), ROUND(AVG(`總成績(jī)`),2), ROUND(AVG(`平均成績(jī)`),2)
FROM(	SELECT 'all' , 
	cname AS "姓名",
	MAX(CASE cource WHEN "語(yǔ)文" THEN score ELSE 0 END) AS "語(yǔ)文", 
	MAX(CASE cource WHEN "數(shù)學(xué)" THEN score ELSE 0 END) AS "數(shù)學(xué)", 
	MAX(CASE cource WHEN "物理" THEN score ELSE 0 END) AS "物理", 
	SUM(score) AS "總成績(jī)",
	ROUND(AVG(score) ,2) AS "平均成績(jī)"
	FROM tb 
	GROUP BY `cname` 
)tb2 GROUP BY tb2.all

執(zhí)行結(jié)果正確，如下所示：

+--------------------+--------+--------+--------+-----------+--------------+

+--------------------+--------+--------+--------+-----------+--------------+

| 張三 | 74.00 | 83.00 | 93.00 | 250.00 | 83.33 |

| 李四 | 74.00 | 84.00 | 94.00 | 252.00 | 84.00 |

| 總成績(jī)平均數(shù) | 74.00 | 83.50 | 93.50 | 251.00 | 83.67 |

+--------------------+--------+--------+--------+-----------+--------------+

4，利用 WITH rollup結(jié)果不符合

SQL代碼如下：

 SELECT IFNULL(cname,'總平均數(shù)') AS "姓名",
	MAX(CASE cource WHEN "語(yǔ)文" THEN score ELSE 0 END) AS "語(yǔ)文", 
	MAX(CASE cource WHEN "數(shù)學(xué)" THEN score ELSE 0 END) AS "數(shù)學(xué)", 
	MAX(CASE cource WHEN "物理" THEN score ELSE 0 END) AS "物理", 
	ROUND(AVG(score),2) AS "總成績(jī)",
	ROUND(AVG(avg_score),2) AS "平均成績(jī)" 
 FROM(
	SELECT 
	cname ,
	IFNULL(cource,'total') cource,
	SUM(score) AS score,
	ROUND(AVG(score) ,2) AS avg_score
	FROM tb 
	GROUP BY `cname`,cource WITH ROLLUP HAVING cname IS NOT NULL
	)tb2 
	GROUP BY tb2.cname WITH ROLLUP;

mysql> SELECT IFNULL(cname,'總平均數(shù)') AS "姓名",
 -> MAX(CASE cource WHEN "語(yǔ)文" THEN score ELSE 0 END) AS "語(yǔ)文", 
 -> MAX(CASE cource WHEN "數(shù)學(xué)" THEN score ELSE 0 END) AS "數(shù)學(xué)", 
 -> MAX(CASE cource WHEN "物理" THEN score ELSE 0 END) AS "物理", 
 -> ROUND(AVG(score),2) AS "總成績(jī)",
 -> ROUND(AVG(avg_score),2) AS "平均成績(jī)" 
 -> FROM(
 -> SELECT 
 -> 
Display ALL 793 possibilities? (Y OR n) 
 -> cname ,
 -> 
Display ALL 793 possibilities? (Y OR n) 
 -> IFNULL(cource,'total') cource,
 -> 
Display ALL 793 possibilities? (Y OR n) 
 -> SUM(score) AS score,
 -> 
Display ALL 793 possibilities? (Y OR n) 
 -> ROUND(AVG(score) ,2) AS avg_score
 -> FROM tb 
 -> GROUP BY `cname`,cource WITH ROLLUP HAVING cname IS NOT NULL
 -> )tb2 
 -> GROUP BY tb2.cname WITH ROLLUP;
+--------------+--------+--------+--------+-----------+--------------+
| 姓名 | 語(yǔ)文 | 數(shù)學(xué) | 物理 | 總成績(jī) | 平均成績(jī) |
+--------------+--------+--------+--------+-----------+--------------+
| 張三 | 74 | 83 | 93 | 125.00 | 83.33 |
| 李四 | 74 | 84 | 94 | 126.00 | 84.00 |
| 總平均數(shù) | 74 | 84 | 94 | 125.50 | 83.67 |
+--------------+--------+--------+--------+-----------+--------------+
3 ROWS IN SET, 1 warning (0.00 sec)
mysql>

總結(jié)： WITH rollup中對(duì)求列的總數(shù)是OK的，但是求列的平均數(shù)有偏差，這里場(chǎng)景使用不是恰當(dāng)。

5，使用動(dòng)態(tài)SQL來(lái)實(shí)現(xiàn)

SQL代碼塊如下：

/*僅僅班級(jí)成員部分*/
SET @a=''; 
SELECT @a:=CONCAT(@a,'SUM(IF(cource=\'',cource,'\'',',score,0)) AS ',cource,',') FROM (SELECT DISTINCT cource FROM tb) A;
SET @a=CONCAT(@a,"ROUND(AVG(score) ,2) AS \"平均成績(jī)\"");
SET @b=CONCAT('SELECT IFNULL(cname,\'總成績(jī)\'),',LEFT(@a,LENGTH(@a)-1),' ,SUM(score) AS \"總成績(jī)\" FROM tb GROUP BY cname ');

/*班級(jí)成員總計(jì)部分**/
SET @a2="";
SET @b2=CONCAT('SELECT "all",IFNULL(cname,\'總成績(jī)\'),',LEFT(@a,LENGTH(@a)-1),' ,SUM(score) AS \"總成績(jī)\" FROM tb GROUP BY cname ');
SELECT @a2:=CONCAT(@a2,'ROUND(AVG(`',cource,'`),2),') FROM (SELECT DISTINCT cource FROM tb) A;
SET @a2=CONCAT(@a2," ROUND(AVG(`平均成績(jī)`),2),ROUND(AVG(`總成績(jī)`),2) ");
SET @c=CONCAT("SELECT \"班級(jí)平均數(shù)\",",LEFT(@a2,LENGTH(@a)-1)," FROM(",@b2,")tb2 GROUP BY tb2.all;");
SET @d=CONCAT(@b," UNION ALL ",@c);

PREPARE stmt1 FROM @d;
EXECUTE stmt1;

查看執(zhí)行結(jié)果如下，已經(jīng)達(dá)到效果：

mysql> /*僅僅班級(jí)成員部分*/
mysql> SET @a=''; 
QUERY OK, 0 ROWS affected (0.00 sec)

mysql> SELECT @a:=CONCAT(@a,'SUM(IF(cource=\'',cource,'\'',',score,0)) AS ',cource,',') FROM (SELECT DISTINCT cource FROM tb) A;
+-----------------------------------------------------------------------------------------------------------------------------------+
| @a:=CONCAT(@a,'SUM(IF(cource=\'',cource,'\'',',score,0)) AS ',cource,',') |
+-----------------------------------------------------------------------------------------------------------------------------------+
| SUM(IF(cource='語(yǔ)文',score,0)) AS 語(yǔ)文, |
| SUM(IF(cource='語(yǔ)文',score,0)) AS 語(yǔ)文,SUM(IF(cource='數(shù)學(xué)',score,0)) AS 數(shù)學(xué), |
| SUM(IF(cource='語(yǔ)文',score,0)) AS 語(yǔ)文,SUM(IF(cource='數(shù)學(xué)',score,0)) AS 數(shù)學(xué),SUM(IF(cource='物理',score,0)) AS 物理, |
+-----------------------------------------------------------------------------------------------------------------------------------+
3 ROWS IN SET (0.00 sec)

mysql> SET @a=CONCAT(@a,"ROUND(AVG(score) ,2) AS \"平均成績(jī)\"");
QUERY OK, 0 ROWS affected (0.00 sec)

mysql> SET @b=CONCAT('SELECT IFNULL(cname,\'總成績(jī)\'),',LEFT(@a,LENGTH(@a)-1),' ,SUM(score) AS \"總成績(jī)\" FROM tb GROUP BY cname ');
QUERY OK, 0 ROWS affected (0.00 sec)

mysql> 
mysql> /*班級(jí)成員總計(jì)部分**/
mysql> SET @a2="";
QUERY OK, 0 ROWS affected (0.00 sec)

mysql> SET @b2=CONCAT('SELECT "all",IFNULL(cname,\'總成績(jī)\'),',LEFT(@a,LENGTH(@a)-1),' ,SUM(score) AS \"總成績(jī)\" FROM tb GROUP BY cname ');
QUERY OK, 0 ROWS affected (0.00 sec)

mysql> SELECT @a2:=CONCAT(@a2,'ROUND(AVG(`',cource,'`),2),') FROM (SELECT DISTINCT cource FROM tb) A;
+-----------------------------------------------------------------------+
| @a2:=CONCAT(@a2,'ROUND(AVG(`',cource,'`),2),') |
+-----------------------------------------------------------------------+
| ROUND(AVG(`語(yǔ)文`),2), |
| ROUND(AVG(`語(yǔ)文`),2),ROUND(AVG(`數(shù)學(xué)`),2), |
| ROUND(AVG(`語(yǔ)文`),2),ROUND(AVG(`數(shù)學(xué)`),2),ROUND(AVG(`物理`),2), |
+-----------------------------------------------------------------------+
3 ROWS IN SET (0.00 sec)

mysql> SET @a2=CONCAT(@a2," ROUND(AVG(`平均成績(jī)`),2),ROUND(AVG(`總成績(jī)`),2) ");
QUERY OK, 0 ROWS affected (0.00 sec)

mysql> SET @c=CONCAT("SELECT \"班級(jí)平均數(shù)\",",LEFT(@a2,LENGTH(@a)-1)," FROM(",@b2,")tb2 GROUP BY tb2.all;");
QUERY OK, 0 ROWS affected (0.00 sec)

mysql> SET @d=CONCAT(@b," UNION ALL ",@c);
QUERY OK, 0 ROWS affected (0.00 sec)

mysql> 
mysql> PREPARE stmt1 FROM @d;
QUERY OK, 0 ROWS affected (0.00 sec)
Statement prepared

mysql> EXECUTE stmt1;
+---------------------------+--------+--------+--------+--------------+-----------+
| IFNULL(cname,'總成績(jī)') | 語(yǔ)文 | 數(shù)學(xué) | 物理 | 平均成績(jī) | 總成績(jī) |
+---------------------------+--------+--------+--------+--------------+-----------+
| 張三 | 74.00 | 83.00 | 93.00 | 83.33 | 250.00 |
| 李四 | 74.00 | 84.00 | 94.00 | 84.00 | 252.00 |
| 班級(jí)平均數(shù) | 74.00 | 83.50 | 93.50 | 83.67 | 251.00 |
+---------------------------+--------+--------+--------+--------------+-----------+
3 ROWS IN SET (0.00 sec)
mysql>

參考文章地址：http://blog.chinaunix.net/uid-7692530-id-2567582.html

聲明：本網(wǎng)頁(yè)內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問(wèn)題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com

[MySQL]行列轉(zhuǎn)換變化各種方法實(shí)現(xiàn)總結(jié)（行變列報(bào)表統(tǒng)計(jì)、列變行數(shù)據(jù)記錄統(tǒng)計(jì)等）_MySQL

推薦度：

點(diǎn)擊下載本文 文檔為doc格式

標(biāo)簽：方法行列轉(zhuǎn)換統(tǒng)計(jì)

熱門(mén)焦點(diǎn)

[MySQL]行列轉(zhuǎn)換變化各種方法實(shí)現(xiàn)總結(jié)（行變列報(bào)表統(tǒng)計(jì)、列變行數(shù)據(jù)記錄統(tǒng)計(jì)等）_MySQL

[MySQL]行列轉(zhuǎn)換變化各種方法實(shí)現(xiàn)總結(jié)（行變列報(bào)表統(tǒng)計(jì)、列變行數(shù)據(jù)記錄統(tǒng)計(jì)等）_MySQL

一，列變成行例子演示

1，準(zhǔn)備測(cè)試數(shù)據(jù)

2，存儲(chǔ)過(guò)程遍歷：

3，行里變化存儲(chǔ)過(guò)程

4，列變行結(jié)果展示

二，行變列例子演示

1，準(zhǔn)備測(cè)試數(shù)據(jù)

2，利用SUM(IF()) 生成列 + WITH ROLLUP 生成匯總行,并利用 IFNULL將匯總行標(biāo)題顯示為 Total_num

3，利用max(CASE ... WHEN ... THEN .. ELSE END) AS "語(yǔ)文"的方式來(lái)實(shí)現(xiàn)

4，利用 WITH rollup結(jié)果不符合

5，使用動(dòng)態(tài)SQL來(lái)實(shí)現(xiàn)

[MySQL]行列轉(zhuǎn)換變化各種方法實(shí)現(xiàn)總結(jié)（行變列報(bào)表統(tǒng)計(jì)、列變行數(shù)據(jù)記錄統(tǒng)計(jì)等）_MySQL

最新推薦

猜你喜歡

熱門(mén)推薦

[MySQL]行列轉(zhuǎn)換變化各種方法實(shí)現(xiàn)總結(jié)（行變列報(bào)表統(tǒng)計(jì)、列變行數(shù)據(jù)記錄統(tǒng)計(jì)等）_MySQL

[MySQL]行列轉(zhuǎn)換變化各種方法實(shí)現(xiàn)總結(jié)（行變列報(bào)表統(tǒng)計(jì)、列變行數(shù)據(jù)記錄統(tǒng)計(jì)等）_MySQL

一，列變成行例子演示

1，準(zhǔn)備測(cè)試數(shù)據(jù)

2，存儲(chǔ)過(guò)程遍歷：

3，行里變化存儲(chǔ)過(guò)程

4，列變行結(jié)果展示

二，行變列例子演示

1，準(zhǔn)備測(cè)試數(shù)據(jù)

2，利用SUM(IF()) 生成列 + WITH ROLLUP 生成匯總行,并利用 IFNULL將匯總行標(biāo)題顯示為 Total_num

3，利用max(CASE ... WHEN ... THEN .. ELSE END) AS "語(yǔ)文"的方式來(lái)實(shí)現(xiàn)

4，利用 WITH rollup結(jié)果不符合

5，使用動(dòng)態(tài)SQL來(lái)實(shí)現(xiàn)

[MySQL]行列轉(zhuǎn)換變化各種方法實(shí)現(xiàn)總結(jié)（行變列報(bào)表統(tǒng)計(jì)、列變行數(shù)據(jù)記錄統(tǒng)計(jì)等）_MySQL

最新推薦

猜你喜歡

熱門(mén)推薦

[MySQL]行列轉(zhuǎn)換變化各種方法實(shí)現(xiàn)總結(jié)（行變列報(bào)表統(tǒng)計(jì)、列變行數(shù)據(jù)記錄統(tǒng)計(jì)等）_MySQL

一，列變成行例子演示

2，存儲(chǔ)過(guò)程遍歷：

3，行里變化存儲(chǔ)過(guò)程

4，列變行結(jié)果展示

二，行變列例子演示

2，利用SUM(IF()) 生成列 + WITH ROLLUP 生成匯總行,并利用 IFNULL將匯總行標(biāo)題顯示為 Total_num

4，利用 WITH rollup結(jié)果不符合

[MySQL]行列轉(zhuǎn)換變化各種方法實(shí)現(xiàn)總結(jié)（行變列報(bào)表統(tǒng)計(jì)、列變行數(shù)據(jù)記錄統(tǒng)計(jì)等）_MySQL