mysql - 如何在 MySQL 中使用 IF 语句设置 WHILE 循环？

Question

我想为 MySQL 创建一个存储例程，计算一个月的工作日或工作日数(工作日为周一至周五)。

这是一个语法错误，但我不知道语法错误是什么。它只告诉我:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to usenear 'WHILE(@daycount < @totaldays) DO IF (WEEKDAY(@checkweekday) < 6)THEN ' at line 2

我的语法错误如下:

WHILE(@daycount < @totaldays) DO
      IF (WEEKDAY(@checkweekday) < 6) THEN

我的代码:

SELECT MONTH(CURDATE()) INTO @curmonth;
SELECT MONTHNAME(CURDATE()) INTO @curmonthname;
SELECT DAY(LAST_DAY(CURDATE())) INTO @totaldays;
SELECT FIRST_DAY(CURDATE()) INTO @checkweekday;
SELECT DAY(@checkweekday) INTO @checkday;
SET @daycount = 0;
SET @workdays = 0;

BEGIN
  WHILE(@daycount < @totaldays) DO
      IF (WEEKDAY(@checkweekday) < 6) THEN
        SET @workdays = @workdays+1;
      END IF;
      SET @daycount = @daycount+1;
      SELECT ADDDATE('@checkweekday', INTERVAL 1 DAY) INTO @checkweekday;
  END WHILE;
END;
SELECT @workdays;
    

有人可以提供帮助吗？

更新我收到以下代码的相同错误，因此它可能与此有关:

SET @workdays = 0;
IF (WEEKDAY('2013-06-13') < 6) THEN
  SET @workdays = @workdays+1;
END IF;
SELECT @workdays;

Answer 1

我发现你不能在 mysql 的存储过程之外有条件。这就是语法错误的原因。只要我把我需要的代码放在

BEGIN
SELECT MONTH(CURDATE()) INTO @curmonth;
SELECT MONTHNAME(CURDATE()) INTO @curmonthname;
SELECT DAY(LAST_DAY(CURDATE())) INTO @totaldays;
SELECT FIRST_DAY(CURDATE()) INTO @checkweekday;
SELECT DAY(@checkweekday) INTO @checkday;
SET @daycount = 0;
SET @workdays = 0;

  WHILE(@daycount < @totaldays) DO
    IF (WEEKDAY(@checkweekday) < 5) THEN
      SET @workdays = @workdays+1;
    END IF;
    SET @daycount = @daycount+1;
    SELECT ADDDATE(@checkweekday, INTERVAL 1 DAY) INTO @checkweekday;
  END WHILE;
END

仅供他人使用:

如果您不确定如何在 phpmyadmin 中创建例程，您可以将其放入 SQL 查询中

delimiter ;;
drop procedure if exists test2;;
create procedure test2()
begin
select ‘Hello World’;
end
;;

运行查询。这将创建一个名为 test2 的存储过程或存储例程。现在转到例程选项卡并将存储过程编辑为您想要的。我还建议阅读 http://net.tutsplus.com/tutorials/an-introduction-to-stored-procedures/如果您刚开始使用存储过程。

您需要的 first_day 函数是: How to get first day of every corresponding month in mysql?

显示程序正在运行只需在 END WHILE 下方和 END 上方添加以下行

SELECT @curmonth,@curmonthname,@totaldays,@daycount,@workdays,@checkweekday,@checkday;

然后在 SQL 查询窗口中使用以下代码。

call test2 /* or whatever you changed the name of the stored procedure to */

注意:如果您使用此代码，请记住此代码不考虑国家法定节假日(或与此相关的任何节假日)。