时间戳

首页 > 解决方案 > 如何将同一张表中的 2 个查询与不同的 group by 组合起来？（甲骨文）

sql - 如何将同一张表中的 2 个查询与不同的 group by 组合起来？（甲骨文）

问题描述

我需要计算准时到达和离开。查询准时出发：

SELECT DEPAIRPORT as AIRPORT,
    COUNT(case when A.STATUS = 'Scheduled' and 
    A.ACTUAL_BLOCKOFF is not null then 1 else NULL END) as SCHEDULED,
    COUNT(case when ((A.ACTUAL_BLOCKOFF+ interval '7' hour) - (A.SCHEDULED_DEPDT+ interval '7' hour))*24*60 <= '+000000015 00:00:00.000000000' and 
    A.ACTUAL_BLOCKOFF is not null then 1 else NULL END) as ONTIME
    FROM TABLE A GROUP BY DEPAIRPORT

和查询以计算准时到达：

SELECT COUNT(case when ((A.ACTUAL_BLOCKON + interval '7' hour) - (A.SCHEDULED_ARRDT+ interval '7' hour))*24*60 <= '+000000015 00:00:00.000000000' and 
    A.ACTUAL_BLOCKON is not null then 1 else NULL END) as ARRONTIME
    FROM TABLE A  
    GROUP BY ARRIVALAIRPORT

如何将这些查询组合成 1 个单一查询，以便我可以像这张表一样显示它：

Name #Schedule #OnTimeDeparture #ArrivalOntime AIRPORTX 41 35 20

标签： sqloracle

解决方案

你可以使用这样的东西：

select 
    max(SCHEDULED) as SCHEDULED,
    max(ONTIME)    as ONTIME,
    max(ARRONTIME) as ARRONTIME
from (select 
      count(case when ... ) over(partition by DEPAIRPORT)     as SCHEDULED,
      count(case when ... ) over(partition by DEPAIRPORT)     as ONTIME,
      count(case when ... ) over(partition by ARRIVALAIRPORT) as ARRONTIME
   from a );

但我想你的问题并不完整。您还需要一个密钥来加入不同的航班。

推荐阅读