pay统计

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

#如果存在名字为recyclingStatistical的procedure(存储过程)则删除;
DROP PROCEDURE IF EXISTS recyclingStatisticalPay;

#创建（创建函数使用的关键字为function 函数名()）;
CREATE PROCEDURE recyclingStatisticalPay()
BEGIN

声明变量

DECLARE p_time varchar(20);
DECLARE m_id int;
DECLARE p_type int;
DECLARE p_money int;

设置游标初始值为0;

DECLARE flag INT DEFAULT 0;

DECLARE(定义变量) CURSOR(游标=数组中的下标,标识数据去到什么地方了) 记录sql查询的结果;

DECLARE id_list CURSOR FOR SELECT member_pay_time,member_id,member_pay_type,member_pay_money FROM t_member_pay WHERE member_pay_third_orderid NOT LIKE ‘10000005%’ AND member_pay_time < ‘2020-01-13 00:00:00’;

为下面while循环建立一个退出标志，当游标遍历完后将flag的值设置为1;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 1;

打开定义的游标id_list;

OPEN id_list;

将游标中的值赋给定义好的变量，实现for循环的要点;

FETCH id_list INTO p_time,m_id,p_type,p_money;#p_type:充值方式: 1 applePay, 2 googlePay

WHILE flag <> 1 DO
	# 在T-SQL中,局部变量必须以@作为前缀,声明方式set,select还有点差别;
	SET @totalRegisteredNum = 0;#总首充人数
	SET @iosRegisteredNum = 0;#ios首充人数
	SET @androidRegisteredNum = 0;#安卓首充人数
	SET @dayTotalRecovery = 0;#当日合计回收
	SET @dayIosRecovery = 0;#当日ios回收
	SET @dayAndroidRecovery = 0;#当日安卓回收
	SET @totalHistoricalRecovery = 0;#合计历史回收
	SET @iosHistoricalRecovery = 0;#ios历史回收
	SET @androidHistoricalRecovery = 0;#安卓历史回收

	#判断这个用户在这个时间点之前是否有充值记录
	#有充值记录 非首充
	IF(SELECT COUNT(*) FROM t_member_pay WHERE member_id = m_id AND member_pay_time < LEFT(p_time,10)) > 0 THEN
		SET @oldTime = (SELECT MIN(member_pay_time) FROM t_member_pay WHERE member_id = m_id AND member_pay_time < LEFT(p_time,10));#最早充值日期
		#有统计数据 更新
		IF(SELECT COUNT(*) FROM t_recycling_statistical_pay WHERE recycling_day = LEFT(@oldTime,10)) > 0 THEN
			#总历史回收
			UPDATE t_recycling_statistical_pay SET total_historical_recovery = total_historical_recovery + p_money WHERE recycling_day = LEFT(@oldTime,10);
			#ios历史回收
			IF p_type = 1 THEN
				UPDATE t_recycling_statistical_pay SET ios_historical_recovery = ios_historical_recovery + p_money WHERE recycling_day = LEFT(@oldTime,10);
			#安卓历史回收
			ELSEIF p_type = 2 THEN	
				UPDATE t_recycling_statistical_pay SET android_historical_recovery = android_historical_recovery + p_money WHERE recycling_day = LEFT(@oldTime,10);
			END IF;
		END IF;
	#无充值记录 是首充
	ELSE

		#判断这个时间之前是否有充值 没有充值 是首充人
		IF(SELECT COUNT(*) FROM t_member_pay WHERE member_id = m_id AND member_pay_time < p_time) = 0 THEN
			#总首充人数+ 1
			SET @totalRegisteredNum = 1;
			#ios首充人数+ 1
			IF p_type = 1 THEN 
				SET @iosRegisteredNum = 1;
			#安卓首充人数+ 1
			ELSEIF p_type = 2 THEN	
				SET @androidRegisteredNum = 1;
			END IF;
		END IF;

		#当日合计回收+ p_money
		SET @dayTotalRecovery = p_money;
		#当日ios回收+ p_money
		IF p_type = 1 THEN 
			SET @dayIosRecovery = p_money;
		#当日安卓回收+ p_money
		ELSEIF p_type = 2 THEN	
			SET @dayAndroidRecovery = p_money;
		END IF;
		
		IF(SELECT COUNT(*) FROM t_recycling_statistical_pay WHERE recycling_day = LEFT(p_time,10)) > 0 THEN
			UPDATE t_recycling_statistical_pay SET 
				total_registered_num = total_registered_num + @totalRegisteredNum,
				ios_registered_num = ios_registered_num + @iosRegisteredNum,
				android_registered_num = android_registered_num + @androidRegisteredNum,
				day_total_recovery = day_total_recovery + @dayTotalRecovery,
				day_ios_recovery = day_ios_recovery + @dayIosRecovery,
				day_android_recovery = day_android_recovery + @dayAndroidRecovery,
				total_historical_recovery = total_historical_recovery + @dayTotalRecovery,
				ios_historical_recovery = ios_historical_recovery + @dayIosRecovery,
				android_historical_recovery = android_historical_recovery + @dayAndroidRecovery
			WHERE recycling_day = LEFT(p_time,10);
		ELSE 
			INSERT INTO t_recycling_statistical_pay(
				recycling_day,
				total_registered_num,
				ios_registered_num,
				android_registered_num,
				day_total_recovery,
				day_ios_recovery,
				day_android_recovery,
				total_historical_recovery,
				ios_historical_recovery,
				android_historical_recovery
			) VALUES(
				LEFT(p_time,10),
				@totalRegisteredNum,
				@iosRegisteredNum,
				@androidRegisteredNum,
				@dayTotalRecovery,
				@dayIosRecovery,
				@dayAndroidRecovery,
				@dayTotalRecovery,
				@dayIosRecovery,
				@dayAndroidRecovery
			);
		END IF;

	END IF;

	# 游标往后移(相当于C里面的指针);
	FETCH id_list INTO p_time,m_id,p_type,p_money;
END WHILE;

关闭游标

CLOSE id_list;
END $$

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

#调用创建的函数
CALL recyclingStatisticalPay();