php - MYSQL 多表选择查询

Question

我有 2 张 table ，

1. 价格(pcode、日期、每周价格)
   • 1 条记录涵盖 1 周
2. 已预订(pcode、日期)
   • 1 条记录 = 1 天，因为有短暂休息

一个表单由 3 个字段组成

1. 开始日期
2. 灵 active (+/- 1/2/3 周)
3. 持续时间 (3/4/5/6/7/14))。

下面的查询应该选择价格和日期，并检查所选的开始日期是否出现在价格表中，以及从开始日期开始的每一天是否出现在预订表中。

SELECT SUM(priceperweek) AS `ppw`, prices.date AS `startdate`
FROM `prices` LEFT JOIN `booked` ON prices.pcode=booked.pcode
WHERE prices.pcode='A2CD59GH'
    AND (prices.date IN ('20131221', '20131228')
        AND booked.date NOT IN ('20131221', '20131222', '20131223',
        '20131224', '20131225', '20131226', '20131227', '20131228',
        '20131229', '20131230', '20131231', '20140101', '20140102',
        '20140103')
    )
    OR (prices.date IN ('20131214', '20131221')
        AND booked.date NOT IN ('20131214', '20131215', '20131216',
            '20131217',
            '20131218', '20131219', '20131220', '20131221', '20131222',
            '20131223', '20131224', '20131225', '20131226', '20131227')
    )
    OR (prices.date IN ('20131228', '20140104') AND booked.date NOT IN
        ('20131228', '20131229', '20131230', '20131231', '20140101',
        '20140102', '20140103', '20140104', '20140105', '20140106',
        '20140107', '20140108', '20140109', '20140110')
    )
GROUP BY prices.date
ORDER BY prices.date ASC

给定查询的值...

• 开始日期 = 20131221
• 持续时间 = 14
• 属性 = A2CD59GH
• 正负 = 1

我的问题是，即使某个范围内的某些日期出现在“bookings”表中并且 ppw 值比我预期的要多得多，此查询也会返回记录。

使用 SUM(ppw) 的原因是，当指定的持续时间为 14 时，价格将把两周相加。

感谢您对此的任何帮助

Answer 1

您的方法的问题在于，只有在 booked 表中的每一条记录都从结果中过滤开始日期给定的 pcode 属于预订期限。显然，如果该特性是在其他日期预订的，情况就不会如此。

我建议按照以下方式执行反连接:

SELECT   t.date, SUM(prices.priceperweek) FROM prices JOIN (
  SELECT   prices.date
  FROM     prices LEFT JOIN booked
              ON booked.pcode = prices.pcode
             AND booked.date BETWEEN prices.date
                                 AND prices.date + INTERVAL 14 DAY
  WHERE    booked.pcode IS NULL
       AND prices.pcode = 'A2CD59GH'
       AND prices.date BETWEEN '20131221' - INTERVAL 1 WEEK
                           AND '20131221' + INTERVAL 2 WEEK
) t ON prices.date BETWEEN t.date AND t.date + INTERVAL 13 DAY
GROUP BY t.date