Suchen Sie den Maximalwert in der Tabelle und zeigen Sie dann die SQL-Gruppen separat sowie die Anzahl der Maximalwerte in jeder SQL-Gruppe an

Question

IDPOSITIONEXPERIENCESALARY1top9015002bottom10015003top907504left9010005right10013006top9015007left8020008top8010009bottom100200010left1002000 Das ist also meine Tabelle SERVICE, bei der (wie wir sehen können) die maximale Erfahrung 100 beträgt. Ich muss eine Abfrage schreiben, um nach Position zu suchen (links, rechts, oben, unten).

P粉718730956 · Answer

一种方法是使用带有子查询的左连接，它仅返回最大值。需要使用 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

P粉936568533 · Answer

使用求和：

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

参见小提琴。