找到表中的最大值，然后分别显示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

所以，这是我的表 SERVICE，其中（如我们所见）最大经验是 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 表达式”

我的逻辑是，首先我将其分为几组，然后使用having子句来计算每组中经验等于最大值的元组。经验。

Answer 1

一种方法是使用带有子查询的左连接，它仅返回最大值。需要使用 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 均为空。简单来说，您只需要计算组中值为 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

Answer 2

使用求和：

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

参见小提琴。