/**
案例:每天凌晨统计记录一下每个用户当前的进账跟支出
**/
金额记录表:test_user_log
列名 | 类型 | 大小 | 备注 | |
id | int | 11 | ||
type | tinyint | 1 | 类型(1 为进,2为出) | |
price | decimal | (12,6) | 金额 | |
user_id | int | 11 | 用户ID | |
addtime | datetime | 操作时间 |
金额统计表:test_system_total
字段名 | 类型 | 大小 | 备注 |
id | int | 11 | 主键id。自增 |
user_id | int | 11 | 用户id |
all_in | decimal | (12,6) | 当前收入金额 |
all_out | decimal | (12,6) | 当前支出金额 |
addtime | datetime | 记录时间 |
2:写个存储过程:
BEGIN
INSERT INTO test_system_total(user_id,allin,allout,addtime)(SELECT a.user_id,IFNULL(i.inprice,0) AS inprice,IFNULL(o.outprice,0) AS outprice,NOW() FROM (SELECT user_id FROM test_user_log GROUP BY user_id) a
LEFT JOIN (SELECT user_id,sum(price) AS inprice FROM test_user_log WHERE type=1 GROUP BY user_id) i ON a.user_id=i.user_id
LEFT JOIN (SELECT user_id,sum(price) AS outprice FROM test_user_log WHERE type=2 GROUP BY user_id) o ON a.user_id=o.user_id);
END
3: 之后再写个事件,就可以每天定时执行语句,统计到test_system_total 表中