在同一个表上连接两个不同聚合函数的结果

I'm trying to combine two queries in order to determine which aircraft will be at a particular point (point ABCD) just before a particular time (2011-09-19 04:00:00.000). So far, I generate the last time an aircraft arrived at this point, and the last time an aircraft departed the point. My current idea is that if the last time it arrived is greater then the last time it departed the aircraft is still at point ABCD just before the specified time.

我正在尝试组合两个查询，以便确定在特定时间（2011-09-19 04：00：00.000）之前哪个飞机将在特定点（点ABCD）。到目前为止，我生成了飞机最后一次到达此点，以及飞机最后一次离开该点。我目前的想法是，如果它最后一次到达的时间大于它最后一次离开飞机仍然在指定时间之前的ABCD点。

The first query finds the last time an aircraft arrived at a certain point:

第一个查询查找飞机最后一次到达某个点：

select aircraft_id, MAX(arrival_datetime) as last_arrival
from flight_schedule
where arrival_datetime < '2011-09-19 04:00:00.000' and arrival_point='ABCD'
group by aircraft_id

and the second query finds the last time an aircraft has left this point:

第二个查询找到飞机最后一次离开这一点：

select aircraft_id, MAX(departure_datetime) as last_departure
from flight_schedule
where departure_datetime < '2011-09-19 04:00:00.000' and departure_point='ABCD'
group by aircraft_id

Both of these queries generate the appropriate responses. I realise that in order to compare the last_departure to the last_arrival fields I need to join the tables somehow.

这两个查询都会生成相应的响应。我意识到为了比较last_departure和last_arrival字段，我需要以某种方式加入表。

I'm not an SQL whiz, and in the past any table joins I've done have been between two completely different tables and haven't involved any aggregate functions so my normal subqueries and structure hasn't worked.

我不是一个SQL专家，在过去任何表连接我已经完成了两个完全不同的表之间并没有涉及任何聚合函数所以我的正常子查询和结构没有工作。

4 个解决方案

#1

The easiest solution would be to

• turn each statement into a subquery
• 将每个语句转换为子查询
• join the results together
• 一起加入结果

SQL Statement

select la.aircraft_id, la.last_arrival, ld.last_departure
from  (
    select aircraft_id, MAX(arrival_datetime) as last_arrival
    from flight_schedule
    where arrival_datetime < '2011-09-19 04:00:00.000' and arrival_point='ABCD'
    group by aircraft_id
  ) la
  full outer join (
    select aircraft_id, MAX(departure_datetime) as last_departure
    from flight_schedule
    where departure_datetime < '2011-09-19 04:00:00.000' and departure_point='ABCD'
    group by aircraft_id
  ) ld on ld.aircraft_id = la.aircraft_id

Note that I've used a full outer join. Most likely an inner join would suffice. The full outer join is only needed if there's ever an arival_datetime without a departure_datetime or vice versa (wich is unlikely to happen).

请注意，我使用了完整的外连接。内部联接很可能就足够了。只有在没有departure_datetime的arival_datetime或者反之亦然的情况下才需要完全外连接（不太可能发生）。

#2

Try like this way :

尝试这样：

select dpt.aircraft_id, last_arrival, last_departure
from
(
     select aircraft_id, MAX(arrival_datetime) as last_arrival 
     from flight_schedule 
     where arrival_datetime < '2011-09-19 04:00:00.000' and arrival_point='ABCD' 
     group by aircraft_id 
) dpt
inner join 
    (
      select aircraft_id, MAX(departure_datetime) as last_departure 
      from flight_schedule 
      where departure_datetime < '2011-09-19 04:00:00.000' and departure_point='ABCD' 
      group by aircraft_id 
    ) arr on dpt.aircraft_id = arr.aircraft_id

#3

The following solution uses standard SQL and should work in most, if not all, the major RDBMSes:

以下解决方案使用标准SQL，并且应该在大多数（如果不是全部）主要RDBMS中工作：

SELECT
  aircraft_id,
  CASE
    WHEN MAX(CASE arrival_point   WHEN 'ABCD' THEN arrival_datetime   END) >
         MAX(CASE departure_point WHEN 'ABCD' THEN departure_datetime END)
    THEN 'At the point'
    ELSE 'Somwhere else'
  END AS is_located
FROM flight_schedule
WHERE arrival_datetime   < '2011-09-19 04:00:00.000' AND arrival_point   = 'ABCD'
   OR departure_datetime < '2011-09-19 04:00:00.000' AND departure_point = 'ABCD'
GROUP BY
  aircraft_id

If your particular RDBMS supports CTEs and ranking functions, you could also try a different approach:

如果您的特定RDBMS支持CTE和排名函数，您还可以尝试不同的方法：

WITH events AS (
  SELECT
    aircraft_id,
    arrival_datetime AS event_datetime,
    'At the point' AS is_located
  FROM flight_schedule
  WHERE arrival_datetime < '2011-09-19 04:00:00.000'
    AND arrival_point = 'ABCD'
  UNION ALL
  SELECT
    aircraft_id,
    departure_datetime AS event_datetime,
    'Somewhere else' AS is_located
  FROM flight_schedule
  WHERE departure_datetime < '2011-09-19 04:00:00.000'
    AND departure_point = 'ABCD'
),
ranked AS (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY aircraft_id
      ORDER BY event_datetime DESC
    ) AS event_rank
  FROM events
)
SELECT
  aircraft_id,
  is_located
FROM ranked
WHERE event_rank = 1

#4

Thank you so much. I have worked 10 hours spanning two days to get this to work.

非常感谢。我已经工作了10个小时，为期两天。

In summary, a primary query wraps around any number of sub queries and by naming the sub queries with aliases, you can push data from the sub queries to the main query:

总之，主查询包装任意数量的子查询，并通过使用别名命名子查询，您可以将数据从子查询推送到主查询：

SELECT N.Name_CO_ID, N.Name_Company, N.District_Count, D.DistrictName, D.District_Response
FROM ( 
SELECT Name_CO_ID, Name_Company, COUNT(dbo.SV_Name.Name_CO_ID) AS 'District_Count'
FROM SV_Name 
JOIN dbo.SV_Activity ON dbo.SV_Name.Name_ID = dbo.SV_Activity.Activity_ID
JOIN dbo.SV_Product ON dbo.SV_Activity.Activity_PRODUCT_CODE = dbo.SV_Product.Product_PRODUCT_CODE
AND (dbo.SV_Activity.Activity_ACTIVITY_TYPE = 'COMMITTEE') 
AND (dbo.SV_Product.Product_GROUP_3 = 'DIST') 
AND (dbo.SV_Activity.Activity_THRU_DATE > GETDATE()) 
AND (dbo.SV_Name.Name_CO_MEMBER_TYPE = 'LSD')
GROUP BY Name_CO_ID, Name_Company) N
FULL OUTER JOIN (SELECT DistrictName, COUNT(DistrictName)AS 'District_Response'
FROM ODS_Landing
WHERE (StartDate > @StartDate1) AND (StartDate < @StartDate2)
GROUP BY DistrictName) D ON N.Name_COMPANY = D.DistrictName
WHERE District_Response > 0
ORDER BY Name_Company