php - 将 union 与嵌套选择和临时表结合使用

Question

这是 @amdixon 在 this thread 上给出的出色答案的后续问题前几天。

快速总结:我有这个 MySql 表(简化):

tbl_cards
ID  FROM        TO
--------------------------
1  2015-10-01  2015-10-08
2  2015-10-06  2015-10-12
3  2015-10-06  2015-10-15
4  ...

我需要一个 SELECT 来检查例如之间的每个日期。 2015-10-01 和 2015-12-31 并返回 3 个(或任意数量)ID 重叠的日期。有些日期不会有任何记录，而另一些日期可能有很多记录。

@amdixon 为我提供了这个答案，这正是我所需要的(请注意，还涉及一个名为“digits”的 mySql View 。看看上面的线程)

select @index := @index + 1 as `Index`, `Date`
from
(
select date_format(calendar.dy, '%Y-%m-%d') as `Date`
from
(
select date_add(date('2015-10-01'), interval (a2.digit * 100) + (a1.digit * 10) + a0.digit day) as dy
from digits a2
cross join digits a1
cross join digits a0
where date_add('2015-10-01', interval (a2.digit * 100) + (a1.digit * 10) + a0.digit day) <= date('2015-12-31')
order by date_add('2015-10-01', interval (a2.digit * 100) + (a1.digit * 10) + a0.digit day)
) calendar
inner join tbl_cards t
on calendar.dy between t.`from` and t.`to`
group by calendar.dy
having count(calendar.dy) = 3
) dts
cross join ( select @index := -1 ) params
;

对于我的后续问题，可能有必要提供一些进一步的细节。 tbl_cards 表用于存储可以涵盖从一到十个日期的所有预订。因此就有 from 和 to 字段。

现在我的一位客户要求我为此制作一个购物车，这意味着每个用户可以在一笔交易中进行多次预订。我想通过创建一个 tbl_shoppingcart 来完成这项工作，其中预订会临时存储 30 分钟左右，直到用户完成并完成付款。

但是，这使得上面的优秀查询过时了，因为 calendar 现在需要同时与 tbl_cards 和 tbl_shoppingcart 进行比较，并确保任何给定日期的确认预订 + 购物车预订总数不超过 3(或我的客户定义的任意数字)。

我确定会涉及到 UNION，但我真的不确定将其放在哪里，因为 HAVING COUNT 子句应该跨越两个表。

这是sqlfiddle由@amdixon 提供。

Answer 1

在绞尽脑汁大约 24 小时后，我能够从 Stack Overflow 上的类似帖子(但查询不那么复杂)得出正确的查询:

select @index := @index + 1 as `Index`, `Date`
from
(
  select date_format(calendar.dy, '%Y-%m-%d') as `Date`
  from
  (
    select date_add(date('2015-10-01'), interval (a2.digit * 100) + (a1.digit * 10) + a0.digit day) as dy
    from digits a2
    cross join digits a1
    cross join digits a0
    where date_add('2015-10-01', interval (a2.digit * 100) + (a1.digit * 10) + a0.digit day) <= date('2015-12-31')
    order by date_add('2015-10-01', interval (a2.digit * 100) + (a1.digit * 10) + a0.digit day)
  ) calendar
  inner join
  (
    SELECT `ID`, `from`, `to` FROM `tbl_cards`
    UNION
    SELECT `ID`, `from`, `to` FROM `tbl_shoppingcart`
  ) t
  on calendar.dy between t.`from` and t.`to`
  group by calendar.dy
  having count(calendar.dy) = 3
) dts
cross join ( select @index := -1 ) params
;

显然我对派生表和子查询了解不够，否则这将是一个简单的定制。看来我今天也学到了新东西:)