如何基于COUNT()子查询编写MAX()查询获取出行最多船只名称

要解决这个问题，我们需要分两步走：先统计每艘船的出行次数，再找出次数最大的那艘（或那些）船的名称。下面是两种可靠的写法，都满足你用MAX()函数的要求，同时避免ORDER BY DESC和TOP 1。

方法1：嵌套子查询 #

这种写法用多层子查询分别完成「统计次数」和「找最大次数」的逻辑，最后关联船只表获取名称：

SELECT B.Name
FROM Boat B
INNER JOIN (
    -- 第一步：统计每艘船的出行次数
    SELECT IdBoat, COUNT(IdTrip) AS NTrips
    FROM Trip
    GROUP BY IdBoat
) AS TripStats ON B.IdBoat = TripStats.IdBoat
WHERE TripStats.NTrips = (
    -- 第二步：找出所有船只中最大的出行次数
    SELECT MAX(NTrips)
    FROM (
        SELECT COUNT(IdTrip) AS NTrips
        FROM Trip
        GROUP BY IdBoat
    ) AS MaxTripCounts
);

逻辑拆解： #

1. 最内层的子查询：统计每艘船的出行次数，返回IdBoat和对应的NTrips。
2. 中间的子查询：从上面的统计结果中提取最大的NTrips值（你的示例数据里这个值是2）。
3. 外层查询：把Boat表和统计次数的子查询关联，筛选出出行次数等于最大值的船只名称。

方法2：使用CTE（更简洁易读） #

用公共表表达式（CTE）可以避免重复编写统计逻辑，让代码更清晰：

WITH TripStats AS (
    -- 一次性统计每艘船的ID、名称和出行次数
    SELECT B.IdBoat, B.Name, COUNT(T.IdTrip) AS NTrips
    FROM Boat B
    INNER JOIN Trip T ON B.IdBoat = T.IdBoat
    GROUP BY B.IdBoat, B.Name
)
-- 从统计结果中筛选出行次数等于最大值的船只
SELECT Name
FROM TripStats
WHERE NTrips = (SELECT MAX(NTrips) FROM TripStats);

为什么你的之前尝试出错？ #

• 直接写WHERE B.IdBoat = MAX(上述子查询)逻辑错误：MAX()是聚合函数，你需要先提取出最大的次数值，再用这个值去匹配统计结果中的次数，而不是直接把船ID和MAX的结果比较。
• 计数错误的问题：大概率是分组逻辑有误，比如没有正确按Boat.IdBoat分组，或者关联时引入了重复行。上面的两种写法都严格按船ID分组，能保证计数准确（你的示例数据里会正确返回SS Sparrow的次数为2）。

用你的示例数据测试，两种写法都会正确返回'SS Sparrow'，符合预期。

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