用于获取不在两个日期之间的记录的SQL连接

Question

我有以下表格：

discount table:
id
name
description
amount

discount_exception
id
from_date
to_date
discount_id (foreign key to discount table)

折扣例外表用于存储折扣对用户不可用的日期范围，因此不应显示折扣。请注意，discount和discount_exception之间存在1:M的关系。换句话说，一个折扣可以有很多例外。

现在，我编写SQL的方法是获取所有折扣，然后在数组中循环它们，并查询discount_exception表，以确定每个折扣是否在特定的日期范围内。我更喜欢修改SQL，这样一次数据库调用就可以获取所有没有例外日期的折扣，这些折扣在指定的日期范围内。

例如，如果用户购买的是在2013-5-1和2013-5-5之间运行的5天服务，我想检查折扣和discount_exception表，以找出哪些折扣具有在2013-5-1和2013-5-5范围内的例外，然后仅显示在指定日期范围内没有例外的折扣。有没有一种方法可以用一条select语句来实现这一点，而不是分解SQL，为每个折扣单独调用数据库？我很难理解SQL，特别是当折扣表和discount_exception表之间存在1:M的关系时。

我尝试了一些类似的东西：

SELECT * FROM discount INNER JOIN `discount_exceptions` ON discount.id = discount_exceptions.discount_id AND (discount_exceptions.date_from NOT BETWEEN '2013-5-1' AND '2013-5-5' OR discount_exception.date_to NOT BETWEEN '2013-5-1' AND '2013-5-5');

但这一点和其他变体似乎并不起作用。你知道我做错了什么吗？

谢谢!

Answer 1

试试这个怎么样：

select * 
   from discount
    where id not in (
        SELECT discount.id FROM discount

             LEFT JOIN discount_exception 
                  ON discount.id = discount_exception.discount_id 
         WHERE ('2013-5-1'  between discount_exception.from_date and discount_exception.to_date ) 
                    OR ('2013-5-5'  BETWEEN discount_exception.from_date  and discount_exception.to_date )
                    OR (discount_exception.from_date between '2013-5-1' and '2013-5-5' )
                    OR (discount_exception.to_date between '2013-5-1' and '2013-5-5') )
 )

添加distinct来获取distinct ID可能更好

​

Answer 2

假设您要查找日期范围为'2013-03-01‘到'2013-03-03’的所有折扣，首先查找适用于此范围的所有discount_exceptions

select e.*
from discount_exception e 
where e.from_date between '2013-03-02' and '2013-03-04'
or e.to_date between '2013-03-02' and '2013-03-04';

将上面的折扣表与折扣表相结合，将为您提供适用于此日期范围的所有折扣例外的折扣ids。使用Distinct关键字，这样就不会得到重复的is。让我们称其为“异常集”

select distinct d.id
from discount_exception e 
join discount d on d.id = e.discount_id
where e.from_date between '2013-03-02' and '2013-03-04'
or e.to_date between '2013-03-02' and '2013-03-04';

然后，您可以对折扣表执行另一个连接，以查找适用于该日期范围的所有折扣(即id不在上面设置的例外中的折扣)。

select *
from discount
where id not in (
  select distinct d.id
  from discount_exception e 
  join discount d on d.id = e.discount_id
  where e.from_date between '2013-03-02' and '2013-03-04'
  or e.to_date between '2013-03-02' and '2013-03-04'
);

Answer 3

要检查交叉点，只需查看其中一个范围的起点是否在另一个范围内。然后形成一个子查询，以排除那些匹配的查询。

set @start_date = CAST('2013-05-01' as date);
set @stop_date = CAST('2013-05-05' as date);

select *
from discounts
where id not in (select discount_id
                 from discount_exception
                 where from_date between @start_date and @stop_date or
                       @start_date between from_date and to_date)