sql - SQL Oracle 仅返回列值的最大值（值）

Question

我的数据看起来像这样。

我希望看到的输出是：

这是我写的一个查询，它还没有完成，但我知道我应该做什么的逻辑。我需要取相同vehicle_size和竞争对手的column(rate)的最大值。这可以用更好的方式编写，所以如果有人能指出我正确的方向，我将不胜感激。

以下是我的查询：

SELECT RENTAL_DATE, OUTBOUND, INBOUND CASE WHEN Competitor = 'kay' AND VEHICLE_SIZE= 'Small' THEN MAX(RATE) WHEN COMPETITOR = 'lola' AND VEHICLE SIZE = 'Small'THEN MAX(RATE) WHEN Competitor = 'kay' AND VEHICLE_SIZE= 'Large' THEN MAX(RATE) WHEN COMPETITOR = 'lola' AND VEHICLE SIZE = 'Large'THEN MAX(RATE) ELSE 'RATE' END AS RATE FROM FORMATTED2018AND2019DATA;

第二个查询：

select Rental_date, outbound, inbound, vehicle_size, max(rate) where competitor='lola' and vehicle_size = 'small' OR 'large' max(Rate)where competitor ='kay' and vehicle_size = 'small' OR 'large' from table2 group by Rental_date, outbound, inbound, vehicle_size,

标签： sqloracle

Answer 1

试试下面 -

select  Rental_date, competitor,outbound, inbound, vehicle_size, 
        max(rate) 
        from table2 
        group by 
        Rental_date, competitor,outbound, inbound, vehicle_size