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子句来计算每组中经验等于最大值的元组。经验。
一种方法是使用带有子查询的左连接，它仅返回最大值。需要使用 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 中也有效，但需要删除子查询后面的关键字
asSELECT 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
使用
求和：参见小提琴。