search
HomeDatabaseMysql Tutorial[MySQL]行列转换变化各种方法实现总结（行变列报表统计、列变行数据记录统计等）_MySQL

[MySQL]行列转换变化各种方法实现总结（行变列报表统计、列变行数据记录统计等）_MySQL

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB
WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB
Jun 01, 2016 pm 01:02 PM
Reportmethod

前言：

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);

SELECT * FROM tb;
需要得到的结果是：

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

| 姓名 | 语文 | 数学 | 物理 | 总成绩 | 平均成绩 |

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

| 张三 | 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结果不符合

SQL代码如下： 
  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>

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

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Related Article
How does MySQL handle data replication?How does MySQL handle data replication?Apr 28, 2025 am 12:25 AM

MySQL processes data replication through three modes: asynchronous, semi-synchronous and group replication. 1) Asynchronous replication performance is high but data may be lost. 2) Semi-synchronous replication improves data security but increases latency. 3) Group replication supports multi-master replication and failover, suitable for high availability requirements.

How can you use the EXPLAIN statement to analyze query performance?How can you use the EXPLAIN statement to analyze query performance?Apr 28, 2025 am 12:24 AM

The EXPLAIN statement can be used to analyze and improve SQL query performance. 1. Execute the EXPLAIN statement to view the query plan. 2. Analyze the output results, pay attention to access type, index usage and JOIN order. 3. Create or adjust indexes based on the analysis results, optimize JOIN operations, and avoid full table scanning to improve query efficiency.

How do you back up and restore a MySQL database?How do you back up and restore a MySQL database?Apr 28, 2025 am 12:23 AM

Using mysqldump for logical backup and MySQLEnterpriseBackup for hot backup are effective ways to back up MySQL databases. 1. Use mysqldump to back up the database: mysqldump-uroot-pmydatabase>mydatabase_backup.sql. 2. Use MySQLEnterpriseBackup for hot backup: mysqlbackup--user=root-password=password--backup-dir=/path/to/backupbackup. When recovering, use the corresponding life

What are some common causes of slow queries in MySQL?What are some common causes of slow queries in MySQL?Apr 28, 2025 am 12:18 AM

The main reasons for slow MySQL query include missing or improper use of indexes, query complexity, excessive data volume and insufficient hardware resources. Optimization suggestions include: 1. Create appropriate indexes; 2. Optimize query statements; 3. Use table partitioning technology; 4. Appropriately upgrade hardware.

What are views in MySQL?What are views in MySQL?Apr 28, 2025 am 12:04 AM

MySQL view is a virtual table based on SQL query results and does not store data. 1) Views simplify complex queries, 2) Enhance data security, and 3) Maintain data consistency. Views are stored queries in databases that can be used like tables, but data is generated dynamically.

What are the differences in syntax between MySQL and other SQL dialects?What are the differences in syntax between MySQL and other SQL dialects?Apr 27, 2025 am 12:26 AM

MySQLdiffersfromotherSQLdialectsinsyntaxforLIMIT,auto-increment,stringcomparison,subqueries,andperformanceanalysis.1)MySQLusesLIMIT,whileSQLServerusesTOPandOracleusesROWNUM.2)MySQL'sAUTO_INCREMENTcontrastswithPostgreSQL'sSERIALandOracle'ssequenceandt

What is MySQL partitioning?What is MySQL partitioning?Apr 27, 2025 am 12:23 AM

MySQL partitioning improves performance and simplifies maintenance. 1) Divide large tables into small pieces by specific criteria (such as date ranges), 2) physically divide data into independent files, 3) MySQL can focus on related partitions when querying, 4) Query optimizer can skip unrelated partitions, 5) Choosing the right partition strategy and maintaining it regularly is key.

How do you grant and revoke privileges in MySQL?How do you grant and revoke privileges in MySQL?Apr 27, 2025 am 12:21 AM

How to grant and revoke permissions in MySQL? 1. Use the GRANT statement to grant permissions, such as GRANTALLPRIVILEGESONdatabase_name.TO'username'@'host'; 2. Use the REVOKE statement to revoke permissions, such as REVOKEALLPRIVILEGESONdatabase_name.FROM'username'@'host' to ensure timely communication of permission changes.

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Show More

Hot Article

Assassin's Creed Shadows: Seashell Riddle Solution
1 months agoByDDD
What's New in Windows 11 KB5054979 & How to Fix Update Issues
3 weeks agoByDDD
Where to find the Crane Control Keycard in Atomfall
1 months agoByDDD
How to fix KB5055523 fails to install in Windows 11?
2 weeks agoByDDD
InZoi: How To Apply To School And University
3 weeks agoByDDD
Show More

Hot Tools

PhpStorm Mac version

PhpStorm Mac version

The latest (2018.2.1) professional PHP integrated development tool

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

Powerful PHP integrated development environment

DVWA

DVWA

Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software

Atom editor mac version download

Atom editor mac version download

The most popular open source editor

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

Show More

Hot Topics

Where is the login entrance for gmail email?
7770
15
Java Tutorial
1644
14
CakePHP Tutorial
1399
52
Laravel Tutorial
1296
25
PHP Tutorial
1234
29
Show More