查询以查看Oracle中的MAX值 [英] Query to see the MAX value in Oracle

问题描述

我在Oracle数据库(大约是一个机场)中有以下N:N表(用于存储人员登机):

I have the following N:N table (which stores the people boardings) in my Oracle Database (it is about an airport):

CREATE TABLE boardings(
Passport VARCHAR2(8),
Day DATE,
Flight VARCHAR2(8),
LuggageWeight NUMBER(4,2),
CONSTRAINT pk PRIMARY KEY(Passport, Day, Flight));

我想查询一下每个航班，那一天是最重的一天(请记住，同一趟航班，例如RY-1234-VY，可以在不同的日子进行不同的旅行.我一直在尝试类似的方法，但这种方法不起作用:

And I would like to make a query in order to see for each flight, which has been the day that has transported the highest amount of weight (keep in mind that a same flight, as RY-1234-VY for example, can make different travels in different days. I have been trying something like this, but it doesn't work:

SELECT Day, Flight
FROM test
GROUP BY Day, Flight
HAVING SUM(LuggageWeight) = (SELECT MAX(SUM(LuggageWeight))
                             FROM test
                             GROUP BY Day, Flight);

推荐答案

我认为您很亲近.

SELECT Day, Flight
FROM boardings b1
GROUP BY Day, Flight
HAVING SUM(LuggageWeight) = (SELECT MAX(SUM(LuggageWeight))
                             FROM boardings b2
                             where b1.Flight = b2.Flight -- I have added this line
                             GROUP BY day, flight);

这是一个演示

是这样吗? :

SELECT Flight, Day
FROM boardings b1
where (Flight, Day) = (SELECT Flight, Day
                     FROM boardings b2
                     where b2.flight = b1.flight
                     GROUP BY Flight, Day
                     order by SUM(LuggageWeight) desc
                     fetch first 1 rows only)

这篇关于查询以查看Oracle中的MAX值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！