MySQL找到第一个可用的周末 [英] MySQL find first available weekend

问题描述

我有一张桌子可以保留餐厅。它只有一个restaurant_id和一个日期列，指定实际的日期（我们在谈论整天的预订）。

我想知道下一个可用周末的时间对于特定的餐厅周末是星期六或星期天。如果其中一个可用，那么我们有一个可用的周末。

当然，查询应该考虑当前计算下个周末的时间。

任何人都可以帮忙？

以下是截至目前为止所有保留的日期表的表格结构和数据：

  id id_venue date 
 12 1 2011-04-22 
 13 1 2011-04-23 
 14 1 2011-04-24 
 15 1 2011-04-30 
 16 1 2011-05-07 
 17 1 2011-05-08 
  

如你所见，23-24的周末是满的，所以是5月7日-8日的一个。我需要找到的是2001-05-01的日期，这是今天日期后的第一个星期六或星期日。

解决方案

我认为其他人错过了这个问题...他们认为你的桌子可能已经在所有的周末和一些状态被打开或不开放...我的猜测是，你的表只有一个记录，如果它被保留..因此，您需要根据一些自动查找日期查找不存在的记录...

这是对另一篇文章的修改，已完成这里

虽然我没有更改查询的上下文，但我只放在与您的表相关联的列中。我明白你只是反对一个单一的场地表，所以我（实际上）。然而，为了理解JustDates别名，这个INNER PRE-QUERY通过对任何其他表执行笛卡尔加法来创建一个动态填充的所有日期表。在这种情况下，你的Venue表保留（我没有'请明确地看到你的实际表名称，所以你必须改变）。所以，这实质上是创建一个从任何今天开始的所有日期的表，并延续30天（通过限制），但是可以是40,50,300或者你需要的多少。提供了YourVenueTable至少与您要测试的日期一样多的记录。 （相同的澄清发布，这是从）。 30天，40天或多天的结果已经过滤，只能在1周日或7周六的某一天的某一天进行过滤。因此，应该只返回4月24日4月24日的结果集30，5月1日，5月7日，5月8日，5月14日，5月15日，5月21日，5月28日等。

所以现在你有一个动态创建的结果集所有可能的日子你正在考虑前进。现在，它被加入到你实际的Venue Reservations表中，并被过滤到只返回那些没有找到你所关心的id_venue的DATES。在你的数据示例中，它会在4月23日和24日找到一个匹配，而不是返回这些记录。与4月30日相同...但是，它将发现包括5月1日的资格预审列表中的记录将不会在场地表中找到日期匹配，因此包括正如您所期待的那样...它将继续跳过5月7日和8日，然后返回5月14日，15日，21日，28日等...

  select JustDates.OpenDate 
 
（select 
 @r：= date_add（@r，interval 1 day）OpenDate 
 from 
（select @r：= current_date（））vars，
 Venue 
 LIMIT 30）JustDates 
其中
 DAYOFWEEK（JustDates.OpenDate）IN（1，7）
 AND JustDates.OpenDate NOT IN 
（select Venue。日期
从Venue 
其中Venue.id_venue = IDYouAreInterestedIn 
和Venue.Date = JustDates.OpenDate）
订单
 JustDates.OpenDate 
  

注意，根据其他保留发布，查询re保留日期可用性日期做30以上的限制可以是系统中的任何表，只要它具有最少的天数，你想期待预订...如果你想要所有的可用性，即将到来的一年，你将要在表中使用365个记录，以获得笛卡尔结果，以便通过动态创建的日期记录获取@r。

I have a table which holds restaurant reservations. It just has an restaurant_id and a date column which specify the actual date (we are talking about whole day reservations).

I want to find out when is the next available weekend for a particular restaurant. A "weekend" is either Saturday or Sunday. If one of them is available, then we have an available weekend.

The query should, of course, consider the current time to calculate the next weekend.

Can anyone help?

Here's the table structure and data for the "dates" table which holds all reservations made so far:

id    id_venue    date  
12    1           2011-04-22  
13    1           2011-04-23  
14    1           2011-04-24  
15    1           2011-04-30  
16    1           2011-05-07  
17    1           2011-05-08

As you can see, the weekend of 23-24 is full, so the one of 7-8 May. What I need to find is the date of 2001-05-01 which is the first available Saturday OR Sunday after today's date.

解决方案

I think the others are missing the question... They think your table may already be POPULATED with all weekends and some status as to open or not... My guess is that your table only HAS a record IF it is reserved... thus you need to find records that DO NOT EXIST AT ALL... based on some automated Look for dates...

This is a modification to another post I've done here

Although I didn't change the context of the query, I only put in the columns associated to YOUR table. I understand you are only going against a single venue table and so am I (actually). However, to understand the "JustDates" alias, this INNER PRE-QUERY is creating a dynamically populated table of ALL DATES by doing a Cartesian join against ANY other table.. in this case, your "Venue" table of reservations (I didn't see your actual table name reference explicitly, so you'll have to change that). So, this in essence creates a table of all dates starting from whatever "today" is and goes forward for 30 days (via limit), but could be 40, 50, 300 or as many as you need.. provided the "YourVenueTable" has at least as many records as days you want to test for. (same clarification in post this was derived from). This result set going out 30, 40 or however many days is pre-filtered for ONLY the given day of week of 1-Sunday or 7-Saturday... So it should return a result set of only Apr 23, Apr 24, Apr 30, May 1, May 7, May 8, May 14, May 15, May 21, May 28, etc.

So NOW you have a dynamically created result set of all possible days you are considering moving forward. Now, that gets joined to your actual Venue Reservations table and is filtered to ONLY return those DATES where it is NOT found for the id_venue you are concerned about. In your data example it WOULD find a match on Apr 23 and 24 and NOT return those records. Same with Apr 30... However, it WILL find that the record in the prequalifying list that includes May 1 will NOT find the date match in the venue table and thus include that as you are anticipating... It will then continue to skip May 7 and 8, then return May 14, 15, 21, 28, etc...

select JustDates.OpenDate
  from 
      ( select
         @r:= date_add( @r, interval 1 day ) OpenDate
      from
         ( select @r := current_date() ) vars,
         Venue 
      LIMIT 30 ) JustDates
   where
      DAYOFWEEK( JustDates.OpenDate ) IN ( 1, 7 )
      AND JustDates.OpenDate NOT IN
          ( select Venue.date
                from Venue
                where Venue.id_venue = IDYouAreInterestedIn
                  and Venue.Date = JustDates.OpenDate )
    order by 
       JustDates.OpenDate

Note, and per the other reservations posting, the query for reservation date availability dates doing a limit of 30 above can be ANY table in the system as long as it has AT LEAST as many days out as you want to look forward for reservations... If you want all availability for an upcoming year, you would want 365 records in the table used for a Cartesian result to get the @r cycling through dynamically created "date" records.

这篇关于MySQL找到第一个可用的周末的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！