【卢公子讲java-01】在深工作中遇到的一些存储过程问题

只贴sql。有不足的地方，有兴趣的博友们可以一起讨论一下。
1，pro_audit_flow_website

BEGIN
DECLARE v_count INT DEFAULT 0;
//查询网址表中昨天的数据
select count(id) countalarm into v_count from t_audit_flow_website_detail
where createtime = DATE_SUB(curdate(),INTERVAL 1 DAY);
//查询后新增到网址表
if v_count > 0 then
insert into t_audit_flow_website(user_name,user_group,ip,type,visit_count,flow_all,flow_up,flow_down,insert_date)
//把查询出来的结果新增到网址表
select t.user_name,t.user_group,ip,t.type,sum(tp.visit_count) visit_count,sum(t.flow_up+t.flow_down) flow_all
,sum(t.flow_up) flow_up,sum(t.flow_down) flow_down,t.insert_date from t_audit_flow_website_detail t
left join (select type,count(*) visit_count from t_audit_flow_website tp group by type) tp on t.type = tp.type
where t.insert_date = DATE_SUB(curdate(),INTERVAL 1 DAY)
group by user_name,user_group,type,insert_date;
end if;
END

2，proc_real_flow_total_snmp snmp拉到流量记录并进行统计新增到表里
declare _week varchar(50) default CONCAT(DATE_SUB(CURDATE(), INTERVAL 7 DAY),’ 00:00:00’);
declare _month varchar(50) default CONCAT(DATE_SUB(CURDATE(), INTERVAL 30 DAY),’ 00:00:00’);

insert into t_real_flow_total(group_id,part_area,total_today_apcount,total_today_flow,total_today_upstream,total_week_apcount,total_week_flow,total_week_upstream,total_mon_apcount,total_mon_flow,total_mon_upstream,update_time,syc_time) 
select t.group_id,t.part_area,t.total_today_apcount,t.total_today_flow,t.total_today_upstream,
	t.total_week_apcount,t.total_week_flow,t.total_week_upstream,
	t.total_mon_apcount,t.total_mon_flow,t.total_mon_upstream,now() update_time,now() syc_time from (
	-- 流量汇总统计---月
	select t1.group_id,t1.part_area,t3.total_today_apcount,t3.total_today_flow,t3.total_today_upstream,
		t2.total_week_apcount,t2.total_week_flow,t2.total_week_upstream,
		t1.total_mon_apcount,t1.total_mon_flow,t1.total_mon_upstream from (
	select g.group_id,p.`name` part_area,count(group_id) total_mon_apcount,sum(down_flow) total_mon_flow,sum(up_flow) total_mon_upstream from t_real_flow_hour h 
	left join t_relative_ap_group g on h.mac=g.ap_mac 
	left join t_project_group p on g.group_id=p.id 
	where g.group_id is not null and h.create_time>=_month 
	group by g.group_id ) t1 
	left join 
	-- 流量汇总统计---周
	(select g.group_id,p.`name` part_area,count(group_id) total_week_apcount,sum(down_flow) total_week_flow,sum(up_flow) total_week_upstream from t_real_flow_hour h 
	left join t_relative_ap_group g on h.mac=g.ap_mac 
	left join t_project_group p on g.group_id=p.id 
	where g.group_id is not null and h.create_time>=_week 
	group by g.group_id) t2 on t1.group_id=t2.group_id 
	left join 
	-- 流量汇总统计---日
	(select g.group_id,p.`name` part_area,count(group_id) total_today_apcount,sum(down_flow) total_today_flow,sum(up_flow) total_today_upstream from t_real_flow_hour h 
	left join t_relative_ap_group g on h.mac=g.ap_mac 
	left join t_project_group p on g.group_id=p.id 
	where g.group_id is not null and date(h.create_time)=date(now())
	group by g.group_id) t3 on t1.group_id=t3.group_id
) t ON DUPLICATE KEY UPDATE group_id=t.group_id,part_area=t.part_area,
total_today_apcount=t.total_today_apcount,total_today_flow=t.total_today_flow,
total_today_upstream=t.total_today_upstream,total_week_apcount=t.total_week_apcount,
total_week_flow=t.total_week_flow,total_week_upstream=t.total_week_upstream,
total_mon_apcount=t.total_mon_apcount,total_mon_flow=t.total_mon_flow,
total_mon_upstream=t.total_mon_upstream,update_time=now();

3，proc_real_flow_update_sessiontime
//ap历史表的创建时间更新
update t_real_flow_history rfh inner join (
select * from t_real_flow_history where DATE_FORMAT(create_time,’%Y-%m-%d’)=DATE_FORMAT(curdate(),’%Y-%m-%d’)
) rf on rfh.mac = rf.mac
set rfh.sessiontime = rfh.online_user*3 + ifnull(rf.sessiontime,0)
where DATE_FORMAT(rfh.create_time,’%Y-%m-%d’)=DATE_FORMAT(curdate(),’%Y-%m-%d’);
END