MySQL CASE查询执照资格判定结果异常问题求助

我是学SQL才几周的学生，正在完成学校作业。我想通过CASE语句结合累计FlightTime和Tail_Number两个条件，从Flight_Log表查询判定执照资格，但返回结果不符合预期。我希望当累计飞行时长达到对应阈值时，显示对应的资格项。以下是原查询语句、预期结果、表结构和插入数据（使用MySQL 8.0.31），请帮我找出问题所在。

原查询语句 #

SELECT FlightNumber AS 'Flight Number', Date, Aircraft, Aircraft_Manufacturer AS 'Aircraft Manufacturer', Tail_Number AS 'Tail Number', Departure, Arrival, FlightTime AS 'Flight Time', Instructor, Passengers, 
CASE
    WHEN SUM(FlightTime) >= 20 AND Tail_Number LIKE '24-%' THEN "RAAus Recreational Pilots Certificate (RPC)"
    WHEN SUM(FlightTime) >= 25 AND Tail_Number LIKE '24-%' THEN "RAAus RPC Passenger Endorsment"
    WHEN SUM(FlightTime) >= 32 AND Tail_Number LIKE '24-%' THEN "RAAus RPC Cross Country Endorsment"
    WHEN SUM(FlightTime) >= 7 AND Tail_Number LIKE 'VH-%' THEN "RPC Conversion to CASA Recreational Pilot License (RPL)"
    ELSE 'Not Eligible For License'
END AS "License Eligibility"
FROM Flight_Log
GROUP BY FlightNumber
ORDER BY FlightNumber

预期结果 #

当累计飞行时长达到20及以上时触发对应WHEN条件，例如Flight Number为4时累计时长达标，应显示对应资格。当前结果与预期不符。

表结构及插入数据 #

CREATE TABLE IF NOT EXISTS Flight_Log (
    `FlightNumber` INTEGER PRIMARY KEY AUTO_INCREMENT,
    `Date` DATE,
    `Aircraft` VARCHAR(50),
    `Aircraft_Manufacturer` VARCHAR(50),
    `Tail_Number` VARCHAR(50),
    `MTOW` INTEGER,
    `Manufacture_Year` YEAR,
    `Aircraft_Type` VARCHAR(50),
    `Departure` CHAR(4),
    `Arrival` CHAR(4),
    `FlightTime` INTEGER,
    `Instructor` BOOLEAN,
    `Passengers` INTEGER
);
INSERT INTO Flight_Log VALUES (1,'2022-10-08','Tecnam P-92 Eaglet','Tecnam','24-5955',600,1992,'Recreational','YCDR','YCDR',1,'1',0);
INSERT INTO Flight_Log VALUES (2,'2022-12-03','Tecnam P-92 Eaglet','Tecnam','24-5955',600,1992,'Recreational','YCDR','YCDR',3,'1',0);
INSERT INTO Flight_Log VALUES (3,'2022-12-05','Tecnam P-92 Eaglet','Tecnam','24-5955',600,1992,'Recreational','YCDR','YCDR',3,'1',0);
INSERT INTO Flight_Log VALUES (4,'2022-12-08','Tecnam Sierra','Tecnam','24-7155',600,2002,'Recreational','YCDR','YCDR',3,'1',0);
INSERT INTO Flight_Log VALUES (5,'2022-12-17','Tecnam Sierra','Tecnam','24-7155',600,2002,'Recreational','YCDR','YCDR',3,'1',0);
INSERT INTO Flight_Log VALUES (6,'2022-12-27','Fly Synthesis Texan','Fly Synthesis','24-5285',600,1998,'Recreational','YCDR','YCDR',1,'1',0);
INSERT INTO Flight_Log VALUES (7,'2022-12-31','Tecnam Sierra','Tecnam','24-7155',600,2002,'Recreational','YCDR','YCDR',2,'0',0);
INSERT INTO Flight_Log VALUES (8,'2023-01-01','Tecnam P-92 Eaglet','Tecnam','24-5955',600,1992,'Recreational','YCDR','YCDR',2,'0',0);
INSERT INTO Flight_Log VALUES (9,'2023-01-12','Tecnam Sierra','Tecnam','24-7155',600,2002,'Recreational','YCDR','YCDR',1,'0',0);
INSERT INTO Flight_Log VALUES (10,'2023-01-18','Tecnam Sierra','Tecnam','24-7155',600,2002,'Recreational','YCDR','YCDR',4,'0',0);
INSERT INTO Flight_Log VALUES (11,'2023-01-27','Tecnam Sierra','Tecnam','24-7155',600,2002,'Recreational','YCDR','YCDR',3,'0',0);
INSERT INTO Flight_Log VALUES (12,'2023-02-03','Tecnam Sierra','Tecnam','24-7155',600,2002,'Recreational','YCDR','YCDR',3,'0',0);
INSERT INTO Flight_Log VALUES (13,'2023-02-14','Tecnam P-92 Eaglet','Tecnam','24-5955',600,1992,'Recreational','YCDR','YCDR',1,'0',1);
INSERT INTO Flight_Log VALUES (14,'2023-02-14','Tecnam P-92 Eaglet','Tecnam','24-5955',600,1992,'Recreational','YCDR','YCDR',1,'0',1);
INSERT INTO Flight_Log VALUES (15,'2023-03-14','Tecnam P-92 Eaglet','Tecnam','24-5955',600,1992,'Recreational','YCDR','YCDR',1,'0',1);
INSERT INTO Flight_Log VALUES (16,'2023-03-14','Tecnam P-92 Eaglet','Tecnam','24-5955',600,1992,'Recreational','YCDR','YCDR',1,'0',1);
INSERT INTO Flight_Log VALUES (17,'2023-03-28','Cessna 172R','Cessna','VH-CFG',1111,1997,'Recreational','YCDR','YCDR',3,'1',0);
INSERT INTO Flight_Log VALUES (18,'2023-04-06','Cessna 172 G1000','Cessna','VH-IVW',1156,2013,'Recreational','YRED','YRED',3,'1',0);
INSERT INTO Flight_Log VALUES (19,'2023-04-19','Cessna 172 G1000','Cessna','VH-IVW',1156,2013,'Recreational','YRED','YCDR',2,'1',0);
INSERT INTO Flight_Log VALUES (20,'2023-04-23','Cirrus SR22','Cirrus','VH-EDH',1633,2014,'Recreational','YBAF','YBAF',2,'1',0);
INSERT INTO Flight_Log VALUES (21,'2023-05-02','Cirrus SR22','Cirrus','VH-EDH',1633,2014,'Recreational','YBAF','YBAF',2,'1',0);

问题分析 #

1. 分组逻辑错误：你按FlightNumber分组，但FlightNumber是主键，每组仅一条记录，SUM(FlightTime)计算的是单条航班的时长，并非同一尾号的累计值，完全不符合需求。
2. CASE条件顺序错误：条件从低阈值到高阈值排列，比如先判断>=20再判断>=25，导致累计时长达标更高阈值时，会优先匹配低阈值的条件，永远无法触发更高资格的判定。
3. 非聚合字段分组违规：在MySQL默认的ONLY_FULL_GROUP_BY模式下，SELECT中的非聚合字段必须出现在GROUP BY中，当前查询的Date、Aircraft等字段未在GROUP BY里，会导致取值随机甚至报错。

修正方案 #

方案1：按尾号计算累计时长（全局累计） #

如果需求是判断每个尾号的总飞行时长是否达标，用子查询先计算累计值，再关联原表：

SELECT 
    fl.FlightNumber AS 'Flight Number',
    fl.Date,
    fl.Aircraft,
    fl.Aircraft_Manufacturer AS 'Aircraft Manufacturer',
    fl.Tail_Number AS 'Tail Number',
    fl.Departure,
    fl.Arrival,
    fl.FlightTime AS 'Flight Time',
    fl.Instructor,
    fl.Passengers,
    CASE
        WHEN ft.TotalFlightTime >= 32 AND fl.Tail_Number LIKE '24-%' THEN "RAAus RPC Cross Country Endorsment"
        WHEN ft.TotalFlightTime >= 25 AND fl.Tail_Number LIKE '24-%' THEN "RAAus RPC Passenger Endorsment"
        WHEN ft.TotalFlightTime >= 20 AND fl.Tail_Number LIKE '24-%' THEN "RAAus Recreational Pilots Certificate (RPC)"
        WHEN ft.TotalFlightTime >= 7 AND fl.Tail_Number LIKE 'VH-%' THEN "RPC Conversion to CASA Recreational Pilot License (RPL)"
        ELSE 'Not Eligible For License'
    END AS "License Eligibility"
FROM Flight_Log fl
JOIN (
    SELECT Tail_Number, SUM(FlightTime) AS TotalFlightTime
    FROM Flight_Log
    GROUP BY Tail_Number
) ft ON fl.Tail_Number = ft.Tail_Number
ORDER BY fl.FlightNumber;

方案2：按航班顺序计算累计时长（逐步累计） #

如果需求是判断截至当前航班时，该尾号的累计时长是否达标，用窗口函数实现：

SELECT 
    FlightNumber AS 'Flight Number',
    Date,
    Aircraft,
    Aircraft_Manufacturer AS 'Aircraft Manufacturer',
    Tail_Number AS 'Tail Number',
    Departure,
    Arrival,
    FlightTime AS 'Flight Time',
    Instructor,
    Passengers,
    CASE
        WHEN SUM(FlightTime) OVER (PARTITION BY Tail_Number ORDER BY Date, FlightNumber) >= 32 AND Tail_Number LIKE '24-%' THEN "RAAus RPC Cross Country Endorsment"
        WHEN SUM(FlightTime) OVER (PARTITION BY Tail_Number ORDER BY Date, FlightNumber) >= 25 AND Tail_Number LIKE '24-%' THEN "RAAus RPC Passenger Endorsment"
        WHEN SUM(FlightTime) OVER (PARTITION BY Tail_Number ORDER BY Date, FlightNumber) >= 20 AND Tail_Number LIKE '24-%' THEN "RAAus Recreational Pilots Certificate (RPC)"
        WHEN SUM(FlightTime) OVER (PARTITION BY Tail_Number ORDER BY Date, FlightNumber) >= 7 AND Tail_Number LIKE 'VH-%' THEN "RPC Conversion to CASA Recreational Pilot License (RPL)"
        ELSE 'Not Eligible For License'
    END AS "License Eligibility"
FROM Flight_Log
ORDER BY FlightNumber;

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