数据作业中的一题：MySql函数

一、题目描述

新建函数，统计user_reg表中work_place字段的总数和各地所占的百分比。（如work_place总数为100，北京的百分比为8%，天津…等等）。

user_reg表：

二、思路

1. 建立一个统计的表place_percent

2. 先写出一条sql语句，可以查询user_reg表中work_place字段的总数和各地所占的百分比

   SELECT
   	work_place,
   	count(*) AS number,
   	total_number,
   	CONCAT( '    ', ROUND( count(*)*100/ total_number, 2 ), '%' ) AS percent 
   FROM
   	user_reg,(
   	SELECT
   		count(*) AS total_number 
   	FROM
   		user_reg 
   	) m 
   GROUP BY
   	work_place;
   

   通过对work_place分组，再用count()函数可以得到每个组的数量（起个别名number）；

   建立一个中间表m，存有work_place的总数；

   ROUND( count(*)*100/ total_number, 2 )，计算百分比，保留两位小数；

   CONCAT（）拼接字符串；

   查询结果：

3. 把sql语句放入函数

   CREATE DEFINER=`root`@`localhost` FUNCTION `aaaa`() RETURNS int
       DETERMINISTIC
   BEGIN
   	#Routine body goes here...
   	DECLARE	name_tmp VARCHAR ( 255 );
   	DECLARE	part_total int;
   	DECLARE	total int;
   	DECLARE	percent_tmp VARCHAR ( 255 );
   	DECLARE	done INTEGER DEFAULT 0;
   	DECLARE cu CURSOR FOR SELECT work_place,percent,total_number,number
   				FROM 
   					(SELECT	
   						work_place,	count(*) as number ,total_number,CONCAT('     ',ROUND( count(*)*100/ total_number, 2 ), '%' ) AS percent 
   					FROM
   						user_reg,(
   						SELECT	
   							count(*) AS total_number 
   						FROM 
   						user_reg ) m 
   					GROUP BY
   						work_place)n;				
   	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;	
   	OPEN cu ;
   	FETCH cu into name_tmp,percent_tmp,total,part_total;
   	
   	WHILE done <> 1 DO	
   	 INSERT INTO quickcast.place_percent (`name`,percent,total_number,the_number) VALUES (name_tmp,percent_tmp,total,part_total);	
   	FETCH cu INTO name_tmp,percent_tmp,total,part_total;		
   	END WHILE;	
   	
   	RETURN 0;
   END
   

   第10，24，27使用游标，将sql语句得到的work_place,percent,total_number,number放入表place_percent；

   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1它的含义是：若没有数据返回,程序继续,并将变量FOUND设为1 ，这种情况是出现在select XX into XXX from tablename的时候发生的。

   执行结果：

   

   表place_percent：
   

4. 原题还要有事件，每20s执行一次函数，此处不再写。

三、注意

上述sql语句可能出现1055错误: this is incompatible with sql_mode=only_full_group_by。（mysql 5.7版本默认的sql配置是:sql_mode=“ONLY_FULL_GROUP_BY”，这个配置严格执行了"SQL92标准"。）

解决方法，将sql语句改为(用了笛卡尔积)：（舍友提供此法，他的mysql的报错）

SELECT
	*,CONCAT('   ', ROUND( total_number * 100 / total_number , 2 ), '%' ) AS percent 
FROM
(
	SELECT
		work_place,
		count(*) AS number 
	FROM
		user_reg 
	GROUP BY
		work_place
) as m 
cross join (
	select count(*) as total_number from user_reg
) as n;