使用窗口函数dense_rank，如下所示：
首先使用cte根据fileID按等级排列名称。使用cte2（具有最大等级）从每个名称中获取最新文件。
然后，我们连接两个CTE以产生所需的结果：

with cte as (
  select *, dense_rank() over (partition by name order by fileID) rn,
           count(name) over (partition by name) countAll
  from tablex
),
cte2 as (
  select name, max(rn) max_rn
  from cte
  group by name
  having max(rn) > 1
)
select c.name, max(c.countAll) as countAll, max(c.file) as latestFile, count(c.name) as countLatestFile, 
       max(c.countAll) - count(c.name) as countOthers
from cte c
inner join cte2 c2 on c.rn = c2.max_rn and c.name = c2.name
group by c.name

Demo here

您可以使用max window函数和条件聚合，如下所示：

with max_ids as
(
  select *,
    max(fileID) over (partition by name) maxid
  from tablex
)
select name,
       count(*) countAll,
       sum(fileID = maxid) countLatestFile,
       sum(fileID <> maxid) countOthers
from max_ids 
group by name
having count(distinct fileId)  > 1

See demo

不使用窗口函数也能做到的：

select name,
(select max(fileId) 
from tablex
where name=t.name) as max_fileid,
count(case when fileid= (select max(fileId) 
                        from tablex
                        where name=t.name)
            then 1
        end) as count_for_max_fileid,
count(case when fileid != (select max(fileId) 
                        from tablex
                        where name=t.name)
            then 1
        end)as count_for_other
from tablex t
where name in (select name from tablex group by name having count(distinct fileid)>1)
group by name
;

-- result set:
+------+------------+----------------------+-----------------+
| name | max_fileid | count_for_max_fileid | count_for_other |
+------+------------+----------------------+-----------------+
| AAA  |          2 |                    3 |               3 |
| BBB  |          3 |                    2 |               4 |
+------+------------+----------------------+-----------------+