带有 IF 语句 MYSQL 的 WHILE 循环
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17139074/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
WHILE LOOP with IF STATEMENT MYSQL
提问by scrfix
I would like to create a stored routine for MySQL that figures out the number of business or working days for a month (Working Days are Monday thru Friday).
我想为 MySQL 创建一个存储例程，用于计算一个月的营业或工作日数（工作日是星期一到星期五）。
It's a syntax error however I don't know what the syntax error is. All it tells me is:
这是一个语法错误，但我不知道语法错误是什么。它告诉我的只是：
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHILE(@daycount < @totaldays) DO IF (WEEKDAY(@checkweekday) < 6) THEN ' at line 2
1064 - 你的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在第 2 行的“WHILE(@daycount < @totaldays) DO IF (WEEKDAY(@checkweekday) < 6) THEN”附近使用的正确语法
My Syntax Error is in the following:
我的语法错误如下：
WHILE(@daycount < @totaldays) DO
IF (WEEKDAY(@checkweekday) < 6) THEN
My Code:
我的代码：
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;
Is someone able to assist?
有人可以提供帮助吗？
UPDATEI receive the same error with the following bit of code so it probably has something to do with this:
更新我收到与以下代码相同的错误，因此它可能与此有关：
SET @workdays = 0;
IF (WEEKDAY('2013-06-13') < 6) THEN
SET @workdays = @workdays+1;
END IF;
SELECT @workdays;
回答by scrfix
I have discovered that you cannot have conditionals outside of the stored procedure in mysql. This is why the syntax error. As soon as I put the code that I needed between
我发现在 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
Just for others:
只为其他人：
If you are not sure how to create a routine in phpmyadmin you can put this in the SQL query
如果您不确定如何在 phpmyadmin 中创建例程，您可以将其放在 SQL 查询中
delimiter ;;
drop procedure if exists test2;;
create procedure test2()
begin
select ‘Hello World';
end
;;
Run the query. This will create a stored procedure or stored routine named test2. Now go to the routines tab and edit the stored procedure to be what you want. I also suggest reading http://net.tutsplus.com/tutorials/an-introduction-to-stored-procedures/if you are beginning with stored procedures.
运行查询。这将创建一个名为 test2 的存储过程或存储例程。现在转到“例程”选项卡并将存储过程编辑为您想要的。如果您开始使用存储过程，我还建议您阅读http://net.tutsplus.com/tutorials/an-introduction-to-stored-procedures/。
The first_day function you need is: How to get first day of every corresponding month in mysql?
您需要的 first_day 函数是： 如何在 mysql 中获取每个对应月份的第一天？
Showing the Procedure is workingSimply add the following line below END WHILE and above END
显示程序正在运行只需在 END WHILE 和 END 上方添加以下行
SELECT @curmonth,@curmonthname,@totaldays,@daycount,@workdays,@checkweekday,@checkday;
Then use the following code in the SQL Query Window.
然后在 SQL 查询窗口中使用以下代码。
call test2 /* or whatever you changed the name of the stored procedure to */
NOTE:If you use this please keep in mind that this code does not take in to account nationally observed holidays (or any holidays for that matter).
注意：如果您使用它，请记住，此代码不考虑国家规定的假期（或任何与此相关的假期）。
