问仅使用日期列更新日期范围表
EN

Stack Overflow用户

提问于 2022-06-30 23:46:59

回答 1查看 36关注 0票数 0

我有一个根据日期范围保存数据的表。例如，在示例表中，范围从07到9，下一个范围从10到14.

如果用户希望修改日期范围，如何在sql中进行操作？

ID    start_date         data_id       price
===    =======           =======      =======
 1    2022-10-07            3            10
 2    2022-10-10            5            50
 3    2022-10-15            5            20
 4    2022-10-16            3            40
       ...                ...          ...

例如，如果用户希望更新价格为30的2022-10-08至2022-10-12之间的范围，表应如下：

ID    start_date         data_id       price
===    =======           =======      =======
 1    2022-10-07            3            10
 5    2022-10-08            3            30
 2    2022-10-13            5            50
 3    2022-10-15            5            20
 4    2022-10-16            3            40
       ...                ...          ...

我只能想出复杂的方法来做到这一点。

编辑以获取更多信息：

mysql版本: 5.7.38

Data_id，在本例中，为了简单起见，我们可以忽略它。

创建表日历( ID bigInt，start_date DATE，data_id int，price int );

UPDATE查询实际上是我无法找到如何执行的查询。用户只需提供一个开始和结束日期，以及一个价格。每个范围将有一个与前一个不同的价格，否则它将包括在其中，如本例所示。

mysql

sql

音视频体验馆免费体验试用

一站式·全方位体验音视频产品，所见即所得，30分钟即可上线同款能力

回答 1

Stack Overflow用户

回答已采纳

发布于 2022-07-01 01:04:34

CREATE PROCEDURE update_calendar (date_from DATE, date_till DATE, new_price INT)
BEGIN
    IF NOT EXISTS ( SELECT NULL
                    FROM calendar
                    WHERE start_date = date_till + INTERVAL 1 DAY ) THEN
        INSERT INTO calendar (start_date, data_id, price)
        SELECT date_till + INTERVAL 1 DAY, data_id, price
        FROM calendar
        WHERE start_date <= date_till
        ORDER BY start_date DESC LIMIT 1;
    END IF;
    DELETE 
    FROM calendar 
    WHERE start_date BETWEEN date_from AND date_till;
    INSERT INTO calendar (start_date, price) 
    SELECT date_from, new_price;
END

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=6de2801efc9ab2ae7627ac86d323cc09

我建议您将整个操作作为可靠的事务执行。也就是说，定义一个falg变量，创建根据处理程序创建的处理程序，添加开始事务，并添加提交/回滚取决于在数据版本期间发生任何问题时在处理程序中设置的标志。

例如，我从10更新到13，价格为100，第10天的行将不必创建，因为第9天已经有了正确的价格。

CREATE PROCEDURE update_calendar (date_from DATE, date_till DATE, new_price INT)
BEGIN
    IF NOT EXISTS ( SELECT NULL
                    FROM calendar
                    WHERE start_date = date_till + INTERVAL 1 DAY ) THEN
        INSERT INTO calendar (start_date, data_id, price)
        SELECT date_till + INTERVAL 1 DAY, data_id, price
        FROM calendar
        WHERE start_date <= date_till
        ORDER BY start_date DESC LIMIT 1;
    END IF;
    DELETE 
    FROM calendar 
    WHERE start_date BETWEEN date_from AND date_till;
    INSERT INTO calendar (start_date, price) 
    SELECT date_from, new_price
    WHERE new_price <> COALESCE( ( SELECT price
                                   FROM calendar
                                   WHERE start_date < date_from 
                                   ORDER BY start_date DESC LIMIT 1 ), 0);
END