MySQL中如何统计supertable关联的车主及附属司机行程数并筛选

原SQL错误原因 #

• 聚合字段别名OwnerRides、DepRides无法在JOIN的ON条件中使用，SQL执行顺序中JOIN操作早于聚合计算，此时别名尚未生成。
• ride表关联逻辑错误，idDriver字段需要和owner、dependant的ID字段关联，不能直接使用聚合结果作为关联条件。
• GROUP BY字段不符合SQL规范，SELECT子句中未经过聚合的字段需要全部放入GROUP BY列表中，开启ONLY_FULL_GROUP_BY模式时会直接触发报错。

实现方案 #

首先先统计所有司机的行程数，再关联owner、dependant表做身份匹配，最后按条件筛选，具体SQL如下：

场景1：筛选自身行程数大于所有从属dependant的总行程数

SELECT 
    ow.idOwner, 
    COALESCE(owner_stat.ride_count, 0) AS owner_ride_count,
    COALESCE(SUM(dep_stat.ride_count), 0) AS total_dependant_ride_count
FROM owners ow
-- 关联owner自身的行程统计数据
LEFT JOIN (
    SELECT idDriver, COUNT(idRide) AS ride_count
    FROM ride
    GROUP BY idDriver
) owner_stat ON ow.idOwner = owner_stat.idDriver
-- 关联owner对应的从属dependant
LEFT JOIN dependants dp ON ow.idOwner = dp.idOwner
-- 关联每个dependant的行程统计数据
LEFT JOIN (
    SELECT idDriver, COUNT(idRide) AS ride_count
    FROM ride
    GROUP BY idDriver
) dep_stat ON dp.idDependant = dep_stat.idDriver
GROUP BY ow.idOwner, owner_ride_count
-- 筛选符合条件的owner
HAVING owner_ride_count > total_dependant_ride_count;

场景2：筛选自身行程数大于每一个从属dependant的单独行程数

仅需要调整上述SQL的HAVING条件即可：

HAVING owner_ride_count > MAX(COALESCE(dep_stat.ride_count, 0))

逻辑说明 #

• 子查询统一统计所有司机的行程数，避免重复计算，同时兼容owner和dependant两种司机身份的统计需求。
• 使用LEFT JOIN保证无行程的owner、dependant也能被纳入统计范围，避免数据遗漏。
• COALESCE函数将空值转换为0，避免空值参与计算时出现逻辑错误。
• 用HAVING子句筛选聚合后的结果，符合SQL语法规范。

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