mysql - 具有条件结果的复杂 mysql 查询

Question

我在 MySQL 数据库中有下一个结构:

boats
id    name
-------------
1     name1
2     name2

boat_prices
id boat_id       date       duration      price     is_default  
---------------------------------------------------------------
1     1       '2018-01-01'       1         100          
2     1       '2018-01-01'       2         200          
3     1          null          null        100           1
4     2       '2018-01-02'       2         400
5     2       '2018-01-02'       4         800
6     2          null          null        200           1
7     3       '2018-01-03'       5        1500
8     3          null          null        300           1

这些船有特定日期和持续时间(以天为单位)的价格。所有船只都有一个默认的“起始”价格，由 date = null 和 duration = null 标识。但是，并不是所有的船都有全天的价格。当我搜索特定日期和持续时间的船价时，查询应返回包含该日期和持续时间的价格的所有行，如果船没有该日期的价格，则返回其“起始”默认价格。

示例:对于日期 = '2018-01-01 和持续时间 = 1，结果应为:

boat_prices
id boat_id       date       duration      price       is_default
----------------------------------------------------------------
1     1       '2018-01-01'       1         100       
6     2          null          null        200           1
8     3          null          null        300           1

我做这个查询示例只是为了简化，但请考虑除此之外，该查询还有一些与其他表的其他连接。

我需要有关查询的帮助。

Answer 1

我相信 Rick 离开加入的方向是正确的，但您可能需要两个。一种是获取符合感兴趣日期的船价，另一种是明确获取默认值。

select 
      b.id,
      b.name,
      DefPrice.price as DefaultPrice,
      Specials.price as SpecialsPrice,
      COALESCE( Specials.price, DefPrice.price ) as DiscountOrDefaultPrice
   from
      ( select @parmDate = '2018-01-01' ) sqlvars,
      boats b
         JOIN boat_prices DefPrice
            on b.id = DefPrice.boat_id
           AND DefPrice.date IS NULL
           AND DefPrice.Duration IS NULL
         LEFT JOIN boat_prices Specials
            on b.id = Specials.boat_id
           AND Specials.date <= @parmDate
           AND @parmDate <= Date_Add( Specials.Date, INTERVAL (Specials.duration -1 ) DAY )

现在，如果没有特价价格，您始终可以通过执行 COALESCE() 仅返回一个有问题的价格，它通过 DiscountOrDefaultPrice 列获取默认价格。

选择要运行的列的版本。这应该得到所有的船，不管基于持续时间的一些特殊价格。当您更改相关参数日期时，即使您更改当前日期，它也会起作用。这是因为您正在根据所有可能的特价船价格及其开始到开始 + 持续时间结束日期范围来测试相关日期。如果您有多个日期重叠的价格，则只会返回重叠的多行。

我的持续时间加法是减1。例如，如果您的日期是 2018-01-01 并且它适用于 1 天，是否意味着它只适用于那一天？或截至并包括 2018-01-02。 -1 强制限定为仅一天。所以 2018-01-01 的价格只适用于 1 天 2018-01-01。

您的 2018-01-02 的另一个示例有两天的持续时间。对我来说，表示 2 天，包括 01-02 到 01-03。实际两天。

来自关于日期和范围的评论的确认

我猜我当时对您的数据需求的解释是错误的。您的两个过时的船价记录样本显然是不够的。你说你想要所有的船，不管特价记录的资格。所以你必须从船和连接开始，无论如何都要获得所有可能的“默认”定价。只有LEFT-JOIN组件需要调整。

话虽如此，让我们模拟更多数据。假设您有以下条件

Boad ID    Date        Duration    Rate
1        2018-01-01       1        x
1        2018-01-02       4        y
2        2018-01-02       2        z
2        2018-01-04       4        a
3        2018-01-03       5        b

如果我提供日期 2018-01-01，我应该看到什么费率记录？如果我提供日期2018-01-03，有什么记录？如果我提供日期2018-01-05，记录什么？