[MySQL] 行列變化各種方法實現總結(行變列報表統計、列變行資料記錄統計等)
前言:
mysql行列變化,最難的就是將多個列變成多行,使用的比較多的是統計學中行變列,列變行,沒有找到現成的函式或者語句,所以自己寫了儲存過程,使用動態sql來實現,應用業務場景,使用者每個月都有使用記錄數錄入一張表,一個月一個欄位,所以表的欄位是動態增長的,現在需要實時統計當前使用者使用的總數量,如果你知道有多少個欄位,那麼可以用select c1+c2+c3+…. From tbname where tid=’111’;來實現,但是關鍵是這個都是動態的,所以在應用程式端來實現確實不適宜,可以放在資料庫後臺在儲存過程裡實現。
而且在行變成列中,如果要寫單個sql來實現,列的數目就需要寫死,因為如果不知道要展示成多少列的話,就需要用動態變數,而一條sql裡面無法使用動態變數。但是可以使用sql塊來實現動態的效果。
一,列變成行例子演示
1,準備測試資料
這是基礎資料表,裡面有多個欄位wm201403……,現在需要把N個這樣的列變成行資料。
-
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;
錄入一批測試資料:
-
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;
需要達到的統計效果是:
+--------+-----------+
| 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,儲存過程遍歷:
這個儲存過程建立了2張臨時表,查詢測試表資料形成遊標,遍歷遊標根據主鍵Number來呼叫pro_flow_modify儲存過程進行行列變化。程式碼如下:
-
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;
-
-
/*定義遊標*/
-
DECLARE cur_all CURSOR FOR SELECT Number,City FROM csdn.`flow_table`;
-
/**這裡如果需要定義下當NOT FOUND的時候,EXIT退出遊標遍歷,不然如果設定成CONTINUE會一直執行下去。*/
-
DECLARE EXIT HANDLER FOR NOT FOUND BEGIN SET _done=1;END;
-
-
/*建立臨時表,存放所有欄位的臨時表*/
-
DROP TABLE IF EXISTS flow_n_columns;
-
CREATE TABLE `flow_n_columns` (
-
`column_name` VARCHAR(10) NOT NULL
-
) ENGINE=INNODB DEFAULT CHARSET=utf8;
-
-
/*存放最終變成行的資料表*/
-
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;
-
/*展示下所有的行轉列的資料**/
-
SELECT * FROM csdn.flow_tmp;
-
END$$
- DELIMITER ;
3,行裡變化儲存過程
透過查詢系統表information_schema.`COLUMNS`來獲取測試表flow_table的所有列,然後寫動態SQL,來把列的值錄入到臨時表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;
-
-
-
/*把測試表flow_table的所有欄位都錄入欄位臨時表中,這樣就達到了從列變成行的目的*/
-
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;
-
-
/*開始迴圈遍歷欄位臨時表的欄位資料,並且把欄位值放入臨時表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;
-
-
/*由於觸發器是不支援動態sql,所以不能使用while迴圈,動態遍歷所有統計列的,只能寫死列了,如下所示:
-
現在一個個insert只能寫死了, flow_table表有多少個統計列就寫多少個insert sql,以後新新增一個列,就在這裡新新增一條insertsql語句
-
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 ;
-
*/
-
-
/*清除掉不資料=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,列變行結果展示
臨時表的所有資料:
-
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>
統計每個使用者的使用總量為:
-
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,準備測試資料
-
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(\'張三\',\'語文\',74);
-
INSERT INTO tb VALUES(\'張三\',\'數學\',83);
-
INSERT INTO tb VALUES(\'張三\',\'物理\',93);
-
INSERT INTO tb VALUES(\'李四\',\'語文\',74);
-
INSERT INTO tb VALUES(\'李四\',\'數學\',84);
- INSERT INTO tb VALUES(\'李四\',\'物理\',94);
需要得到的結果是:
+--------------------+--------+--------+--------+-----------+--------------+
| 姓名 | 語文 | 數學 | 物理 | 總成績 | 平均成績 |
+--------------------+--------+--------+--------+-----------+--------------+
| 張三 | 74.00 | 83.00 | 93.00 | 250.00 | 83.33 |
| 李四 | 74.00 | 84.00 | 94.00 | 252.00 | 84.00 |
| 總成績平均數 | 74.00 | 83.50 | 93.50 | 251.00 | 83.67 |
+--------------------+--------+--------+--------+-----------+--------------+
2,利用SUM(IF()) 生成列 + WITH ROLLUP 生成彙總行,並利用 IFNULL將彙總行標題顯示為 Total_num
SQL程式碼塊如下:
-
SELECT cname AS \"姓名\",
-
SUM(IF(cource=\"語文\",score,0)) AS \"語文\",
-
SUM(IF(cource=\"數學\",score,0)) AS \"數學\",
-
SUM(IF(cource=\"物理\",score,0)) AS \"物理\",
-
SUM(score) AS \"總成績\",
-
ROUND(AVG(score),2) AS \"平均成績\"
-
FROM tb
-
GROUP BY cname
-
UNION ALL
-
SELECT
-
\"總成績平均數\",
-
ROUND(AVG(`語文`),2) , ROUND(AVG(`數學`),2), ROUND(AVG(`物理`),2), ROUND(AVG(`總成績`),2), ROUND(AVG(`平均成績`),2)
-
FROM(
-
SELECT \"all\",cname AS \"姓名\",
-
SUM(IF(cource=\"語文\",score,0)) AS \"語文\",
-
SUM(IF(cource=\"數學\",score,0)) AS \"數學\",
-
SUM(IF(cource=\"物理\",score,0)) AS \"物理\",
-
SUM(score) AS \"總成績\",
-
AVG(score) AS \"平均成績\"
-
FROM tb
-
GROUP BY cname
-
)tb2
- GROUP BY tb2.all;
執行結果正確,如下所示:
+--------------------+--------+--------+--------+-----------+--------------+
| 姓名 | 語文 | 數學 | 物理 | 總成績 | 平均成績 |
+--------------------+--------+--------+--------+-----------+--------------+
| 張三 | 74.00 | 83.00 | 93.00 | 250.00 | 83.33 |
| 李四 | 74.00 | 84.00 | 94.00 | 252.00 | 84.00 |
| 總成績平均數 | 74.00 | 83.50 | 93.50 | 251.00 | 83.67 |
+--------------------+--------+--------+--------+-----------+--------------+
3,利用max(CASE ... WHEN ... THEN .. ELSE END) AS "語文"的方式來實現
SQL程式碼如下:
-
SELECT
-
cname AS \"姓名\",
-
MAX(CASE cource WHEN \"語文\" THEN score ELSE 0 END) AS \"語文\",
-
MAX(CASE cource WHEN \"數學\" THEN score ELSE 0 END) AS \"數學\",
-
MAX(CASE cource WHEN \"物理\" THEN score ELSE 0 END) AS \"物理\",
-
SUM(score) AS \"總成績\",
-
ROUND(AVG(score) ,2) AS \"平均成績\"
-
FROM tb
-
GROUP BY `cname`
-
UNION ALL
-
SELECT
-
\"總成績平均數\",
-
ROUND(AVG(`語文`),2) , ROUND(AVG(`數學`),2), ROUND(AVG(`物理`),2), ROUND(AVG(`總成績`),2), ROUND(AVG(`平均成績`),2)
-
FROM( SELECT \'all\' ,
-
cname AS \"姓名\",
-
MAX(CASE cource WHEN \"語文\" THEN score ELSE 0 END) AS \"語文\",
-
MAX(CASE cource WHEN \"數學\" THEN score ELSE 0 END) AS \"數學\",
-
MAX(CASE cource WHEN \"物理\" THEN score ELSE 0 END) AS \"物理\",
-
SUM(score) AS \"總成績\",
-
ROUND(AVG(score) ,2) AS \"平均成績\"
-
FROM tb
-
GROUP BY `cname`
- )tb2 GROUP BY tb2.all
執行結果正確,如下所示:
+--------------------+--------+--------+--------+-----------+--------------+
| 姓名 | 語文 | 數學 | 物理 | 總成績 | 平均成績 |
+--------------------+--------+--------+--------+-----------+--------------+
| 張三 | 74.00 | 83.00 | 93.00 | 250.00 | 83.33 |
| 李四 | 74.00 | 84.00 | 94.00 | 252.00 | 84.00 |
| 總成績平均數 | 74.00 | 83.50 | 93.50 | 251.00 | 83.67 |
+--------------------+--------+--------+--------+-----------+--------------+
4,利用 WITH rollup結果不符合
-
SELECT IFNULL(cname,\'總平均數\') AS \"姓名\",
-
MAX(CASE cource WHEN \"語文\" THEN score ELSE 0 END) AS \"語文\",
-
MAX(CASE cource WHEN \"數學\" THEN score ELSE 0 END) AS \"數學\",
-
MAX(CASE cource WHEN \"物理\" THEN score ELSE 0 END) AS \"物理\",
-
ROUND(AVG(score),2) AS \"總成績\",
-
ROUND(AVG(avg_score),2) AS \"平均成績\"
-
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,\'總平均數\') AS \"姓名\",
-
-> MAX(CASE cource WHEN \"語文\" THEN score ELSE 0 END) AS \"語文\",
-
-> MAX(CASE cource WHEN \"數學\" THEN score ELSE 0 END) AS \"數學\",
-
-> MAX(CASE cource WHEN \"物理\" THEN score ELSE 0 END) AS \"物理\",
-
-> ROUND(AVG(score),2) AS \"總成績\",
-
-> ROUND(AVG(avg_score),2) AS \"平均成績\"
-
-> 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;
-
+--------------+--------+--------+--------+-----------+--------------+
-
| 姓名 | 語文 | 數學 | 物理 | 總成績 | 平均成績 |
-
+--------------+--------+--------+--------+-----------+--------------+
-
| 張三 | 74 | 83 | 93 | 125.00 | 83.33 |
-
| 李四 | 74 | 84 | 94 | 126.00 | 84.00 |
-
| 總平均數 | 74 | 84 | 94 | 125.50 | 83.67 |
-
+--------------+--------+--------+--------+-----------+--------------+
-
3 ROWS IN SET, 1 warning (0.00 sec)
-
- mysql>
總結: WITH rollup中對求列的總數是OK的,但是求列的平均數有偏差,這裡場景使用不是恰當。
5,使用動態SQL來實現
SQL程式碼塊如下:
-
/*僅僅班級成員部分*/
-
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 \\\"平均成績\\\"\");
-
SET @b=CONCAT(\'SELECT IFNULL(cname,\\\'總成績\\\'),\',LEFT(@a,LENGTH(@a)-1),\' ,SUM(score) AS \\\"總成績\\\" FROM tb GROUP BY cname \');
-
-
/*班級成員總計部分**/
-
SET @a2=\"\";
-
SET @b2=CONCAT(\'SELECT \"all\",IFNULL(cname,\\\'總成績\\\'),\',LEFT(@a,LENGTH(@a)-1),\' ,SUM(score) AS \\\"總成績\\\" 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(`平均成績`),2),ROUND(AVG(`總成績`),2) \");
-
SET @c=CONCAT(\"SELECT \\\"班級平均數\\\",\",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;
檢視執行結果如下,已經達到效果:
-
mysql> /*僅僅班級成員部分*/
-
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=\'語文\',score,0)) AS 語文, |
-
| SUM(IF(cource=\'語文\',score,0)) AS 語文,SUM(IF(cource=\'數學\',score,0)) AS 數學, |
-
| SUM(IF(cource=\'語文\',score,0)) AS 語文,SUM(IF(cource=\'數學\',score,0)) AS 數學,SUM(IF(cource=\'物理\',score,0)) AS 物理, |
-
+-----------------------------------------------------------------------------------------------------------------------------------+
-
3 ROWS IN SET (0.00 sec)
-
-
mysql> SET @a=CONCAT(@a,\"ROUND(AVG(score) ,2) AS \\\"平均成績\\\"\");
-
QUERY OK, 0 ROWS affected (0.00 sec)
-
-
mysql> SET @b=CONCAT(\'SELECT IFNULL(cname,\\\'總成績\\\'),\',LEFT(@a,LENGTH(@a)-1),\' ,SUM(score) AS \\\"總成績\\\" FROM tb GROUP BY cname \');
-
QUERY OK, 0 ROWS affected (0.00 sec)
-
-
mysql>
-
mysql> /*班級成員總計部分**/
-
mysql> SET @a2=\"\";
-
QUERY OK, 0 ROWS affected (0.00 sec)
-
-
mysql> SET @b2=CONCAT(\'SELECT \"all\",IFNULL(cname,\\\'總成績\\\'),\',LEFT(@a,LENGTH(@a)-1),\' ,SUM(score) AS \\\"總成績\\\" 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(`語文`),2), |
-
| ROUND(AVG(`語文`),2),ROUND(AVG(`數學`),2), |
-
| ROUND(AVG(`語文`),2),ROUND(AVG(`數學`),2),ROUND(AVG(`物理`),2), |
-
+-----------------------------------------------------------------------+
-
3 ROWS IN SET (0.00 sec)
-
-
mysql> SET @a2=CONCAT(@a2,\" ROUND(AVG(`平均成績`),2),ROUND(AVG(`總成績`),2) \");
-
QUERY OK, 0 ROWS affected (0.00 sec)
-
-
mysql> SET @c=CONCAT(\"SELECT \\\"班級平均數\\\",\",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,\'總成績\') | 語文 | 數學 | 物理 | 平均成績 | 總成績 |
-
+---------------------------+--------+--------+--------+--------------+-----------+
-
| 張三 | 74.00 | 83.00 | 93.00 | 83.33 | 250.00 |
-
| 李四 | 74.00 | 84.00 | 94.00 | 84.00 | 252.00 |
-
| 班級平均數 | 74.00 | 83.50 | 93.50 | 83.67 | 251.00 |
-
+---------------------------+--------+--------+--------+--------------+-----------+
-
3 ROWS IN SET (0.00 sec)
-
- mysql>
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29734436/viewspace-1266896/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- oracle中通過decode實現行變列的二維表統計展示Oracle
- 行列轉換,列行轉換統計
- oracle 行變列Oracle
- Oracle之多行記錄變一行記錄,行變列,並排序(wmsys.wm_concat)Oracle排序
- ACCESS 統計報表有多少行記錄
- 利用行SCN實現表變化跟蹤
- SQLServer統計監控SQL執行計劃突變的方法SQLServer
- PostgreSQL執行計劃變化SQL
- oracle資料庫獲取繫結變數的各種方法Oracle資料庫變數
- MySQL列轉行巧錄資料MySql
- C++變數總結束 | 輸出各種變數的值C++變數
- 行變列構建sql語句SQL
- sql統計各種奇葩的資料庫表資料SQL資料庫
- 執行計劃變化的處理
- MySQL索引失效行鎖變表鎖MySql索引
- 幾千萬記錄,資料庫表結構如何平滑變更?資料庫
- Oracle獲取繫結變數的各種方法Oracle變數
- MySQL 持久化系統變數MySql持久化變數
- Airbnb的變更資料捕獲系統,實現資料突變實時響應AI
- Excel將一列資料變為兩列Excel
- 統計沒有繫結變數SQL變數SQL
- vue總結:computed屬性實現監控變數變化,watch屬性監控變數變化從而實現其他業務...Vue變數
- vue總結:computed屬性實現監控變數變化,watch屬性監控變數變化從而實現其他業務Vue變數
- 報表如何實現對資料列進行排名分析?
- 改變gridview繫結列值的方法View
- 一次因表變數導致SQL執行效率變慢的實戰記錄SNSR變數SQL
- Gridview的資料列中實現滑鼠懸浮變色View
- SQL行轉列統計問題SQL
- 直播軟體搭建,狀態列顏色跟隨裝置系統變化而變化
- 隔兩行生一行計算變化率
- 陣列在記憶體中的變化陣列記憶體
- Win10系統工作列變高了的解決方法Win10
- 結對程式設計其實可以變變?薦程式設計
- PLSQL Language Referenc-PL/SQL集合和記錄-可變陣列-正確地使用可變陣列SQL陣列
- 統計陣列中各元素出現次數陣列
- JS實現陣列去重方法總結(六種方法)JS陣列
- 如何監聽陣列變化?陣列
- dba_tab_modifications -- 表中改變行次佔10%才分析統計資訊