sql - 条件SQL查询

我正在显示下表中给出的属性。现在，我要做的是，在以下位置找到位于相同位置的属性(假设我的属性位于sec-19，匹配sec-19，如果在那里都找不到它们，则搜索整个城市)应该在10天后发布，或者如果没有在10天后发布，则应以30天为基础计算结果。

我有下面提到的下表(属性):



现在，我要从该表中检索的是那些上市时间少于10天的属性(property)的“属性”和“平均价格”，如果没有一个少于10天，则返回结果的时间少于30天。

谁能帮我解决这个问题。提前致谢。

或者，只要没有位置匹配，任何人都可以回答我。

我需要根据10天前发布的属性计算“平均价格”，如果没有10天前发布的属性，则将其作为30天之前。像这样的东西:

Select AVG(Price) As Average_Price from Properties where (DATEDIFF(day,listingtime,getdate())<30 or DATEDIFF(day,listingtime,getdate())<10)

但是在这里，我只获得一个“平均价格”字段，在这里，我也没有放置检查以筛选它是10天前还是30天前过帐的。仔细检查并尝试解决我的问题。提前致谢。

最佳答案

我花了一些时间，我相信我已经解决了您的所有担忧。我不确定城市或位置的数据类型，因此我使用了varchar(100)，这应该可以解决您的所有问题。如果您描述的情况无法解决，请发表评论。

   CREATE PROCEDURE [dbo].[GetRecentlyListedProperties]
(@location varchar(100), @city varchar(100),@propertyID int)
As
Begin
DECLARE @numberOfDays int,
        @propertyCount int,
        @IsLocation bit -- looking for a location and not a city
SET @Propertycount = 0
SET @numberOfDays= 10
-- CHECK TO SEE IF THERE ARE LISTINGS IN THE LAST 10 DAYS IN THE SAME LOCATION
SELECT  @PropertyCount =
 Count(*) FROM properties where location = @location and DATEDIFF(day,listingtime,GETDATE()) < @numberOFDays
 and PropertyID != @propertyID
If(@PropertyCount = 0)
Begin
-- CHECK TO SEE IF THERE ARE LISTINGS IN THE LAST 10 DAYS IN THE SAME CITY
SELECT  @PropertyCount = Count(*) from properties where city = @city
        AND DATEDIFF(day,listingtime,GETDATE()) < @numberOFDays
        AND PropertyID != @propertyID
    IF(@PropertyCount = 0 )
    BEGIN
    SET @NumberOfDays = 30
    -- CHECK TO SEE IF THERE ARE LISTINGS IN THE LAST 30 DAYS IN THE SAME LOCATION
    SELECT  @PropertyCount = COUNT(*) from properties where location = @location
            AND DATEDIFF(day,listingtime,GETDATE()) < @numberOFDays
            AND PropertyID != @propertyID
        IF(@PropertyCount = 0 )
        BEGIN
        -- CHECK TO SEE IF THERE ARE LISTINGS IN THE LAST 30 DAYS IN THE SAME CITY
        SELECT @PropertyCount = Count(*) from properties where city = @city
                AND DATEDIFF(day,listingtime,GETDATE()) < @numberOFDays
                AND PropertyID != @propertyID
        END
        ELSE
        SET @IsLocation = 1 --There are properties in the same location in the last 30 days
    END
    ELSE
    SET @IsLocation  = 0 -- There are properties listed int he city in the last 10 days
End
Else
SET @IsLocation = 1
-- This is where the appropriate results are returned.
IF(@IsLocation = 1)
Begin
SELECT * ,(SELECT AVG(PRICE) as AveragePrice
       FROM PROPERTIES
       WHERE DATEDIFF(day,listingtime,GETDATE()) < @numberOFDays
         AND Location = @Location
         AND PropertyID != @propertyID)
FROM Properties
WHERE DATEDIFF(day,listingtime,GETDATE()) < @numberOFDays
      AND Location = @Location
      AND PropertyID != @propertyID
End
ElSE
SELECT * ,(SELECT AVG(PRICE) as AveragePrice
      FROM PROPERTIES
          WHERE DATEDIFF(day,listingtime,GETDATE()) < @numberOFDays
          AND City = @City
          AND PropertyID != @propertyID)
FROM Properties
WHERE DATEDIFF(day,listingtime,GETDATE()) < @numberOFDays
      AND City = @City
      AND PropertyID != @propertyID
End

您可能需要更改外键的位置和城市的某些数据类型，因为我将它们用作varchars。

关于sql - 条件SQL查询，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/2235889/