获取给定日期范围的ID

 id   start_date    end_date          
 1    2012-02-05   2012-03-10      
 2    2012-03-05   2012-03-25       
 3    2012-03-19   2012-03-27      
 4    2012-04-01   2012-04-20

Table Name: bulbs

Here this table contains information about bulbs which are lights during the given date period. If I select two dates I want to get currently lighting bulb ids for those date range. e.g. for the dates '2012-02-10' and '2012-03-20' this should return ids - 1,2 and 3. I have no idea how to do it. please help.

此表包含有关灯泡的信息,这些灯泡在给定日期期间是灯光。如果我选择两个日期,我想要获得这些日期范围的灯泡ID。例如对于'2012-02-10'和'2012-03-20'的日期,这应该返回ID - 1,2和3.我不知道该怎么做。请帮忙。

3 个解决方案

#1

I dont get how 2012-02-10 and 2012-02-20 would give you ids 1,2,3, if i understand this right it should give just id 1 in that case SQL should be

我不明白2012-02-10和2012-02-20将如何给你ID 1,2,3,如果我理解这是正确的它应该只给id 1在这种情况下SQL应该是

select id from bulbs where
end_date >= '2012-02-10' and
start_date <= '2012-02-20'

#2

select id 
from bulbs
where '2012-02-10' <= start_date
and ('2012-02-20' between start_date and end_date)

#3

SELECT
  *
FROM
  bulbs
WHERE
  start_date <= '2012-02-20'
  AND end_date >= '2012-02-10'

This type of query, however, is extremely slow over large volumes of data.

但是,这种类型的查询对于大量数据来说非常慢。

This is because an index on (start_date, end_date) doesn't help any more than an index on just (start_date).

这是因为(start_date,end_date)上的索引对just(start_date)的索引没有任何帮助。

One way to improve performance to to give both an upper and lower limit on the start date. This can be done if you know the maximum number of days between start_date and end_date.

提高性能的一种方法是在开始日期给出上限和下限。如果您知道start_date和end_date之间的最大天数,则可以执行此操作。

If, for example, they are never more than 60 days appart, you can do this...

例如,如果他们从未超过60天appart,你可以这样做...

SELECT
  *
FROM
  bulbs
WHERE
      start_date <= '2012-02-20'
  AND start_date >= '2012-02-10' - 60
  AND end_date   >= '2012-02-10'

#1