在过程中未使用EXISTS引入子查询时，只能在选择列表中指定一个表达式

Question

我的查询如下：

DECLARE @roomPrice DECIMAL(18, 2) = (SELECT TOP(1) r.Price 
FROM Rooms r
JOIN Trips t ON t.RoomId = r.Id
WHERE r.HotelId = @HotelId AND 
((@Date NOT BETWEEN t.ArrivalDate AND t.ReturnDate) 
OR t.CancelDate IS NULL) AND r.Beds >= @People
ORDER BY r.Price) 

我收到的错误是Msg 116, Level 16, State 1, Procedure udf_GetAvailableRoom, Line 9 [Batch Start Line 0] Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.。我知道我不能在子查询中返回多个列，这就是我使用TOP(1)的原因。有人能向我解释一下问题出在哪里吗？

Answer 1

尝试从Top(1)中去掉括号(请参阅此处的正确语法(：

DECLARE @roomPrice DECIMAL(18, 2) = (SELECT TOP 1 r.Price 
FROM Rooms r
JOIN Trips t ON t.RoomId = r.Id
WHERE r.HotelId = @HotelId AND 
((@Date NOT BETWEEN t.ArrivalDate AND t.ReturnDate) 
OR t.CancelDate IS NULL) AND r.Beds >= @People
ORDER BY r.Price)