mysql过程-inner for循环条件未生成正确的输出

kse8i1jr 于 2021-06-19 发布在 Mysql

关注(0)|答案(1)|浏览(282)

我正在创建一个酒吧，啤酒，饮酒者，喜欢，常客，交易模式。每个酒吧一周中每天都有营业时间。
具体错误：我有一个外循环，通过每个酒吧，和内循环分配1-7个开放/关闭时间为每个酒吧条目。尽管循环并尝试插入1-7个条目，insert语句在外循环中每个条条目只触发一次。
我的营业时间表：

Bar Name varchar(45) PK FK 
Bar Phone # INT PK FK
Weekday INT PK
Open Hour TIME
Close Hour TIME

电流输出由每巴一个打开/关闭小时输入组成：
运行程序后的打开/关闭时间表

Boone's Brew Inc    2147483647  7   04:55:51    18:39:25
Boondocks Brewing Tap Room & Restaurant 2147483647  4   09:01:01    17:10:09
Boon Island Alehouse    2147483647  1   05:14:24    19:56:57
........ for 957 more bars, all with listed only once in the table with a random weekday

期望输出：

Boone's Brew Inc    2147483647  7   04:55:51    18:39:25
Boone's Brew Inc    2147483647  6   04:55:51    18:39:25
Boone's Brew Inc    2147483647  5   04:55:51    18:39:25
Boone's Brew Inc    2147483647  4   04:55:51    18:39:25
Boondocks Brewing Tap Room & Restaurant 2147483647  4   09:01:01    17:10:09
Boondocks Brewing Tap Room & Restaurant 2147483647  3   09:01:01    17:10:09
Boondocks Brewing Tap Room & Restaurant 2147483647  2   09:01:01    17:10:09
Boon Island Alehouse    2147483647  1   05:14:24    19:56:57

所需输出：每根棒1-7个打开/关闭小时条目，而不是1个。
我的程序是：

DROP PROCEDURE fill_hours;
DELIMITER $$

CREATE PROCEDURE fill_hours()
BEGIN

    DECLARE i INT DEFAULT 0;
    DECLARE j INT DEFAULT 0;
    DECLARE num_of_bars INT DEFAULT 0;
    DECLARE bar_name varchar(45) DEFAULT "";
    DECLARE bar_phone_num INT DEFAULT 0;
    DECLARE weekday INT DEFAULT 0;
    DECLARE open_hour TIME DEFAULT 0;
    DECLARE close_hour TIME DEFAULT 0;
    DECLARE start_time_factor TIME DEFAULT 0;
    DECLARE end_time_factor TIME DEFAULT 0;

    SELECT COUNT(*) INTO num_of_bars FROM Bar;
    WHILE i < num_of_bars DO
    SELECT `Bar Name`, `Bar Phone #` INTO bar_name, bar_phone_num FROM Bar LIMIT i,1;
        weekday: WHILE j < 7 DO
            SET weekday := (1 + FLOOR(RAND() * 7));
            IF EXISTS(SELECT `Bar Name`, `Bar Phone #`, `Weekday` FROM `Opening/Closing Hours` WHERE `Bar Name` = bar_name AND `Bar Phone #` = bar_phone_num AND `Weekday` = weekday) THEN
                SELECT CONCAT("weekday: ", weekday);
                SET j := j + 1;
                ITERATE weekday;
            END IF;
            SELECT SEC_TO_TIME(FLOOR(TIME_TO_SEC('13:00:00') + RAND() * (TIME_TO_SEC(TIMEDIFF('13:00:00', '15:00:00'))))) INTO start_time_factor;
            SELECT SEC_TO_TIME(FLOOR(TIME_TO_SEC('20:00:00') + RAND() * (TIME_TO_SEC(TIMEDIFF('18:00:00', '19:00:00'))))) INTO end_time_factor;
            SELECT SEC_TO_TIME(FLOOR(TIME_TO_SEC(start_time_factor) + RAND() * (TIME_TO_SEC(TIMEDIFF(start_time_factor, '16:00:00'))))) INTO open_hour;
            SELECT SEC_TO_TIME(FLOOR(TIME_TO_SEC(end_time_factor) + RAND() * (TIME_TO_SEC(TIMEDIFF(end_time_factor, '23:59:00'))))) INTO close_hour;

            INSERT INTO `Opening/Closing Hours` VALUE(bar_name, bar_phone_num, weekday, open_hour, close_hour);

            SET j := j + 1;
        END WHILE;
    SET i := i + 1;
    SET j := 0;
    END WHILE;

END $$
DELIMITER ;

sql mysql stored-procedures mysql-workbench innodb

来源：https://stackoverflow.com/questions/53357624/mysql-procedure-inner-for-loop-condition-not-producing-the-correct-output

1条答案

按热度按时间

ftf50wuq1#

第一步：去掉不相关的绒毛，比如table。你在抱怨计算不是随机的，对吗？那么，这等于计算吗？

DROP PROCEDURE fill_hours;
DELIMITER $$

CREATE PROCEDURE fill_hours()
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE j INT DEFAULT 0;
    DECLARE num_of_bars INT DEFAULT 0;
    DECLARE bar_name varchar(45) DEFAULT "";
    DECLARE bar_phone_num INT DEFAULT 0;
    DECLARE weekday INT DEFAULT 0;
    DECLARE open_hour TIME DEFAULT 0;
    DECLARE close_hour TIME DEFAULT 0;
    DECLARE start_time_factor TIME DEFAULT 0;
    DECLARE end_time_factor TIME DEFAULT 0;

        weekday: WHILE j < 7 DO
            SET weekday := (1 + FLOOR(RAND() * 7));
            SELECT SEC_TO_TIME(FLOOR(TIME_TO_SEC('13:00:00') + RAND() * (TIME_TO_SEC(TIMEDIFF('13:00:00', '15:00:00'))))) INTO start_time_factor;
            SELECT SEC_TO_TIME(FLOOR(TIME_TO_SEC('20:00:00') + RAND() * (TIME_TO_SEC(TIMEDIFF('18:00:00', '19:00:00'))))) INTO end_time_factor;
            SELECT SEC_TO_TIME(FLOOR(TIME_TO_SEC(start_time_factor) + RAND() * (TIME_TO_SEC(TIMEDIFF(start_time_factor, '16:00:00'))))) INTO open_hour;
            SELECT SEC_TO_TIME(FLOOR(TIME_TO_SEC(end_time_factor) + RAND() * (TIME_TO_SEC(TIMEDIFF(end_time_factor, '23:59:00'))))) INTO close_hour;
            -- For debugging:
            SELECT weekday, open_hour, close_hour;
            SET j := j + 1;
        END WHILE;
END $$
DELIMITER ;

但这似乎行得通。。。

mysql> call fill_hours();
+---------+-----------+------------+
| weekday | open_hour | close_hour |
+---------+-----------+------------+
|       3 | 11:19:41  | 17:07:07   |
+---------+-----------+------------+
1 row in set (0.01 sec)

+---------+-----------+------------+
| weekday | open_hour | close_hour |
+---------+-----------+------------+
|       6 | 07:43:48  | 17:06:57   |
+---------+-----------+------------+
1 row in set (0.01 sec)

+---------+-----------+------------+
| weekday | open_hour | close_hour |
+---------+-----------+------------+
|       5 | 09:55:03  | 15:56:42   |
+---------+-----------+------------+
1 row in set (0.01 sec)

+---------+-----------+------------+
| weekday | open_hour | close_hour |
+---------+-----------+------------+
|       2 | 10:11:17  | 19:42:50   |
+---------+-----------+------------+
1 row in set (0.01 sec)

+---------+-----------+------------+
| weekday | open_hour | close_hour |
+---------+-----------+------------+
|       6 | 09:58:52  | 18:58:02   |
+---------+-----------+------------+
1 row in set (0.01 sec)

+---------+-----------+------------+
| weekday | open_hour | close_hour |
+---------+-----------+------------+
|       4 | 11:26:18  | 18:02:06   |
+---------+-----------+------------+
1 row in set (0.01 sec)

+---------+-----------+------------+
| weekday | open_hour | close_hour |
+---------+-----------+------------+
|       3 | 11:35:00  | 17:18:14   |
+---------+-----------+------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

现在，我将以“为我工作”来回应。
如果你认为 INSERT 可能是问题所在，然后进行类似的切割，以隔离（尽可能实际的）问题 INSERT . 也许到时候你（或我们）就能看出哪里不对了。

赞(0）回复(0）举报 2021-06-20

我来回答

mysql过程-inner for循环条件未生成正确的输出

1条答案

相关问题

热门标签

最新问答