Salah satu cara ialah menggunakan gabungan kiri dengan subkueri, yang hanya mengembalikan nilai maksimum. Kes diperlukan untuk mengembalikan kumpulan dengan nilai maksimum yang sewenang-wenangnya.

SELECT s.position,
       sum(case when max_experience is null then 0 else 1 end ) as max_count
FROM service s
LEFT JOIN  ( select max(experience) as max_experience
             from service 
            ) as s1 ON  s.experience = s1.max_experience
group by s.position
order by max_count desc ;

https://dbfiddle.uk/-8pHZ8wm

Untuk memudahkan pemahaman, jalankan pertanyaan di bawah dan anda akan mendapati bahawa max_experience kosong dalam setiap baris dalam jadual perkhidmatan kecuali nilai 100. Ringkasnya, anda hanya perlu mengira baris dalam kumpulan dengan nilai 100 dan 0 yang belum mencapai nilai pengalaman maksimum.

SELECT s.*,s1.*
FROM service s
LEFT JOIN  (select max(experience) as max_experience
             from service 
            ) as s1 ON  s.experience = s1.max_experience ;

https://dbfiddle.uk/al8YYLk9

Edit. Jawapannya juga berfungsi dalam Oracle, tetapi kata kunci selepas subquery perlu dialih keluar as

SELECT s.position,
       sum(case when max_experience is null then 0 else 1 end ) as max_count
FROM service s
LEFT JOIN  ( select max(experience) as max_experience
             from service 
            )  s1 ON  s.experience = s1.max_experience
group by s.position
order by max_count desc ;

https://dbfiddle.uk/hhGB_xXx

Gunakan 求和:

select position, sum(experience = 100) from tbl group by position

Lihat biola.