我这样做是为了一个统一的任务，任务是基本上得到所有的驾驶执照号码谁有相等的违法行为的最高数额为每个扣分代码。
所以有一个违规表，记过表，和司机表，查询应该返回一个列表，所有的驾驶执照已经获得了每个记过类别最多的罚单，如果有相等的最大值，返回所有的驾驶执照谁得到了该记过类别的最大值。
我使用了一个超级丑陋的嵌套子查询设置，如下所示：

select 
    sp.dem_code as "Demerit Code", 
    dem.dem_description as "Demerit Description", 
    sp2.lic_no as "License No.",
    d.lic_fname || ' ' || d.lic_lname as "Driver Fullname",
    sp."Total Times Booked"
from (
    select
        dem_code,
        max(o.num) as "Total Times Booked"
    from (
        select 
            dem_code, 
            count(lic_no) as num, 
            lic_no 
        from offence 
        group by dem_code, lic_no 
        order by dem_code asc
    ) o
    group by dem_code
    order by dem_code asc
) sp
join (
    select 
        dem_code, 
        count(lic_no) as num, 
        lic_no 
    from offence 
    group by dem_code, lic_no 
    order by dem_code asc
) sp2 on sp."Total Times Booked" = sp2.num and sp.dem_code = sp2.dem_code
join driver d on sp2.lic_no = d.lic_no
join demerit dem on dem.dem_code = sp2.dem_code
order by sp.dem_code asc, sp2.lic_no asc
;

但我不明白为什么会有这样的事情：

select
    dem_code,
    max(num),
    lic_no
from (
select dem_code, count(lic_no) as num, lic_no from offence group by dem_code, lic_no order by dem_code asc
)
group by dem_code
;

或

select
    dem_code,
    max(num),
    lic_no
from (
select dem_code, count(lic_no) as num, lic_no from offence group by dem_code, lic_no
)
group by dem_code
having num = max(num)
order by dem_code asc
;

不起作用？
它基本上就是要获取所有的许可证号，这些许可证号等于这组错误代码的最大值。