Oracle查询需求：按终端统计各航空公司航班数量

Here's the complete Oracle query that will produce your desired output, using conditional aggregation and CASE statements to handle the mappings:

SELECT
    COUNT(CASE WHEN Carrier IN (1, 2) THEN 1 END) AS "count(Delta)",
    COUNT(CASE WHEN Carrier IN (3, 4) THEN 1 END) AS "count(Air France)",
    COUNT(CASE WHEN Carrier IN (5, 6) THEN 1 END) AS "count(Lufthansa)",
    CASE 
        WHEN Terminal IN (1, 101) THEN 'T1'
        WHEN Terminal IN (2, 202) THEN 'T2'
        WHEN Terminal IN (3, 303) THEN 'T3'
    END AS terminal
FROM your_table_name  -- Replace this with your actual table name
GROUP BY 
    CASE 
        WHEN Terminal IN (1, 101) THEN 'T1'
        WHEN Terminal IN (2, 202) THEN 'T2'
        WHEN Terminal IN (3, 303) THEN 'T3'
    END
ORDER BY terminal;

How this works: #

• Conditional Counting: Each COUNT(CASE ...) line checks if the row's Carrier falls into the airline's group. If it does, we return 1 (which gets counted), otherwise we return NULL (which COUNT ignores). This gives us the exact count per airline per terminal, including zeros where there are no matching flights.
• Terminal Mapping: The CASE statement in both the SELECT and GROUP BY clauses converts your raw Terminal values (1,101 → T1, etc.) into the grouped terminal codes you need. Grouping by this mapped value ensures we aggregate all flights from the same terminal group together.
• Ordering: The ORDER BY terminal clause ensures the output is sorted T1 → T2 → T3, matching your expected result format.

Alternative using SUM (optional): #

If you prefer, you can use SUM instead of COUNT for the airline counts—this explicitly returns 0 for non-matching rows, which might be more readable:

SUM(CASE WHEN Carrier IN (1, 2) THEN 1 ELSE 0 END) AS "count(Delta)",
SUM(CASE WHEN Carrier IN (3, 4) THEN 1 ELSE 0 END) AS "count(Air France)",
SUM(CASE WHEN Carrier IN (5, 6) THEN 1 ELSE 0 END) AS "count(Lufthansa)"

Either approach will produce the exact output you're looking for.

内容的提问来源于stack exchange，提问作者VijZ