mysql-在所有日期都出现的日期之间进行搜索 [英] mysql - search between dates where all dates appear

问题描述

我正在处理一些导入的数据，这些数据存储有关房间"在特定日期是否可用的详细信息.每个房间都有可用日期的单独条目.

I'm working with some imported data that stores details about whether a "room" is available on a specific day or not. Each room has an individual entry for the date that it is available.

| id  |  date        |  price  |
--------------------------------
|  1  |  2010-08-04  |  45.00  |

用户可以在一个日期范围内进行搜索，搜索需要带回这两个日期之间可用的相关房间.

A user can search across a date range and the search needs to bring back the relevant rooms that are available between those two dates.

换句话说，使用sql查询进行搜索:

In other words using a sql query to search:

where date>=2010-08-04 AND date<=2010-08-09

不能满足要求，因为这将使选定日期之间在某些时间点可用的所有房间都带回，而不是所有有关日期都可以使用的房间.

would not suffice as this would bring back all rooms available at SOME point between the chosen dates not the rooms that are available for ALL of the dates concerned.

我正在考虑以某种方式使用临时日期表来交叉引用该范围内每个日期都有一个条目，但是不确定实现此目的的最佳方法.

I am considering using a temporary date table in some way to cross-reference that there is an entry for every date in the range but are uncertain as to the best way to implement this.

最终的代码平台是PHP，我也在探索是否可以在代码中随后处理数据，但如果可能的话，希望将所有内容都保留在sql中.

The end code platform is PHP and I'm also exploring whether the data can be processed subsequently within the code but would like to keep everything with the sql if possible.

任何提出的建议将不胜感激.

Any suggestions that put forward would be greatly appreciated.

谢谢

推荐答案

更新:我的原始答案与Quassnoi的答案相同，但为时已晚1分钟，因此我决定删除它，然后执行其他操作.该查询不假定(id，date)是唯一的.如果条目不止一个，它将选择最便宜的条目.另外，它还会对总成本求和，并返回总成本，这可能也很有用.

Update: my original answer was identical to Quassnoi's but 1 minute too late, so I decided to delete it and do something different instead. This query does not assume that (id, date) is unique. If there is more than one entry, it selects the cheapest. Also, it also sums the total cost and returns that too which might also be useful.

SELECT id, SUM(price) FROM (
    SELECT id, date, MIN(price) AS price
    FROM Table1
    GROUP BY id, date) AS T1
WHERE `date` BETWEEN '2010-08-05' AND '2010-08-07'
GROUP BY id
HAVING COUNT(*) = DATEDIFF('2010-08-07','2010-08-05') + 1

这篇关于mysql-在所有日期都出现的日期之间进行搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！