如何获得房间 [英] How to get rooms available

问题描述

help me
The sql statement obtains the free space in the attached table, the roomAvailable field is the number of available rooms that day, the sql statement must now check for room availability from date to date.

this is structure table

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RoomRate](
	[roomRateID] [int] IDENTITY(1,1) NOT NULL,
	[roomID] [int] NOT NULL,
	[roomRate] [float] NOT NULL,
	[roomAvailable] [int] NOT NULL,
	[roomRateDate] [date] NOT NULL,
	[roomRateState] [bit] NOT NULL,
 CONSTRAINT [PK_RoomRate] PRIMARY KEY CLUSTERED 
(
	[roomRateID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[RoomRate] ON
INSERT [dbo].[RoomRate] ([roomRateID], [roomID], [roomRate], [roomAvailable], [roomRateDate], [roomRateState]) VALUES (1, 55, 100, 4, CAST(0xCC3D0B00 AS Date), 1)
INSERT [dbo].[RoomRate] ([roomRateID], [roomID], [roomRate], [roomAvailable], [roomRateDate], [roomRateState]) VALUES (2, 55, 100, 3, CAST(0xCD3D0B00 AS Date), 1)
INSERT [dbo].[RoomRate] ([roomRateID], [roomID], [roomRate], [roomAvailable], [roomRateDate], [roomRateState]) VALUES (3, 55, 100, 1, CAST(0xCE3D0B00 AS Date), 1)
INSERT [dbo].[RoomRate] ([roomRateID], [roomID], [roomRate], [roomAvailable], [roomRateDate], [roomRateState]) VALUES (4, 55, 100, 0, CAST(0xCF3D0B00 AS Date), 1)
INSERT [dbo].[RoomRate] ([roomRateID], [roomID], [roomRate], [roomAvailable], [roomRateDate], [roomRateState]) VALUES (5, 55, 100, 3, CAST(0xD03D0B00 AS Date), 1)
INSERT [dbo].[RoomRate] ([roomRateID], [roomID], [roomRate], [roomAvailable], [roomRateDate], [roomRateState]) VALUES (6, 55, 100, 0, CAST(0xD13D0B00 AS Date), 1)
INSERT [dbo].[RoomRate] ([roomRateID], [roomID], [roomRate], [roomAvailable], [roomRateDate], [roomRateState]) VALUES (7, 55, 100, 5, CAST(0xD23D0B00 AS Date), 1)
INSERT [dbo].[RoomRate] ([roomRateID], [roomID], [roomRate], [roomAvailable], [roomRateDate], [roomRateState]) VALUES (8, 55, 100, 3, CAST(0xD33D0B00 AS Date), 1)
INSERT [dbo].[RoomRate] ([roomRateID], [roomID], [roomRate], [roomAvailable], [roomRateDate], [roomRateState]) VALUES (9, 55, 100, 2, CAST(0xD43D0B00 AS Date), 1)
SET IDENTITY_INSERT [dbo].[RoomRate] OFF
/****** Object:  Default [DF_RoomRate_roomRate]    Script Date: 01/25/2018 20:05:29 ******/
ALTER TABLE [dbo].[RoomRate] ADD  CONSTRAINT [DF_RoomRate_roomRate]  DEFAULT ((0)) FOR [roomRate]
GO
/****** Object:  Default [DF_RoomRate_roomRateState]    Script Date: 01/25/2018 20:05:29 ******/
ALTER TABLE [dbo].[RoomRate] ADD  CONSTRAINT [DF_RoomRate_roomRateState]  DEFAULT ((1)) FOR [roomRateState]
GO

我的尝试：

What I have tried:

how to get rooms available

推荐答案

很明显......你必须使用WHERE子句 [ ^ ]：

It's quite obvious... You have to use WHERE clause[^]:

SELECT *
FROM RoomRate
WHERE roomRateDate BETWEEN '2018-01-01' AND '2018-01-31'

欲了解更多详情，请参阅：

SELECT示例（Transact-SQL）| Microsoft Docs [ ^ ]

这篇关于如何获得房间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！