多表数据统计汇总入新的记录表中

mysql默认结束标识符为";",但是有多个语句需要执行,所以定义$$为一句sql的结束标志,取消";"的所代表的意义;

delimiter $$

如果存在名字为recyclingStatistical的procedure(存储过程)则删除;

DROP PROCEDURE IF EXISTS bookStatistical;

创建（创建函数使用的关键字为function 函数名()）;

CREATE PROCEDURE bookStatistical()
BEGIN
# 声明变量
DECLARE b_day varchar(10);
DECLARE b_id int;
# 设置游标初始值为0;
DECLARE flag INT DEFAULT 0;
# DECLARE(定义变量) CURSOR(游标=数组中的下标,标识数据去到什么地方了) 记录sql查询的结果;
DECLARE id_list CURSOR FOR SELECT LEFT(click_time,10),book_id FROM t_book_click WHERE click_time < ‘2019-12-04 00:00:00’ GROUP BY LEFT(click_time,10),book_id ORDER BY LEFT(click_time,10),book_id;
# 为下面while循环建立一个退出标志，当游标遍历完后将flag的值设置为1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 1;
# 打开定义的游标id_list;
OPEN id_list;
# 将游标中的值赋给定义好的变量，实现for循环的要点;
FETCH id_list INTO b_day,b_id;

	WHILE flag <> 1 DO
		# 在T-SQL中,局部变量必须以@作为前缀,声明方式set,select还有点差别;
		SET @book_name = '';
		SET @book_gender = -1;#男女频（0 男 1女）
		SET @order_num = 0;#订阅数 
		SET @chapter_num = 0;#付费章节数
		SET @order_book_num = 0;#订阅人数
		SET @book_clicknum = 0;#真实点击数
		SET @click_num = 0;#真实点击人数 					
		SET @order_all_num = 0.00;#总均订 = 订阅数 / 付费章节数
		SET @order_per_capita_num = 0.00;#人均订 = 订阅数 / 订阅人数
		SET @orderRate = 0.00;#转订率 = 订阅人数 / 真实点击人数

		IF (SELECT COUNT(*) FROM t_book WHERE book_id = b_id) > 0 THEN 
			SET @book_name = (SELECT book_name_cn FROM t_book WHERE book_id = b_id);
			SET @book_gender = (SELECT book_gender FROM t_book WHERE book_id = b_id);
		END IF;
		# 根据游标中存储的日期和书籍id查询出该书在存储日期内的订阅数,更新到声明的变量中;
		IF (SELECT COUNT(*) FROM t_member_consume WHERE book_id = b_id AND LEFT(consume_time,10) = b_day) > 0 THEN 
			SET @order_num = (SELECT COUNT(*) FROM t_member_consume WHERE book_id = b_id AND LEFT(consume_time,10) = b_day);
		END IF;
		#付费章节数
		IF (SELECT COUNT(*) FROM t_book_chapter WHERE book_id = b_id AND chapter_ispay = 1) > 0 THEN 
			SET @chapter_num = (SELECT COUNT(*) FROM t_book_chapter WHERE book_id = b_id AND chapter_ispay = 1);
			#总均订 = 订阅数 / 付费章节数
			SET @order_all_num = @order_num / @chapter_num;
		END IF;
		#订阅人数
		IF (SELECT COUNT(*) FROM t_member_order_book WHERE book_id = b_id AND LEFT(createtime,10) = b_day) > 0 THEN 
			SET @order_book_num = (SELECT COUNT(*) FROM t_member_order_book WHERE book_id = b_id AND LEFT(createtime,10) = b_day);
			#人均订 = 订阅数 / 订阅人数
			SET @order_per_capita_num = @order_num / @order_book_num;
		END IF;
		#真实点击数
		IF (SELECT COUNT(*) FROM t_book_click WHERE book_id = b_id AND LEFT(click_time,10) = b_day) > 0 THEN 
			SET @book_clicknum = (SELECT COUNT(*) FROM t_book_click WHERE book_id = b_id AND LEFT(click_time,10) = b_day);
		END IF;
		#真实点击人数
		IF (SELECT COUNT(DISTINCT member_id) FROM t_book_click WHERE book_id = b_id AND LEFT(click_time,10) = b_day) > 0 THEN 
			SET @click_num = (SELECT COUNT(DISTINCT member_id) FROM t_book_click WHERE book_id = b_id AND LEFT(click_time,10) = b_day);
			#转订率 = 订阅人数 / 真实点击人数
			SET @orderRate = @order_book_num / @click_num;
		END IF;
		
		INSERT INTO t_book_statistical(
			book_day,
			book_id,
			book_name,
			book_gender,
			order_num,
			order_all_num,
			order_book_num,
			order_per_capita_num,
			book_clicknum,
			click_num,
			orderRate
		) VALUES(
			b_day,
			b_id,
			@book_name,
			@book_gender,
			@order_num,
			@order_all_num,
			@order_book_num,
			@order_per_capita_num,
			@book_clicknum,
			@click_num,
			@orderRate
		);

		# 游标往后移(相当于C里面的指针);
		FETCH id_list INTO b_day,b_id;
	END WHILE;
# 关闭游标
CLOSE id_list;

END $$

重新定义";“为一句sql的结束标志,取消之前定义”$$"的所代表意义;

delimiter ;

调用创建的函数

CALL bookStatistical();