前言
这一篇主要讲解存储过程的小技巧
1、统计类型为1的人数和个数和总人数
证书等级数据(等级 1普通证书人数 2高级 3特级)certificate_task_flow
SELECT SUM(IF(certificate_type = 1, 1,0)) prs,
count(DISTINCT account_id) rs
from certificate_level_award_flow
2、if判断
IF(accountId IS NULL) THEN
SELECT accountId;
SET is_new_acc = 0;
END IF;
3、使用游标
DECLARE done VARCHAR(2) DEFAULT 0; -- 遍历数据结束标志
-- 将结束标志绑定到游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN user_cur; -- 打开游标
REPEAT -- 循环
FETCH user_cur INTO accountIds; -- 提取游标
IF done <> 1 THEN
-- 判断(可无,非游标里面内容)
IF(accountId IS NULL) THEN
SELECT accountId;
SET is_new_acc = 0;
END IF;
--逻辑
END IF;
UNTIL done = 1 END REPEAT; -- 结束条件
CLOSE user_cur;-- 关闭游标
4、拼接执行体
-- 更新破产统计表
SET createTableSQL = CONCAT(" UPDATE ",tableName," set is_new_acc = ",is_new_acc," where account_id = ",accountIds," ");
-- 创建表语句
SET @v_sql=createTableSQL;
PREPARE stmt FROM @v_sql;
## 执行SQL语句
EXECUTE stmt;
## 释放掉预处理段
deallocate prepare stmt;
5、修改语句
-- 更新破产统计表
UPDATE tableName set is_new_acc = 1 where account_id = 2