mysql存储过程语法 （时间区间工作日，去除节假日）

定义变量：

BEGIN  
DECLARE workDays INTEGER;
DECLARE adjustmentDay INTEGER;  
DECLARE legalRest INTEGER;
SELECT COUNT(1) INTO adjustmentDay FROM pub_apppar WHERE APR_VALUE >= s AND APR_VALUE <= e AND APR_NAME=\'周末是工作日的列表\';
SELECT COUNT(1) INTO legalRest FROM pub_apppar WHERE APR_VALUE >= s AND APR_VALUE <= e AND APR_NAME=\'周一至周五为假期参数列表\';
select  (floor(days/7)*5 
    + days%7
    - case when 6 between wd and wd+days%7-1 then 1 else 0 end
    - case when 7 between wd and wd+days%7-1 then 1 else 0 end
        + adjustmentDay
        - legalRest) as result
        INTO workDays
from 
(select datediff(e,s)+1 as days, weekday(s)+1 as wd ) as a;
return workDays;
END

子查询

BEGIN  
DECLARE workDays INTEGER;
select  (floor(days/7)*5 
    + days%7
    - case when 6 between wd and wd+days%7-1 then 1 else 0 end
    - case when 7 between wd and wd+days%7-1 then 1 else 0 end
        + (SELECT COUNT(1) FROM pub_apppar WHERE APR_VALUE >= DATE_FORMAT(s,\'%Y-%m-%d\') AND APR_VALUE <= DATE_FORMAT(e,\'%Y-%m-%d\') AND APR_NAME=\'周末是工作日的列表\')
        - (SELECT COUNT(1) FROM pub_apppar WHERE APR_VALUE >= DATE_FORMAT(s,\'%Y-%m-%d\') AND APR_VALUE <= DATE_FORMAT(e,\'%Y-%m-%d\') AND APR_NAME=\'周一至周五为假期参数列表\')) as result
        INTO workDays
from 
(select datediff(e,s)+1 as days, weekday(s)+1 as wd ) as a;
return workDays;
END