我在进行查询时遇到很大的困难,我想根据另一个表中的平均值对查询进行排序。我有一个带有HotelID,HotelName,PriceName,PricePerNight,LeftCoordinate和TopCoordinate的tblHotels表,以及一个带有HotelID和HotelRating的tblRatings表(各个用户/多个评级从1到10排名)。
我希望我的查询显示所有PricePerNight(来自tblHotels)在给定范围内(最低值和最高值之间)的酒店,然后按其在tblRatings中的等级对其进行排序。
SELECT tblRatings.HotelID
, tblHotels.HotelName
, LeftCoordinate
, TopCoordinate
, AVG(HotelRating) AS Rating
FROM tblRatings, tblHotels
WHERE ( SELECT HotelID
FROM tblHotels
WHERE PricePerNight BETWEEN LowerValue AND UpperValue) =
tblRatings.HotelID
GROUP BY tblRatings.HotelID
ORDER BY AVG(HotelRating) DESC
SELECT tblRatings.HotelID
, HotelName
, LeftCoordinate
, TopCoordinate
, AVG(HotelRating) AS Rating
FROM tblHotels, tblRatings
WHERE (PricePerNight BETWEEN LowerValue AND UpperValue) AND (tblHotels.HotelID = tblRatings.HotelID)
GROUP BY tblRatings.HotelID
ORDER BY AVG(HotelRating) DESC
我尝试了一个子查询和一个相关的表查询,但是都给我错误“ HotelName不是聚合函数的一部分”
我一直在网上寻找解决方案,但没有成功:(
基本上,您需要按更多内容分组...
SELECT tblRatings.HotelID
, HotelName
, LeftCoordinate
, TopCoordinate
, AVG(HotelRating) AS Rating
FROM tblHotels, tblRatings
WHERE (PricePerNight BETWEEN LowerValue AND UpperValue) AND (tblHotels.HotelID = tblRatings.HotelID)
GROUP BY tblRatings.HotelID, HotelName, LeftCoordinate, TopCoordinate
ORDER BY AVG(HotelRating) DESC
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句