统计在某月某时间段工作的天数，除去周末

以5月份为例，利用自定义的函数来统计

DROP FUNCTION IF EXISTS f_c; 
CREATE FUNCTION f_c (start_date date, end_date date ) 
	RETURNS int
BEGIN
   DECLARE start_date2 date;
   DECLARE end_date2 date;
  DECLARE i int;
DECLARE j int;
set i=1;
set j=0;

  if start_date>=str_to_date('2017-5-1','%Y-%m-%d') and     end_date<=str_to_date('2017-5-31','%Y-%m-%d')
then set start_date2=start_date ,end_date2=end_date; 
end if;
if start_date<str_to_date('2017-5-1','%Y-%m-%d') and     end_date<=str_to_date('2017-5-31','%Y-%m-%d')
and     end_date>=str_to_date('2017-5-1','%Y-%m-%d')
then set start_date2=str_to_date('2017-5-1','%Y-%m-%d') ,end_date2=end_date; 
end if;
  if start_date>=str_to_date('2017-5-1','%Y-%m-%d') and  start_date<str_to_date('2017-5-31','%Y-%m-%d') and 
   end_date>str_to_date('2017-5-31','%Y-%m-%d')
then set start_date2=start_date ,end_date2=str_to_date('2017-5-31','%Y-%m-%d'); 
end if;  
if start_date<str_to_date('2017-5-1','%Y-%m-%d') and     end_date>str_to_date('2017-5-31','%Y-%m-%d')
then set start_date2=str_to_date('2017-5-1','%Y-%m-%d') ,end_date2=str_to_date('2017-5-31','%Y-%m-%d'); 

end if;

 while start_date2< end_date2
     do 
 if DAYOFWEEK(start_date2) = 1 or DAYOFWEEK(start_date2) = 7  THEN
set j=j+1;
end if;
set start_date2=start_date2+1;
end while;



RETURN j;
end;

-- select f_c(str_to_date('2017-5-1','%Y-%m-%d'),str_to_date('2017-5-10','%Y-%m-%d') )

上面这个有点问题又修改了下：

DROP FUNCTION IF EXISTS f_c; 
CREATE FUNCTION f_c (start_date date, end_date date ) 
	RETURNS int
BEGIN
   DECLARE start_date2 date;
   DECLARE end_date2 date;
  DECLARE i int;
DECLARE j int;
set i=1;
set j=0;
  if  DATE_FORMAT(start_date,'%Y-%m-%d') >=str_to_date('2017-5-1','%Y-%m-%d') and DATE_FORMAT(end_date,'%Y-%m-%d')   
 <=str_to_date('2017-5-31','%Y-%m-%d')
then set start_date2=DATE_FORMAT(start_date,'%Y-%m-%d') ,end_date2=DATE_FORMAT(end_date,'%Y-%m-%d') ; 
end if;   
if start_date<str_to_date('2017-5-1','%Y-%m-%d') and     end_date<=str_to_date('2017-5-31','%Y-%m-%d')
and     end_date>=str_to_date('2017-5-1','%Y-%m-%d')
then set start_date2=str_to_date('2017-5-1','%Y-%m-%d') ,end_date2=end_date; 
end if;
  if start_date>=str_to_date('2017-5-1','%Y-%m-%d') and  start_date<=str_to_date('2017-5-31','%Y-%m-%d') and 
   end_date>str_to_date('2017-5-31','%Y-%m-%d')
then set start_date2=start_date ,end_date2=str_to_date('2017-5-31','%Y-%m-%d'); 
end if;  
if start_date<str_to_date('2017-5-1','%Y-%m-%d') and     end_date>str_to_date('2017-5-31','%Y-%m-%d')
then set start_date2=str_to_date('2017-5-1','%Y-%m-%d') ,end_date2=str_to_date('2017-5-31','%Y-%m-%d'); 
end if; 
 while start_date2<= end_date2
     do 
 if DAYOFWEEK(start_date2) = 2 or DAYOFWEEK(start_date2) = 3  or DAYOFWEEK(start_date2) = 4 
 or DAYOFWEEK(start_date2) = 5
 or DAYOFWEEK(start_date2) = 6
  THEN
set j=j+1;
end if;
set start_date2=date_add(start_date2, interval 1 day);
end while;

RETURN j;
end;

下面是项目需求中具体运用到上述sql自定义函数的实例