定义变量:
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