mysql查询室可用性 [英] mysql query room availability

问题描述

到目前为止，我已经看过其他一些有关mysql可用性查询的问题，但我的工作方式与其他方式略有不同.

Having looked at some of the other mysql availability query questions on here has got me so far, but the way mine works is slightly different to the others.

基本上，我有一个日期表，每个日期都是一行.每行都包含用于说明费用，房型以及该日期必须预订的天数的字段.因此，如果一家酒店有1种房型，它将有365行，如果有5种房型，它将有1825行

Basically I have a table of dates, and each date is a row. Each row contains fields to say the cost, room type, and how many days your booking has to be to book on that date. So if a Hotel has 1 room type, it will have 365 rows, if it has 5 room types, it will have 1825 rows

bd_id   int(10) NO  PRI NULL    auto_increment
bd_room_type    varchar(32)
bd_date date    NO
bd_price    decimal(8,2)
bd_h_id int(8)  NO /* Hotel id */
bd_available    tinyint(1) /* number of days you must book to include this date */

我可以确定出发日期和结束日期，然后填写两者之间的间隔，这样我就可以预订所有日期.

I get the from and to dates, and fill in the gap in between so I have all the dates for the booking.

$q1 = "SELECT bd_h_id
FROM booking_dates
WHERE bd_date IN ('2011-02-16','2011-02-17','2011-02-18')
AND bd_available <= '3'
AND bd_room_type = 'single'
AND bd_price > '0'
GROUP BY bd_h_id
HAVING count(*) = '3'";

因此，如果计数与持续时间相同，则意味着已满足所有依赖性，并且可以在搜索中显示结果.

So if the count is the same as the duration, it means all dependencies have been met and it can show the result in the search.

然后我将该查询作为变量传递给另一个查询，该查询提取酒店信息，检查子查询是否返回任何内容.

I am then passing that query as a variable into another query, that extracts the hotel info, checks to see if the sub query is returning anything or not.

$q = "SELECT c_title FROM c_content WHERE c_id IN ($q1) AND IF(($q1) > 0, 1, 0)";

这很好，但是，如果子查询返回的酒店多于1家，则主查询给我错误:

This is fine, however if the sub query returns more than 1 hotel, the main query gives me the error:

子查询返回1行以上

Subquery returns more than 1 row

我想是因为我使用了"IN"就可以了.有什么建议?同样，当我打算实现多个子查询时，该解决方案也需要与此一起工作.

I thought because I used 'IN' that it would be ok. Any suggestions? Also when I go about implementing multiple sub queries, the solution will need to work with that too.

我知道我的房间制作方式并不尽如人意，但是我不确定如何才能达到所需的效果，因为预订通常是批量预订(例如，多个预订)一次预订的房间，如果在提供的日期内没有一种房型，则需要从搜索结果中删除整个酒店.

I am aware that the way I am doing the rooms isnt as elegant as it could be, however I am not sure how else to achieve the results I need, as because the bookings will usually be bulk bookings (eg more than one room booked at a time, if one room type isnt available during the dates provided then the whole hotel needs to be removed from the search results).

推荐答案

IF(($q1) > 0, 1, 0)

这是返回错误的部分.

此外，$q1的评估方式也被评估了两次，这可能不是您想要的.

Also, the way you have it, $q1 is being evaluated twice, which is probably not what you want.

如果我正确理解您要执行的操作，那么您应该可以省去这部分内容.如果$q1不返回任何行，则IN表达式根本不匹配任何内容.

If I understand correctly what you're trying to do, then you should be able to just leave this part out. If $q1 returns no rows, then the IN expression simply won't match anything at all.

还应注意，带有子查询的IN在MySQL中效率很低；加入连接后运行速度会更快:

Should note as well that IN with subqueries is rather inefficient in MySQL; would run much faster with a join:

SELECT `c_title` FROM `c_content` JOIN ($q1) `a` ON `c_content`.`c_id`=`a`.`bd_h_id`

这篇关于mysql查询室可用性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！