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

I am trying to return the minimum and maximum prices for a villa booking system. I have a look up table that stores the price for each week for each villa.

我正在尝试返回别墅预订系统的最低和最高价格。我有一个查询表,存储每个别墅每周的价格。

I am using the min and max functions to do this within the select but I'm having lots of problems. Can anyone explain where i'm going wrong? Heres the sp

我正在使用min和max函数在select中执行此操作,但是我遇到了很多问题。任何人都可以解释我哪里出错了?继承人

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 added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;

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

2 个解决方案

#1

You don't elaborate on what problems you are getting, but this is probably one: you need to specify all the non-aggregate columns in the GROUP BY clause i.e.:

您没有详细说明您遇到的问题,但这可能是一个:您需要在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

From your follow-up comment is appears that some of your columns are of a data type that can't be used in a GROUP BY clause. Try this instead:

从您的后续注释中可以看出,您的某些列属于无法在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)

#2

Thanks for your help

谢谢你的帮助

When I Group By and include all the columns from the select except the two functions I get the following error

当我分组并包括select中除了两个函数之外的所有列时,我得到以下错误

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

The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator. Msg 306, Level 16, State 2, Procedure spVillaGet, Line 22 The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

除非使用IS NULL或LIKE运算符,否则无法比较或排序text,ntext和image数据类型。消息306,级别16,状态2,过程spVillaGet,第22行无法比较或排序text,ntext和image数据类型,除非使用IS NULL或LIKE运算符。

#1