感谢@klin，因为在此之前我不知道
我最近在codility平台上进行了测试，被以下SQL问题卡住了。
问题：对于在线平台，对3个主题进行了评估，并提供了分数。
表：评估
输入：
|Id|experience|sql|algo|bug_fixing|
|1 |5 |100|null|100 |
|2 |1 |100|100 |null |
|3 |3 |100|null|50 |
|4 |5 |100|null|50 |
|5 |5 |100|100 |100 |
我们需要打印经验，拥有完美分数的学生人数(零被认为是完美分数(为最大值，拥有该年经验的学生人数为计数。结果将按经验年限降序打印。
输出：
|experience|max|counts|
|5 |2 |3 |
|3 |0 |1 |
|1 |1 |1 |
我的解决方案：
With t1 as
(select experience,
count(experience) as max
from assessments
where (sql=100 or sql=null)
and (algo=100 or algo=null)
and (bug_fixing=100 or bug_fixing=null)
group by experience)
select a.experience,
t1.max,
count(a.experience) as counts
from assessments a join t1 on a.experience=t1.experience
group by a.experience
然而，我在第二列(最大值(的输出中得到了不正确的计数。
有人能告诉我代码中的错误或需要更正吗？TIA。
您不需要子查询或with语句。使用带有filter选项的聚合，例如：
select
experience,
count(*) filter (where
coalesce(sql, 100)+
coalesce(algo, 100)+
coalesce(bug_fixing, 100) = 300
) as max,
count(*)
from assessments
group by experience
order by experience desc
在db<gt；不停摆弄
阅读文档中的更多信息。
答案：
With t1 as
(select experience,
count(experience) as max
from assessment
where (sql=100 or sql is null)
and (algo=100 or algo is null)
and (bug_fixing=100 or bug_fixing is null)
group by experience)
select a.experience,
coalesce(t1.max,0),
count(a.experience) as counts
from assessment a left join t1 on a.experience=t1.experience
group by a.experience,t1.max;
filter子句。以下是我的CASE声明解决方案：
select
experience,
sum(case when
coalesce(sql, 100)+
coalesce(algo, 100)+
coalesce(bug_fixing, 100) = 300 Then 1 Else 0 End
) as max,
count(exp) as count
from assessments
group by experience
order by experience desc