mysql - 根据 MySQL 中的条件更新不同的字段

Question

我将某些用户可以执行的要求存储在我的数据库中。需求可以有不同的状态(存储为事件)，例如进行中、完成、等待等(有 30 种不同的状态)。不同的治疗步骤对应不同的需求期限。

如果它们的当前状态属于预定义列表，我需要“卡住”一些需求的截止日期。

例如:

• 如果需求的状态为 “A”，我必须“卡住”截止日期 2 到 5。
• 如果状态是 “B” 或 “C”，我必须“卡住”截止日期 3 到 5。
• 如果状态是 “D”，我必须“卡住”截止日期 4 和 5。

我计划使用每天 19:00 运行的 EVENT 来更新(增加 1 天)相关需求的不同截止日期。

表结构:

表需求

id | someDatas | deadline1 | deadline2 | deadline3 | deadline4 | deadline5
---+-----------+-----------+-----------+-----------+-----------+-----------
   |           |           |           |           |           |

表格状态

id | name
---+-----
   |

表事件

id | id_demand | someOthersDatas | id_status
---+-----------+-----------------+----------
   |           |                 |

我写了一个查询来获取状态列表对应的需求:

SELECT dem.*, st.`name` as 'statusName'
FROM `status` st
INNER JOIN `events` eve
    ON eve.id_status = st.id
INNER JOIN `demand` dem
    ON eve.id_demand = dem.id
WHERE st.`name` IN ('A', 'B', 'C', 'D')
    AND eve.id IN
    (
        SELECT MAX(even.id) ev
        FROM `demand` de
        INNER JOIN `events` even
            ON even.id_demand = de.id
        GROUP BY de.id
    );

这个查询非常有效，我可以获得治疗所需的信息，我有需求的 ID、截止日期和当前状态的名称。

我不介意将此结果存储在临时表中，例如:

DROP TEMPORARY TABLE IF EXISTS pendingDemands;
CREATE TEMPORARY TABLE IF NOT EXISTS pendingDemands
SELECT /* the query shown above */

为了确保我要添加到截止日期的那一天是有效的(= 不是休息日)我写了一个计算下一个有效日期的函数:

DELIMITER //
DROP FUNCTION IF EXISTS `get_next_valid_date`;
CREATE FUNCTION `get_next_valid_date`(MyDate DATETIME) RETURNS DATETIME
BEGIN
    REPEAT
        SET MyDate = (DATE_ADD(MyDate, INTERVAL 1 DAY));
        SET @someCondition = (select isDayOff(MyDate));
    UNTIL (@someCondition = 0) END REPEAT;
    RETURN MyDate;
END//

这个函数完美运行，我得到了预期的结果，isDayOff() 不需要详细说明。

我的问题是我不知道如何一起使用它们(临时表pendingDemands 和函数get_next_valid_date)来更新表demand，我的 SQL 不够熟练，无法构建如此漂亮的 UPDATE 查询。

我可以采取什么方向？

Answer 1

我终于找到了基于此 answer 的解决方法

我创建了一个存储过程，其中我使用一个游标来存储我用来提供 pendingDemands 临时表的查询。

然后，我遍历该游标并使用 CASE WHEN 语句来确定要修改的值:

DELIMITER $$
DROP PROCEDURE IF EXISTS `freezePendingDeadlines` $$
CREATE PROCEDURE `freezePendingDeadlines`()
BEGIN
    -- from http://stackoverflow.com/questions/35858541/call-a-stored-procedure-from-the-declare-statement-when-using-cursors-in-mysql

    -- declare the program variables where we'll hold the values we're sending into the procedure;
    -- declare as many of them as there are input arguments to the second procedure,
    -- with appropriate data types.

    DECLARE p_id INT DEFAULT 0;
    DECLARE pT2P DATETIME DEFAULT NULL;
    DECLARE pT3P DATETIME DEFAULT NULL;
    DECLARE pT4P DATETIME DEFAULT NULL;
    DECLARE pT5P DATETIME DEFAULT NULL;
    DECLARE pstatusName VARCHAR(255) DEFAULT NULL;

    -- we need a boolean variable to tell us when the cursor is out of data

    DECLARE done TINYINT DEFAULT FALSE;

    -- declare a cursor to select the desired columns from the desired source table1
    -- the input argument (which you might or might not need) is used in this example for row selection

    DECLARE demandCursor
     CURSOR FOR
     SELECT p.id,
            p.T2P,
            p.T3P,
            p.T4P,
            p.T5P,
            P.statusName
       FROM
       (
           SELECT dem.*, st.`name` as 'statusName'
           FROM `status` st
           INNER JOIN `events` eve
               ON eve.id_status = st.id
           INNER JOIN `demand` dem
               ON eve.id_demand = dem.id
           WHERE st.`name` IN ('A', 'B', 'C', 'D')
               AND eve.id IN
               (
                   SELECT MAX(even.id) ev
                   FROM `demand` de
                   INNER JOIN `events` even
                       ON even.id_demand = de.id
                   GROUP BY de.id
               )
       ) AS p;

    -- a cursor that runs out of data throws an exception; we need to catch this.
    -- when the NOT FOUND condition fires, "done" -- which defaults to FALSE -- will be set to true,
    -- and since this is a CONTINUE handler, execution continues with the next statement.   

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    DROP TEMPORARY TABLE IF EXISTS days_off;
    CREATE TEMPORARY TABLE IF NOT EXISTS days_off
    (
        date_off VARCHAR(5)
    );

    INSERT INTO days_off VALUES('01-01'),
                               ('05-01'),
                               ('05-08'),
                               ('07-14'),
                               ('08-15'),
                               ('11-01'),
                               ('11-11'),
                               ('12-25');

    -- open the cursor

    OPEN demandCursor;

    my_loop: -- loops have to have an arbitrary label; it's used to leave the loop
    LOOP

      -- read the values from the next row that is available in the cursor

      FETCH demandCursor INTO p_id, pT2P, pT3P, pT4P, pT5P, pstatusName;

      IF done THEN -- this will be true when we are out of rows to read, so we go to the statement after END LOOP.
        LEAVE my_loop;
      ELSE
        CASE pstatusName
            WHEN 'A' THEN
                SET pT2P=get_next_valid_date(pT2P);
                SET pT3P=get_next_valid_date(pT3P);
                SET pT4P=get_next_valid_date(pT4P);
                SET pT5P=get_next_valid_date(pT5P);

            WHEN 'B' THEN
                SET pT3P=get_next_valid_date(pT3P);
                SET pT4P=get_next_valid_date(pT4P);
                SET pT5P=get_next_valid_date(pT5P);

            WHEN 'C' THEN
                SET pT3P=get_next_valid_date(pT3P);
                SET pT4P=get_next_valid_date(pT4P);
                SET pT5P=get_next_valid_date(pT5P);

            WHEN 'D' THEN
                SET pT4P=get_next_valid_date(pT4P);
                SET pT5P=get_next_valid_date(pT5P);
        END CASE;
        UPDATE `demand`
        SET T2P=pT2P,
            T3P=pT3P,
            T4P=pT4P,
            T5P=pT5P
        WHERE id=p_id;
      END IF;
    END LOOP;
    CLOSE demandCursor;
    DROP TEMPORARY TABLE IF EXISTS days_off;
END$$