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
演示win7调整屏幕亮度的方法演示win7调整屏幕亮度的方法Jul 08, 2023 pm 07:49 PM

不同的电脑系统在调整屏幕亮度的操作方法上会有些不同，最近就有使用win7系统的网友不知道win7怎么调整屏幕亮度，看久了电脑眼睛比较酸痛。下面小编就教下大家win7调整屏幕亮度的方法。具体的操作步骤如下：1、点击win7电脑左下角的“开始”，在弹出的开始菜单中选择“控制面板”打开。2、在打开的控制面板中找到“电源选项”打开。3、也可以用鼠标右键电脑右下角的电源图标，在弹出的菜单中，点击“调整屏幕亮度”，如下图所示。两种方法都可以用。4、在打开的电源选项窗口的最下面可以看到屏幕亮度调整的滚动条，直

win10监控摄像头打开照片的方法win10监控摄像头打开照片的方法Jul 10, 2023 pm 09:41 PM

如果我们手头没有手机，只有电脑，但我们必须拍照，我们可以使用电脑内置的监控摄像头拍照，那么如何打开win10监控摄像头，事实上，我们只需要下载一个相机应用程序。打开win10监控摄像头的具体方法。win10监控摄像头打开照片的方法:1.首先，盘快捷键Win+i打开设置。2.打开后，进入个人隐私设置。3.然后在相机手机权限下打开访问限制。4.打开后，您只需打开相机应用软件。(如果没有，可以去微软店下载一个)5.打开后，如果计算机内置监控摄像头或组装了外部监控摄像头，则可以拍照。(因为人们没有安装摄

基于Java的机器视觉实践和方法介绍基于Java的机器视觉实践和方法介绍Jun 18, 2023 am 11:21 AM

随着科技的不断发展，机器视觉技术在各个领域得到了广泛应用，如工业自动化、医疗诊断、安防监控等。Java作为一种流行的编程语言，其在机器视觉领域也有着重要的应用。本文将介绍基于Java的机器视觉实践和相关方法。一、Java在机器视觉中的应用Java作为一种跨平台的编程语言，具有跨操作系统、易于维护、高度可扩展等优点，对于机器视觉的应用具有一定的优越性。Java

win7怎么调屏幕亮度的两种简单方法win7怎么调屏幕亮度的两种简单方法Jul 08, 2023 pm 06:33 PM

目前有很多屏幕亮度调整软件，我们可以通过使用软件进行快速调整或者通过显示器上自带的亮度功能进行调整。以下是详细的Win7屏幕亮度调整方式，您可以通过教程中的方法进行快速调整即可。Win7系统电脑怎么调节屏幕亮度教程：1、依次点击“计算机—右键—控制面板”，如果没有也可以在搜索框中进行搜索。2、点击控制面板下的“硬件和声音”，或者点击“外观和个性化”都可以。3、点击“NVIDIA控制面板”，有些显卡可能是AMD或者Intel的，请根据实际情况选择。4、调节图示中亮度滑块即可。5、还有一种方法，就是

Go 语言中的方法是怎样定义和使用的？Go 语言中的方法是怎样定义和使用的？Jun 10, 2023 am 08:16 AM

Go语言是近年来备受青睐的编程语言，因其简洁、高效、并发等特点而备受开发者喜爱。其中，方法（Method）也是Go语言中非常重要的概念。接下来，本文就将详细介绍Go语言中方法的定义和使用。一、方法的定义Go语言中的方法是带有接收器（Receiver）的函数，它是一个与某个类型绑定的函数。接收器可以是值类型或者指针类型。用于接收者的参数可以在方法名

PHP文件下载方法及常见问题解答PHP文件下载方法及常见问题解答Jun 09, 2023 pm 12:37 PM

PHP是一个广泛使用的服务器端编程语言，它的许多功能和特性可以将其用于各种任务，包括文件下载。在本文中，我们将了解如何使用PHP创建文件下载脚本，并解决文件下载过程中可能出现的常见问题。一、文件下载方法要在PHP中下载文件，我们需要创建一个PHP脚本。让我们看一下如何实现这一点。创建下载文件的链接通过HTML或PHP在页面上创建一个链接，让用户能够下载文件。

Vue 中的 createApp 方法是什么？Vue 中的 createApp 方法是什么？Jun 11, 2023 am 11:25 AM

随着前端开发的快速发展，越来越多的框架被用来构建复杂的Web应用程序。Vue.js是流行的前端框架之一，它提供了许多功能和工具来简化开发人员构建高质量的Web应用程序。createApp()方法是Vue.js中的一个核心方法之一，它提供了一种简单的方式来创建Vue实例和应用程序。本文将深入探讨Vue中createApp方法的作用，其如何使用以及使用时需要了解

win10手动关闭自动更新方法win10手动关闭自动更新方法Jul 08, 2023 pm 11:53 PM

Win102019ltsc已经是一个非常稳定的系统，不会有太更新，但仍会有定期的安全更新。一些用户认为系统更新非常烦人，所以他们想关闭这个功能。让我们来看看手动关闭自动更新的方法。一、关闭WindowsUpdate服务。我的电脑右键→管理。打开计算机管理→服务和应用程序→服务找到Windowsupdate双击打开属性。默认情况下，更新是手动或自动的。如果我们想禁止更新，我们需要在点击应用程序确定后将启动类型更改为禁止，以关闭自动更新。二、通过命令行关闭WindowsUpdate服务。以下命令可以

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

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Show More

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
1 months agoBy尊渡假赌尊渡假赌尊渡假赌
How Long Does It Take To Beat Split Fiction?
4 weeks agoByDDD
R.E.P.O. Save File Location: Where Is It & How to Protect It?
4 weeks agoByDDD
Two Point Museum: All Exhibits And Where To Find Them
1 months agoBy尊渡假赌尊渡假赌尊渡假赌
Show More

Hot Tools

MantisBT

MantisBT

Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

Atom editor mac version download

Atom editor mac version download

The most popular open source editor

Dreamweaver Mac version

Dreamweaver Mac version

Visual web development tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 English version

SublimeText3 English version

Recommended: Win version, supports code prompts!

Show More

Hot Topics

Where is the login entrance for gmail email?
7378
15
Java Tutorial
1628
14
CakePHP Tutorial
1357
52
Laravel Tutorial
1267
25
PHP Tutorial
1216
29
Show More