如何使sum()只对min()选择的乘客求和？

23c0lvtd 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(266)

我有一个关于sql server的问题。我试图得到航班数最少的航空公司，也必须得到航空公司选择的乘客总数 MIN() 在他们的航班上有过。我做了一个代码让我可以得到 MIN() 以及 Name 航空公司的，但我不能用正确的方法得到总数，它总是把所有的东西都加起来。我将插入我的数据库表和用于获取 MIN() . 请记住，我不能使用显式值。
我的table：

FLIGHTS
NumFlight  |       Date          | Origin |Destination|  Airline  | NumPassengers | International|
___________|_____________________|________|___________|___________|_______________|______________|
44             2014-02-08            2          3           3           135              Yes
76             2014-03-17            2          1           2            80              No
380            2014-06-04            5          1           2           147              No
421            2014-04-21            1          2           1           185              No
572            2014-09-12            5          4           3            99              Yes
906            2014-05-10            3          2           3           154              Yes
918            2014-12-24            1          5           1           122              No

AIRLINES
   AirlineID  |   Name    |
______________|___________|
       1         Delta
       2         United
       3        Air France

CITY
    CityID    |      City       |
______________|_________________|
      1           Chicago
      2           New York
      3           Paris
      4           Madrid
      5           Houston

我的问题：

SELECT A.Name, MINIMUM.[# of Flights] AS 'N. of Flights'
FROM AIRLINES A,
     (SELECT MIN(F.Total) AS '# of Flights' FROM 
        (SELECT Airline, COUNT(Airline) AS 'Total' FROM FLIGHTS GROUP BY Airline) F,
        AIRLINES A
    WHERE A.AirlineID = F.Airline) MINIMUM,
    (SELECT Airline, COUNT(Airline) AS 'Total' FROM FLIGHTS GROUP BY Airline) TOTAL
WHERE TOTAL.Total = MINIMUM.[# of Flights] AND A.AirlineID = TOTAL.Airline

sql sql-server subquery

来源：https://stackoverflow.com/questions/62876805/how-do-i-make-sum-to-only-sum-the-passengers-selected-by-the-min

2条答案

按热度按时间

jrcvhitl1#

我有一个关于sql server的问题。我想让航班最少的航空公司
我建议使用 top (1) with ties :

select a.*, f.numflights, f.NumPassengers
from airlines a join
     (select top (1) with ties airlineid, count(*) as numflights, sum(NumPassengers) as NumPassengers
      from flights f
      group by airlineid
      order by count(*) asc
     ) f
     on f.airlineid = a.airlineid;

赞(0）回复(0）举报 2021-07-26

ajsxfq5m2#

您可以将窗口函数用于 apply :

select a.name, f1.Total_flights, TotalPassenger
from AIRLINES a inner join
     (select Airline, count(*) as Total_flights, 
              row_number() over (partition by Airline order by count(*)) as seq
      from FLIGHTS
      group by Airline
     ) fl
     on fl.Airline = a.AirlineID and seq = 1 cross apply
     (select sum(f.NumPassengers) as TotalPassenger
      from FLIGHTS f
      where f.Airline = f1.Airline
     ) fpassenger;

赞(0）回复(0）举报 2021-07-26

我来回答

如何使sum()只对min()选择的乘客求和？

2条答案

相关问题

热门标签

最新问答