在表中查找最大值，然后分别显示SQL组和每个SQL组中的最大值计数

Question

ID  POSITION    EXPERIENCE  SALARY
1   top               90    1500
2   bottom           100    1500
3   top               90    750
4   left              90    1000
5   right            100    1300
6   top               90    1500
7   left              80    2000
8   top               80    1000
9   bottom           100    2000
10  left             100    2000

所以，这是我的服务表，(正如我们所看到的(最大体验是100。我需要写一个查询，通过位置(左、右、上、下(找到每个组中经验中出现100次的次数。

所以我写道：-

select position,count(*)
from service
group by position
having experience=(select max(experience) from service);

预期输出：-

POSITION  COUNT(*)
bottom         2 
left           1
right          1
top            0

但是，它给了我一个错误：；而不是GROUP BY表达式"；

我的逻辑是，首先我把它分成几组，然后使用have子句，我计算每组中经验等于最大经验的元组。

Answer 1

使用sum:

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

见小提琴。

Answer 2

一种方法是将左联接与只返回最大值的子查询一起使用。需要使用case来返回具有任何最大值的组。

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

为了更容易理解，运行下面的查询，您会发现除了值100之外，服务表的每一行中的max_experience都为null。简单地说，对于没有最大体验值的组，只需要计算值为100和0的行。

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

编辑。答案在Oracle中也有效，但子查询后的关键字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