目的:SQL里有采集时间和日期,实现取出该日期距今经过了多少个工作日
实现:
TIMESTAMPDIFF( DAY, a.`采集日期`, CURRENT_DATE () ) AS '距今', # 自然日
(TIMESTAMPDIFF(DAY, a.`采集日期`, CURRENT_DATE ()) + 1)
-(TIMESTAMPDIFF(WEEK, a.`采集日期`, CURRENT_DATE ()) * 2)
-(CASE WHEN DAYNAME(a.`采集日期`) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DAYNAME(CURRENT_DATE ()) = 'Saturday' THEN 1 ELSE 0 END) as '距今工作日'
结论:该SQL只能剔除掉周六和周日,暂时无法搞定中国节假日的调休