MySQL 求前两天、前两周、前两个月（跨年）最后一天数据

2017-02-28

　　一、要求：

　　

　　

　　要求实现下面效果：

　　二、实现步骤：

　　1.创建中间过程表，包含字段如下：

　　

　　2.查询资源表resource_storage_info数据依据时间放入中间表storage_usaged对应列中，代码如下：

　　更新今天数据：

　　update　storage_usaged s inner join　(select r.STORAGE_USAGE SZE,r.STORAGE_SIZE SIZ,c.USER_ID UID　from resource_storage_info r,cfg_user_filepath c
　　where r.CUST_INFO = c.PATH and day(STR_TO_DATE(r.DATE,'%Y-%m-%d')) = day(now())　group by r.CUST_INFO) d　on s.USER_ID = d.UID
　　set s.STORAGE_USED = d.SZE　where s.USER_ID = d.UID,s.STORAGE_ALL = d.SIZ;

　　更新昨天数据：

　　update　storage_usaged s inner join　(select r.STORAGE_USAGE SZE,c.USER_ID UID　from resource_storage_info r,cfg_user_filepath c
　　where r.CUST_INFO = c.PATH and day(STR_TO_DATE(r.DATE,'%Y-%m-%d')) = day(now())-1　group by r.CUST_INFO) d　on s.USER_ID = d.UID
　　set s.STORAGE_USED = d.SZE　where s.USER_ID = d.UID;

　　更新前天数据：

　　update　storage_usaged s inner join　(select r.STORAGE_USAGE SZE,c.USER_ID UID　from resource_storage_info r,cfg_user_filepath c
　　where r.CUST_INFO = c.PATH and day(STR_TO_DATE(r.DATE,'%Y-%m-%d')) = day(now())-2　group by r.CUST_INFO) d　on s.USER_ID = d.UID
　　set s.STORAGE_USED = d.SZE　where s.USER_ID = d.UID;

　　更新上周数据：

　　update　storage_usaged s inner join　(select r.STORAGE_USAGE SZE,c.USER_ID UID　from resource_storage_info r,cfg_user_filepath c
　　where r.CUST_INFO = c.PATH and STR_TO_DATE(r.DATE,'%Y-%m-%d') = (select date_sub(CURDATE(),INTERVAL WEEKDAY(CURDATE()) + 1 DAY))
　　　　group by r.CUST_INFO) d　on s.USER_ID = d.UID
　　set s.STORAGE_LASTWEEK = d.SZE
　　where s.USER_ID = d.UID;

　　更新前2周数据：

　　update　storage_usaged s inner join　(select r.STORAGE_USAGE SZE,c.USER_ID UID　from resource_storage_info r,cfg_user_filepath c
　　where r.CUST_INFO = c.PATH and STR_TO_DATE(r.DATE,'%Y-%m-%d') = (select date_sub(CURDATE(),INTERVAL WEEKDAY(CURDATE()) + 8 DAY))
　　　　group by r.CUST_INFO) d　on s.USER_ID = d.UID
　　set s.STORAGE_LASTWEEK = d.SZE
　　where s.USER_ID = d.UID;

-------------------------------------------------------------------------------------------------------------------------------------------------------------

　　如果前两个月都是在本年，用下面的SQL语句：

+-----------------------------------------------------------------------------------------------------------------------------------------------------------+

　　更新上月数据：

　　update　storage_usaged s inner join　(select r.STORAGE_USAGE SZE,c.USER_ID UID　from resource_storage_info r,cfg_user_filepath c
　　where r.CUST_INFO = c.PATH and STR_TO_DATE(r.DATE,'%Y-%m-%d') = (select date_sub(date_sub(date_format(now(),'%y-%m-%d'),interval extract( day from now()) day),interval 0 month))　group by r.CUST_INFO) d　on s.USER_ID = d.UID
　　set s.STORAGE_LASTMONTH = d.SZE
　　where s.USER_ID = d.UID;

　　更新上2个月数据：

　　update　storage_usaged s inner join　(select r.STORAGE_USAGE SZE,c.USER_ID UID　from resource_storage_info r,cfg_user_filepath c
　　　　where r.CUST_INFO = c.PATH and STR_TO_DATE(r.DATE,'%Y-%m-%d') = (select CONCAT(date_format(LAST_DAY(now() - interval 2 month),'%Y-%m-'),'01'))　group by r.CUST_INFO) d　on s.USER_ID = d.UID
　　set s.STORAGE_BEFORE_LASTMONTH = d.SZE
　　where s.USER_ID = d.UID

+-----------------------------------------------------------------------------------------------------------------------------------------------------------+

　　若现在是1月，上个月是去年12月，上2个月是去年11月，使用下面SQL语句：

+-----------------------------------------------------------------------------------------------------------------------------------------------------------+

　　更新去年11月数据：

　　update　storage_usaged s inner join　(select r.STORAGE_USAGE SZE,c.USER_ID UID　from resource_storage_info r,cfg_user_filepath c
　　　　where r.CUST_INFO = c.PATH and STR_TO_DATE(r.DATE,'%Y-%m-%d') = STR_TO_DATE(CONCAT(DATE_FORMAT(NOW(), '%Y')-1,'-11-30'),'%Y-%m-%d')　group by r.CUST_INFO) d　on s.USER_ID = d.UID
　　set s.STORAGE_BEFORE_LASTMONTH = d.SZE
　　where s.USER_ID = d.UID;

　　更新去年12月数据：　

　　update　storage_usaged s inner join　(select r.STORAGE_USAGE SZE,c.USER_ID UID　from resource_storage_info r,cfg_user_filepath c
　　　　where r.CUST_INFO = c.PATH and STR_TO_DATE(r.DATE,'%Y-%m-%d') = STR_TO_DATE(CONCAT(DATE_FORMAT(NOW(), '%Y')-1,'-12-31'),'%Y-%m-%d')　group by r.CUST_INFO) d　on s.USER_ID = d.UID
　　set s.STORAGE_LASTMONTH = d.SZE
　　where s.USER_ID = d.UID

+-----------------------------------------------------------------------------------------------------------------------------------------------------------+

　　若现在是2月，上2个月是去年12月，使用下面SQL语句：

+-----------------------------------------------------------------------------------------------------------------------------------------------------------+

　　update　storage_usaged s inner join　(select r.STORAGE_USAGE SZE,r.STORAGE_SIZE SIZ,c.USER_ID UID　from resource_storage_info r,cfg_user_filepath c
　　　　where r.CUST_INFO = c.PATH and STR_TO_DATE(r.DATE,'%Y-%m-%d') = STR_TO_DATE(CONCAT(DATE_FORMAT(NOW(), '%Y')-1,'-12-31'),'%Y-%m-%d')　group by r.CUST_INFO) d　on s.USER_ID = d.UID
　　set s.STORAGE_BEFORE_LASTMONTH = d.SZE
　　where s.USER_ID = d.UID

+-----------------------------------------------------------------------------------------------------------------------------------------------------------+

　　3.更新展示表storage_rate数据：

　　update storage_rate r inner join (select user_id id,
　　round((storage_used/1024)/1024,2) used,　　--此字段是KB，保留两位小数
　　CONCAT(round((storage_used*100/storage_all),1),'%') used_rate,　　
　　CONCAT(ABS(round(((storage_yesterday-storage_before_yesterday)*100/storage_before_yesterday),1)),'%') day_rate,
　　CONCAT(ABS(round(((storage_lastweek-storage_before_lastweek)*100/storage_before_lastweek),1)),'%') week_rate,
　　CONCAT(ABS(round(((storage_lastmonth-storage_before_lastmonth)*100/storage_before_lastmonth),1)),'%') month_rate
　　from storage_usaged group by user_id) s on r.user_id = s.id
　　set r.storage_used = s.used,r.storage_used_rate = used_rate,r.prev_day_rate = day_rate,
　　r.prev_week_rate = week_rate,r.prev_month_rate = month_rate,r.date = DATE_FORMAT(NOW(),'%Y-%m-%e %H:%i:%s')
　　where r.user_id = s.id

　　

　　本人知识匮乏，欢迎留下更简单的SQL语句和更好的方法......