mysql - 按其他日期分割日期

Question

我有一张 table rate具有以下结构(近似):

CREATE TABLE `rate` (
    `id`       int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
    `from`     date NOT NULL,
    `to`       date NOT NULL
)

还有一个(大约)相同的表 stop_sale :

CREATE TABLE `stop_sale` (
    `id`       int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
    `from`     date NOT NULL,
    `to`       date NOT NULL
)

考虑到，对于每个人来说，他们的时间间隔是他​​们各自的 from 之间的天数范围。和to字段:

我想以时间间隔不重叠的方式一起查询这些表，而是调整为 stop_sale优先。

示例

rates

| id | from         | to           |
| 1  | "2018-01-05" | "2018-01-31" |
| 2  | "2018-02-01" | "2018-02-15" |

stop_sale

| id | from         | to           |
| 1  | "2018-01-11" | "2018-01-20" |
| 2  | "2018-02-01" | "2018-02-10" |

期望的结果

| rate_id | from         | to           |
| 1       | "2018-01-05" | "2018-01-10" |
| 0       | "2018-01-11" | "2018-01-20" |
| 1       | "2018-01-21" | "2018-01-31" |
| 0       | "2018-02-01" | "2018-02-10" |
| 2       | "2018-02-11" | "2018-02-15" |

注意如何 rate与 id=1根据 stop_rate 的时间间隔分为两条记录与 id=1 (注意: id s 并不重要，重要的是时间间隔)。

换句话说，stop_sale时间间隔对 rate 的时间间隔执行减法运算，并且还绘制有最终结果集。

• 这可以通过 SQL 实现吗？还有MySQL？
• 如果是这样，查询的最优程度如何？在 PHP 中处理这个操作是否更好？

Answer 1

据我所知，仅使用 SQL 查询是无法做到这一点的。这可以在存储函数中迭代解决，但是没有干净的选项来返回数据。最好是返回数据的分隔字符串。

另一种方法是构建一个存储过程，定期填充结果数据表，并对其进行 php 查询。基本逻辑是:

传入起始数据值-起始日期。

使用查询创建临时表:

    select * from rates
    where from >= starting_date
    union
    select * from stop_sale
    where from >= starting_date
    order by from asc

• 遍历临时表。
  • 获取第一个“from”值。
  • 获取下一个更大的“目标”值
  • 查找“from”值<当前“to”值和>当前“from”值，但!=“from”值已在正在填充的结果表中
    • 如果找到，请将当前“起始”值和“结束”值 -1 天插入正在填充的结果表中
    • 否则将当前的“从”值和“到”值插入正在填充的结果表中

这个基本逻辑可以在 php 中完成，尽管它可能更复杂，因为您需要构建从上述临时表查询返回的行数组，在其上运行逻辑，并构建结果数组。这比运行存储过程的效率要低，因为一旦运行存储过程，您只需要对比该运行更新的数据再次运行它。因此是starting_data参数。