SQL语句备份

BEGIN
DECLARE date_start varchar(10);
DECLARE date_end VARCHAR(10);
DECLARE date_today VARCHAR(20);
DECLARE date_day VARCHAR(20);
DECLARE table_name VARCHAR(30);
SET date_start=DATE_FORMAT(DATE_ADD(now(),INTERVAL -1 DAY),'%Y%m%d');
SET date_day=DATE_FORMAT(DATE_ADD(now(),INTERVAL -1 DAY),'%d');
SET date_end=DATE_FORMAT(now(),'%Y%m%d');
SET date_today=DATE_FORMAT(now(),'%Y-%m-%d 00:00:00');
SET table_name= concat('cdmacounts_',date_day);

INSERT INTO db_logs(logdesc) VALUES(concat('EVENT STARTING.......',table_name));

insert into cdmacounts_tmp(num,jz,ch,imsi,ct,date,source)
select DISTINCT(num),jz,ch,imsi,ct,date,source from table_name
where num<>'' and date>date_start and date<date_end;

insert into online_of_hour(phonecount,tjdate) select count(DISTINCT num) phonecount,date tjdate from table_name
WHERE date>date_start and date<date_end group by date;

insert into phone_day_count(phonecount,tjdate) select count(*) as phonecount,date_start from cdmacounts_tmp
where date>date_start and date<date_end;

update cdmacounts_tmp a,h_code b set a.city=b.city,a.province=b.province where substring(a.num,1,9)=b.hcode and a.city is null;
update cdmacounts_tmp a,h_code b set a.city=b.city,a.province=b.province where substring(a.num,1,7)=b.hcode and a.city is null;

insert into phone_alluser_area_tj(province,city,phonecount,tjtype,tjdate) select DISTINCT province,city,count(*) as phonecount,2,date_start as tjtype from cdmacounts_tmp
where date>date_start and date<date_end group by city;

#call process_imei();

update cdma_user_info set visitdays=visitdays+1 where phonenumber in (select num from cdmacounts_tmp where date>date_start and date<date_end);

insert into cdma_user_info(phonenumber,imei,imsi,province,city)
select DISTINCT(num),ch,imsi,province,city from cdmacounts_tmp where
date>date_start and date<date_end and num not in (select phonenumber from cdma_user_info) and
num<>'';

/*
update cdma_user_info b,h_code h set b.province=h.province,b.city=h.city where
substring(b.phonenumber,1,7)=h.hcode and b.city is null;
*/

insert into phone_alluser_area_tj(province,city,phonecount,tjtype,tjdate) select a.province,a.city,count(*) phonecount,3,date_start from cdma_user_info a
where a.createdtime>date_today group by a.city;

insert into phone_alluser_area_tj(province,city,phonecount,tjtype,tjdate) select a.province,a.city,count(*) phonecount,1,date_start from cdma_user_info a group by
a.city;

/*delete from cdmacounts_tmp;

delete from cdmacounts;
*/
INSERT INTO db_logs(logdesc) VALUES('EVENT END!');
END

转载于:https://www.cnblogs.com/mergn/p/4932891.html