时间戳

首页 > 解决方案 > 根据 MySQL 中的条件更新不同的字段

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

问题描述

我将一些用户可以做的需求存储在我的数据库中。需求可以有不同的状态（存储为事件），例如进行中、完成、等待等（有 30 种不同的状态）。需求具有对应于不同处理步骤的不同期限。

我需要“冻结”一些需求的最后期限，如果它们的当前状态属于预定义的列表。

例如：

我计划使用EVENT每天运行的 19:00 来更新（增加 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查询。

我可以采取任何方向吗？

标签： mysqlsqlsql-updatesubquery

解决方案

我终于根据这个答案找到了解决方法

我创建了一个存储过程，其中我使用一个游标来存储我用来提供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$$

推荐阅读