-- 我的一个函数
-- 其作用是生成一个可执行的SQL语句,
-- 目的是生成“查询一张表的int类型字段的所有max、min、avg值”的SQL语句的函数:DELIMITER $$CREATE FUNCTION get_tb_numberInfo(v_tb_name varchar(50))
RETURNS VARCHAR(2000) CHARSET utf8
BEGIN
DECLARE v_sql varchar(20000); DECLARE v_column_name varchar(200);
DECLARE v_column_name_min varchar(200);
DECLARE v_column_name_max varchar(200);
DECLARE v_column_name_avg varchar(200);
DECLARE no_more_departments boolean; DECLARE get_tbInfo CURSOR FOR
SELECT column_name FROM information_schema.columns
WHERE UPPER(TABLE_NAME)=upper(v_tb_name)
AND TABLE_SCHEMA=(select database())
AND DATA_TYPE IN('bigint','int','decimal'); DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_departments=1;
SET no_more_departments=0;
SET v_sql := 'SELECT ';
OPEN get_tbInfo;
REPEAT
-- Get get_tbInfo
FETCH get_tbInfo INTO v_column_name;
SET v_column_name_min = concat('min(',v_column_name,') as ',v_column_name,'_min,');
SET v_column_name_max = concat('max(',v_column_name,') as ',v_column_name,'_max,');
SET v_column_name_avg = concat('avg(',v_column_name,') as ',v_column_name,'_avg,');
SET v_sql = concat(v_sql,v_column_name_min,v_column_name_max,v_column_name_avg,'\n');
UNTIL no_more_departments
END REPEAT;
CLOSE get_tbInfo; SET v_sql = substring(v_sql,1,length(v_sql)-1);
SET v_sql = concat(v_sql,' from ',v_tb_name,';'); RETURN v_sql;
END;
$$delimiter ;-- 但是:我每次执行后,最后一个字段生成了两次:min、max、avg字段,例如:
mysql> select get_tb_numberInfo('f_union_refer_date_index'); SELECT
min(date_id) as date_id_min,max(date_id) as date_id_max,avg(date_id) as date_id_avg,
min(refer_id) as refer_id_min,max(refer_id) as refer_id_max,avg(refer_id) as refer_id_avg,
min(ip_num) as ip_num_min,max(ip_num) as ip_num_max,avg(ip_num) as ip_num_avg,
min(direct_pv_num) as direct_pv_num_min,max(direct_pv_num) as direct_pv_num_max,avg(direct_pv_num) as direct_pv_num_avg,
min(indirect_pv_num) as indirect_pv_num_min,max(indirect_pv_num) as indirect_pv_num_max,avg(indirect_pv_num) as indirect_pv_num_avg,
min(direct_cv_num) as direct_cv_num_min,max(direct_cv_num) as direct_cv_num_max,avg(direct_cv_num) as direct_cv_num_avg,
min(indirect_cv_num) as indirect_cv_num_min,max(indirect_cv_num) as indirect_cv_num_max,avg(indirect_cv_num) as indirect_cv_num_avg,
min(direct_uv_num) as direct_uv_num_min,max(direct_uv_num) as direct_uv_num_max,avg(direct_uv_num) as direct_uv_num_avg,
min(new_uv_num) as new_uv_num_min,max(new_uv_num) as new_uv_num_max,avg(new_uv_num) as new_uv_num_avg,
min(new_uv_num) as new_uv_num_min,max(new_uv_num) as new_uv_num_max,avg(new_uv_num) as new_uv_num_avg from f_union_refer_date_index;-- 请问是怎么回事儿呢?有什么办法解决不?
-- 其作用是生成一个可执行的SQL语句,
-- 目的是生成“查询一张表的int类型字段的所有max、min、avg值”的SQL语句的函数:DELIMITER $$CREATE FUNCTION get_tb_numberInfo(v_tb_name varchar(50))
RETURNS VARCHAR(2000) CHARSET utf8
BEGIN
DECLARE v_sql varchar(20000); DECLARE v_column_name varchar(200);
DECLARE v_column_name_min varchar(200);
DECLARE v_column_name_max varchar(200);
DECLARE v_column_name_avg varchar(200);
DECLARE no_more_departments boolean; DECLARE get_tbInfo CURSOR FOR
SELECT column_name FROM information_schema.columns
WHERE UPPER(TABLE_NAME)=upper(v_tb_name)
AND TABLE_SCHEMA=(select database())
AND DATA_TYPE IN('bigint','int','decimal'); DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_departments=1;
SET no_more_departments=0;
SET v_sql := 'SELECT ';
OPEN get_tbInfo;
REPEAT
-- Get get_tbInfo
FETCH get_tbInfo INTO v_column_name;
SET v_column_name_min = concat('min(',v_column_name,') as ',v_column_name,'_min,');
SET v_column_name_max = concat('max(',v_column_name,') as ',v_column_name,'_max,');
SET v_column_name_avg = concat('avg(',v_column_name,') as ',v_column_name,'_avg,');
SET v_sql = concat(v_sql,v_column_name_min,v_column_name_max,v_column_name_avg,'\n');
UNTIL no_more_departments
END REPEAT;
CLOSE get_tbInfo; SET v_sql = substring(v_sql,1,length(v_sql)-1);
SET v_sql = concat(v_sql,' from ',v_tb_name,';'); RETURN v_sql;
END;
$$delimiter ;-- 但是:我每次执行后,最后一个字段生成了两次:min、max、avg字段,例如:
mysql> select get_tb_numberInfo('f_union_refer_date_index'); SELECT
min(date_id) as date_id_min,max(date_id) as date_id_max,avg(date_id) as date_id_avg,
min(refer_id) as refer_id_min,max(refer_id) as refer_id_max,avg(refer_id) as refer_id_avg,
min(ip_num) as ip_num_min,max(ip_num) as ip_num_max,avg(ip_num) as ip_num_avg,
min(direct_pv_num) as direct_pv_num_min,max(direct_pv_num) as direct_pv_num_max,avg(direct_pv_num) as direct_pv_num_avg,
min(indirect_pv_num) as indirect_pv_num_min,max(indirect_pv_num) as indirect_pv_num_max,avg(indirect_pv_num) as indirect_pv_num_avg,
min(direct_cv_num) as direct_cv_num_min,max(direct_cv_num) as direct_cv_num_max,avg(direct_cv_num) as direct_cv_num_avg,
min(indirect_cv_num) as indirect_cv_num_min,max(indirect_cv_num) as indirect_cv_num_max,avg(indirect_cv_num) as indirect_cv_num_avg,
min(direct_uv_num) as direct_uv_num_min,max(direct_uv_num) as direct_uv_num_max,avg(direct_uv_num) as direct_uv_num_avg,
min(new_uv_num) as new_uv_num_min,max(new_uv_num) as new_uv_num_max,avg(new_uv_num) as new_uv_num_avg,
min(new_uv_num) as new_uv_num_min,max(new_uv_num) as new_uv_num_max,avg(new_uv_num) as new_uv_num_avg from f_union_refer_date_index;-- 请问是怎么回事儿呢?有什么办法解决不?
解决方案 »
- MySQL中存储过程stored procedure中报错,END这里画浪线啊
- mysql_real_query总是返回1
- 该安装哪个MYSQL?
- 如何实现对mysql数据库的100M文件读写?
- 面试题 求学生平均分大于n的学生
- group by一列数据,但是要合并数据,有个函数忘记了,谢谢帮助
- 搞了一天了,头昏眼花,jsp + mysql 3.22.46, 怎么也联不上,高手帮忙!
- 实在搞不定了,MYSQL 5.6 一个数据库中文乱码
- decimal跟VARBINARY的问题
- mysql中文为啥查不出来
- Navicat8 显示数据库中的表中文乱码,同时输入中文出错:1366。
- 一句select语句无法使用索引的问题
DROP FUNCTION IF EXISTS get_tb_numberInfo$$
CREATE FUNCTION get_tb_numberInfo(v_tb_name VARCHAR(50))
RETURNS VARCHAR(2000) CHARSET utf8
BEGIN
DECLARE v_sql VARCHAR(20000); DECLARE v_column_name VARCHAR(200);
DECLARE v_column_name_min VARCHAR(200);
DECLARE v_column_name_max VARCHAR(200);
DECLARE v_column_name_avg VARCHAR(200);
DECLARE no_more_departments BOOLEAN; DECLARE get_tbInfo CURSOR FOR
SELECT column_name FROM information_schema.columns
WHERE UPPER(TABLE_NAME)=UPPER(v_tb_name)
AND TABLE_SCHEMA=(SELECT DATABASE())
AND DATA_TYPE IN('bigint','int','decimal'); DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_departments=1;
SET no_more_departments=0;
SET v_sql := 'SELECT ';
OPEN get_tbInfo;
WHILE no_more_departments=0 DO
-- Get get_tbInfo
FETCH get_tbInfo INTO v_column_name;
SET v_column_name_min = CONCAT('min(',v_column_name,') as ',v_column_name,'_min,');
SET v_column_name_max = CONCAT('max(',v_column_name,') as ',v_column_name,'_max,');
SET v_column_name_avg = CONCAT('avg(',v_column_name,') as ',v_column_name,'_avg,');
SET v_sql = CONCAT(v_sql,v_column_name_min,v_column_name_max,v_column_name_avg,'\n');
-- select v_sql;
FETCH get_tbInfo INTO v_column_name;
END WHILE ;
CLOSE get_tbInfo; SET @v_sql = SUBSTRING(v_sql,1,LENGTH(v_sql)-1);
-- SELECT @v_sql;
SET v_sql = CONCAT(@v_sql,' from ',v_tb_name,';'); RETURN v_sql;
END;
$$DELIMITER ;SELECT get_tb_numberInfo('attachment');
FETCH get_tbInfo INTO v_column_name;
REPEAT
-- Get get_tbInfo
SET v_column_name_min = concat('min(',v_column_name,') as ',v_column_name,'_min,');
SET v_column_name_max = concat('max(',v_column_name,') as ',v_column_name,'_max,');
SET v_column_name_avg = concat('avg(',v_column_name,') as ',v_column_name,'_avg,');
SET v_sql = concat(v_sql,v_column_name_min,v_column_name_max,v_column_name_avg,'\n');
FETCH get_tbInfo INTO v_column_name;
UNTIL no_more_departments
END REPEAT;
-- 这样是没有重复生成SQL语句的字段啦,但是:好像又少了些字段没有生成SQL语句!
mysql> desc f_union_date_uid_index;
+-----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| date_id | int(8) | NO | PRI | NULL | |
| union_id | varchar(10) | NO | PRI | NULL | |
| out_ip_num | int(10) | YES | | NULL | |
| out_cv_num | int(8) | YES | | NULL | |
| ip_num | int(10) | YES | | NULL | |
| direct_pv_num | int(10) | YES | | NULL | |
| indirect_pv_num | int(10) | YES | | NULL | |
| direct_cv_num | int(10) | YES | | NULL | |
| indirect_cv_num | int(10) | YES | | NULL | |
| direct_uv_num | int(10) | YES | | NULL | |
| new_uv_num | int(10) | YES | | NULL | |
+-----------------+-------------+------+-----+---------+-------+
11 rows in set (0.00 sec)-- 但是执行2楼你的存储过程,只得到了6个字段生成的SQL语句: SELECT min(date_id) as date_id_min,max(date_id) as date_id_max,avg(date_id) as date_id_avg,
min(out_cv_num) as out_cv_num_min,max(out_cv_num) as out_cv_num_max,avg(out_cv_num) as out_cv_num_avg,
min(direct_pv_num) as direct_pv_num_min,max(direct_pv_num) as direct_pv_num_max,avg(direct_pv_num) as direct_pv_num_avg,
min(direct_cv_num) as direct_cv_num_min,max(direct_cv_num) as direct_cv_num_max,avg(direct_cv_num) as direct_cv_num_avg,
min(direct_uv_num) as direct_uv_num_min,max(direct_uv_num) as direct_uv_num_max,avg(direct_uv_num) as direct_uv_num_avg,
min(new_uv_num) as new_uv_num_min,max(new_uv_num) as new_uv_num_max,avg(new_uv_num) as new_uv_num_avg, from f_union_date_uid_index;
RETURNS VARCHAR(2000) CHARSET utf8
BEGIN
DECLARE v_sql varchar(20000); DECLARE v_column_name varchar(200);
DECLARE v_column_name_min varchar(200);
DECLARE v_column_name_max varchar(200);
DECLARE v_column_name_avg varchar(200);
DECLARE no_more_departments INT; DECLARE get_tbInfo CURSOR FOR
SELECT column_name FROM information_schema.columns
WHERE UPPER(TABLE_NAME)=upper(v_tb_name)
AND TABLE_SCHEMA=(select database())
AND DATA_TYPE IN('bigint','int','decimal'); DECLARE CONTINUE HANDLER FOR NOT FOUND
SET no_more_departments=1;
SET no_more_departments=0;
SET v_sql := 'SELECT ';
OPEN get_tbInfo;
REPEAT
-- Get get_tbInfo
FETCH get_tbInfo INTO v_column_name;
IF no_more_departments = 0 THEN
SET v_column_name_min = concat('min(',v_column_name,') as ',v_column_name,'_min,');
SET v_column_name_max = concat('max(',v_column_name,') as ',v_column_name,'_max,');
SET v_column_name_avg = concat('avg(',v_column_name,') as ',v_column_name,'_avg,');
SET v_sql = concat(v_sql,'\n',v_column_name_min,v_column_name_max,v_column_name_avg);
END IF;
UNTIL no_more_departments = 1
END REPEAT;
CLOSE get_tbInfo; SET v_sql = substring(v_sql,1,length(v_sql)-1);
SET v_sql = concat(v_sql,' from ',v_tb_name,';'); RETURN v_sql;
END;
$$
DELIMITER ;