咨询：如何实现查询商务舱座位数大于20的所有飞机的SQL语句

你可以通过分组统计+条件筛选直接实现需求，以下提供两种适配不同编码习惯的写法：

简洁版查询语句 #

SELECT 
    a.aircraft_code,
    COUNT(*) AS business_seat_count
FROM Aircrafts a
JOIN Seats s ON a.aircraft_code = s.aircraft_code
WHERE s.fare_conditions = 'Business'
GROUP BY a.aircraft_code
HAVING COUNT(*) > 20;

适配你现有代码结构的CTE版 #

如果你习惯用CTE组织逻辑，可以基于你已写的代码修改：

WITH aircraft_business_seats AS (
    SELECT 
        a.aircraft_code,
        s.fare_conditions
    FROM Aircrafts a
    JOIN Seats s ON a.aircraft_code = s.aircraft_code
    WHERE s.fare_conditions = 'Business'
)
SELECT 
    aircraft_code,
    COUNT(*) AS business_seat_count
FROM aircraft_business_seats
GROUP BY aircraft_code
HAVING COUNT(*) > 20;

关键逻辑说明 #

• WHERE子句：先筛选出所有商务舱座位记录，减少后续统计的数据量
• GROUP BY：按飞机代码分组，将同一架飞机的商务舱座位归为一组
• HAVING子句：对分组后的统计结果做筛选，只保留座位数大于20的飞机

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