向东博客 专注WEB应用 构架之美 --- 构架之美，在于尽态极妍 | 应用之美，在于药到病除|赢在IT，Playin' with IT,Focus on Killer Application,Marketing Meets Technology.

mysql低版本日期相加：
select opendate,openlong,INTERVAL openlong DAY + opendate from enter_status;

线上跑的sql：
select a.enterpriseid,b.name,a.opendate,a.openlong,INTERVAL a.openlong DAY + a.opendate as now from enter_status a,domain b where TO_DAYS(NOW()) - TO_DAYS(a.opendate) > a.openlong+7 and a.enterpriseid=b.enterpriseid
-----------------------------------------------------------------------------------------------------------------


select b.enterpriseid from enter_status a,enterprise b where TO_DAYS(NOW()) - TO_DAYS(a.opendate) > a.openlong+1 and a.enterpriseid=b.enterpriseid and b.expiration=0 and b.flag=2 limit 10;

状态为 b.expiration=1 b.flag=1 为过期，发提醒信件，但是还能正常收发信，但是正常企业0 2 也有过期的，是由于程序没有放置标志位！



mysql -uroot -p enterprise < in.txt > out.txt
准确的sql：
SELECT  b.enterpriseid,c.name,opendate,openlong,ADDDATE(a.opendate,a.openlong) from enter_status a,enterprise b,domain c where TO_DAYS(NOW()) - TO_DAYS(a.opendate) > a.openlong+1 and a.enterpriseid=b.enterpriseid and b.enterpriseid=c.enterpriseid and a.enterpriseid=c.enterpriseid limit 15

更准确：
select  b.enterpriseid,c.name,a.opendate,a.openlong,ADDDATE(a.opendate,a.openlong) from enter_status a,enterprise b,domain c where TO_DAYS(NOW()) - TO_DAYS(a.opendate) > a.openlong+1 and a.enterpriseid=b.enterpriseid and b.enterpriseid=c.enterpriseid and a.enterpriseid=c.enterpriseid and b.expiration=1 and b.flag=1 group by c.enterpriseid limit 15;

select a.enterpriseid,b.name,a.opendate,a.openlong,ADDDATE(a.opendate,a.openlong) from enter_status a,domain b where TO_DAYS(NOW()) - TO_DAYS(a.opendate) > a.openlong+7 and a.enterpriseid=b.enterpriseid;

导入时候第4行可能有错：改为：
`check` tinyint(4) NOT NULL default '1'

作者：jackxiang@向东博客 专注WEB应用 构架之美 --- 构架之美，在于尽态极妍 | 应用之美，在于药到病除
地址：https://jackxiang.com/post/964/
版权所有。转载时必须以链接形式注明作者和原始出处及本声明！