sql - 使用聚合函数（最小/最大）作为select语句的一部分

Question

我试图返回别墅预订系统的最低和最高价格。我有一张查询表，用于存储每个别墅每周的价格。

我使用最小和最大函数来做到这一点内选择，但我有很多问题。任何人都可以解释我要去哪里错了吗？继承人的SP

ALTER PROCEDURE spVillaGet 
-- Add the parameters for the stored procedure here 
@accomodationTypeFK int = null, 
@regionFK int = null, 
@arrivalDate datetime = null, 
@numberOfNights int = null, 
@sleeps int = null, 
@priceFloor money = null, 
@priceCeil money = null 

AS BEGIN - SET NOCOUNT ON加入是为了避免额外的结果集 - 用SELECT语句的干扰。 SET NOCOUNT ON;

-- Insert statements for procedure here 
SELECT tblVillas.name, 
     tblVillas.introduction, 
     tblVillas.italian_introduction, 
     tblVillas.uk_content, 
     tblVillas.italian_content, 
     tblVillas.sleeps, 
     tblVillas.postcode, 
     tblLkUpRegions.regionName, 
     tblLkUpAccomodationTypes.accomodationType, 
     MIN(price) As MinPrice, 
     MAX(price) As MaxPrice 

FROM tblVillas 

LEFT JOIN tblLkUpRegions on tblVillas.regionFK = tblLkUpRegions.regionID 
LEFT JOIN tblLkUpAccomodationTypes on tblVillas.accomodationTypeFK = tblLkUpAccomodationTypes.accomodationId  
LEFT JOIN tblWeeklyPrices on tblWeeklyPrices.villaFK = tblVillas.villaId 

WHERE 

    ((@accomodationTypeFK is null OR accomodationTypeFK = @accomodationTypeFK) 
    AND (@regionFK is null OR regionFK = @regionFK) 
    AND (@sleeps is null OR sleeps = @sleeps) 
    AND tblVillas.deleted = 0) 

GROUP BY tblVillas.name 

Answer 1

你不细说你得到什么问题，但是这可能是一个：你需要指定所有在GROUP非聚合列BY子句即：

GROUP BY tblVillas.name, 
     tblVillas.introduction, 
     tblVillas.italian_introduction, 
     tblVillas.uk_content, 
     tblVillas.italian_content, 
     tblVillas.sleeps, 
     tblVillas.postcode, 
     tblLkUpRegions.regionName, 
     tblLkUpAccomodationTypes.accomodationType 

从您的后续评论看来，您的某些列的数据类型不能在GROUP BY子句中使用。试试这个：

SELECT tblVillas.name, 
      tblVillas.introduction, 
      tblVillas.italian_introduction, 
      tblVillas.uk_content, 
      tblVillas.italian_content, 
      tblVillas.sleeps, 
      tblVillas.postcode, 
      tblLkUpRegions.regionName, 
      tblLkUpAccomodationTypes.accomodationType, 
      (SELECT MIN(price) FROM tblWeeklyPrices where tblWeeklyPrices.villaFK = tblVillas.villaId) As MinPrice, 
      (SELECT MAX(price) FROM tblWeeklyPrices where tblWeeklyPrices.villaFK = tblVillas.villaId) As MaxPrice 
FROM tblVillas 
LEFT JOIN tblLkUpRegions on tblVillas.regionFK = tblLkUpRegions.regionID 
LEFT JOIN tblLkUpAccomodationTypes on tblVillas.accomodationTypeFK = tblLkUpAccomodationTypes.accomodationId  
WHERE 
     ((@accomodationTypeFK is null OR accomodationTypeFK = @accomodationTypeFK) 
     AND (@regionFK is null OR regionFK = @regionFK) 
     AND (@sleeps is null OR sleeps = @sleeps) 
     AND tblVillas.deleted = 0) 

Answer 2

感谢您的帮助

当我GROUP BY和包括所有从选择，除了这两个功能我收到以下错误

Msg 306, Level 16, State 2, Procedure spVillaGet, Line 22 

文本列， ntext和图像数据类型不能进行比较或排序，除非使用IS NULL或LIKE运算符。 消息306，级别16，状态2，过程spVillaGet，行22 除使用IS NULL或LIKE运算符时，不能比较或排序文本，ntext和图像数据类型。