SQL在ASP C＃酒店客房供应情况 [英] SQL for hotel room availability in ASP c#

问题描述

我有这三个表：

通过此SELECT语句：

  SELECT DISTINCT r.roomId，rt.roomTypeName，rt.roomTypeDesc，rt.roomTypePrice
从一个房间 -  [R左连接RoomType RT
    在r.roomTypeId = rt.roomTypeId
WHERE roomStatus ='在线'
     AND NOT EXISTS（
         SELECT 1 FROM预订B其中b.roomId = r.roomId
           AND（2015年7月11日'之间b.checkInDate
           和b.checkOutDate OR'2015年8月11日'之间b.checkInDate
           和b.checkOutDate OR（'7/11/2015」＆LT; = b.checkInDate和2015年8月11日'＆GT; = b.checkOutDate）））
为了通过rt.roomTypePrice
 

我得到这样的结果。

我怎么能指望每个房间类型的可用客房的数量，这样我可以在[结果]一个下拉列表，让用户选择他们想要的数量是多少？ （Similiar作为网站Agoda.com。）

解决方案

  SELECT rt.roomTypeName，计数（rt.roomTypeName）
    从一个房间 -  [R左连接RoomType RT
    在r.roomTypeId = rt.roomTypeId
    WHERE roomStatus ='在线'
         AND NOT EXISTS（
             SELECT 1 FROM预订B其中b.roomId = r.roomId
             AND（2015年7月11日'之间b.checkInDate
             和b.checkOutDate OR'2015年8月11日'之间b.checkInDate
             和b.checkOutDate OR（'7/11/2015」＆LT; = b.checkInDate和2015年8月11日'＆GT; = b.checkOutDate）））
    GROUP BY rt.roomTypeName
 

更多关于 GROUP BY ：

http://www.w3schools.com/sql/sql_groupby.asp

这基本上是说，'给我的每一个 roomTypeName 在第1列和所有记录的计数，其中roomTypeName是不是在列2空

这还不显着，但随后被使用组，保证结果集是卷起成不同的 roomTypeName 值，因此计数则适用于与 roomTypeName 值的记录出现的次数。

I have these three tables:

With this SELECT statement:

SELECT DISTINCT r.roomId,rt.roomTypeName,rt.roomTypeDesc,rt.roomTypePrice
FROM Room r left join RoomType rt 
    on r.roomTypeId=rt.roomTypeId 
WHERE roomStatus='Online' 
     AND NOT EXISTS(
         SELECT 1 FROM Reservation b WHERE b.roomId = r.roomId 
           AND ('7/11/2015' BETWEEN b.checkInDate
           AND b.checkOutDate OR '8/11/2015' BETWEEN b.checkInDate 
           AND b.checkOutDate OR ('7/11/2015'<= b.checkInDate AND '8/11/2015' >= b.checkOutDate)))
order by rt.roomTypePrice

I get this result

How can I count the number of available rooms of each room type, so that I can use a drop down list in [RESULT] to allow user select the quantity that they want? (Similiar as the website Agoda.com.)

解决方案

SELECT rt.roomTypeName, count(rt.roomTypeName)
    FROM Room r left join RoomType rt 
    on r.roomTypeId=rt.roomTypeId 
    WHERE roomStatus='Online' 
         AND NOT EXISTS(
             SELECT 1 FROM Reservation b WHERE b.roomId = r.roomId 
             AND ('7/11/2015' BETWEEN b.checkInDate
             AND b.checkOutDate OR '8/11/2015' BETWEEN b.checkInDate 
             AND b.checkOutDate OR ('7/11/2015'<= b.checkInDate AND '8/11/2015' >= b.checkOutDate)))
    GROUP BY rt.roomTypeName

More on GROUP BY:

http://www.w3schools.com/sql/sql_groupby.asp

This basically says 'give me every roomTypeName in column 1 and a count of all records where the roomTypeName is not null in column 2'

That's not yet distinct, but then the use of GROUP BY ensures the results set is 'rolled up' into distinct roomTypeName values, thus the count then applies to the count of occurrences of records with that roomTypeName value.

这篇关于SQL在ASP C＃酒店客房供应情况的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！