一个简单的方法是 RANK :

WITH cte AS (
    SELECT
        v.veh_manufname AS "Manufacturer Name",
        COUNT(o.off_no) AS "Total No. of Offences",
        RANK() OVER (ORDER BY COUNT(o.off_no) DESC) rnk
    FROM VEHICLE v
    INNER JOIN OFFENCE o ON v.veh_vin = o.veh_vin
    GROUP BY v.veh_manufname
)

SELECT "Manufacturer Name", "Total No. of Offences"
FROM cte
WHERE rnk = 1;

我们还可以使用子查询来仅限制具有最大违规次数的车辆：

SELECT "Manufacturer Name", "Total No. of Offences"
FROM cte
WHERE "Total No. of Offences" = (SELECT MAX("Total No. of Offences") FROM cte);

如果希望直接修改当前查询，而不使用cte，可以在 HAVING 条款：

SELECT
    v.veh_manufname AS "Manufacturer Name",
    COUNT(o.off_no) AS "Total No. of Offences"
FROM VEHICLE v
INNER JOIN OFFENCE o ON v.veh_vin = o.veh_vin
GROUP BY v.veh_manufname
HAVING COUNT(o.off_no) = (SELECT MAX(max_off)
                          FROM (SELECT COUNT(o.off_no) AS max_off
                                FROM VEHICLE v
                                INNER JOIN OFFENCE o ON v.veh_vin = o.veh_vin
                                GROUP BY v.veh_manufname) t);

有一种不用子查询的方法可以做到这一点，但有一个问题：
如果有两个或多个制造商的违规次数相同，会怎样？
这个 RANK() 函数是有用的，或者您可以使用 ROW_NUMBER() 在子查询中：

x.offences
From            (SELECT         v.veh_manufname,
                                COUNT(o.off_no) OVER (PARTITION BY v.veh_manufname) AS offences,
                                ROW_NUMBER() OVER (PARTITION BY v.veh_manufname ORDER BY o.off_no DESC) AS n
                From            VEHICLE v (NOLOCK)
                Left Outer Join OFFENCE o (NOLOCK) On o.veh_vin = v.veh_vin
                ) AS x
Order By        x.offences DESC,
                x.n DESC

这将给出一个结果，其中将显示违规次数最多和最近违规次数最多的制造商。