sql - 如何在 postgres 列中对顶级类别进行排名

Question

我需要找到一所学校的前 5 个民族。

Select count(distinct studentnr),
       count(case when ctf.text like 'United Kingdom' then 1 end) as nation1,
       count(case when ctf.text like 'Germany' then 1 end) as nation2,
       count(case when ctf.text like 'France' then 1 end) as nation3,
       count(case when ctf.text like 'Italy' then 1 end) as nation4,
       count(case when ctf.text like 'Hungary' then 1 end) as nation5

from student s  
       join pupil p on p.id = s.personid
       join pupilnationality pn on pn.pupilid = p.id
       join country ctf on ctf.id = pn.countryid

如您所见，这是手动搜索，我希望查找字段并进行计数并将它们单独分类在一个列中。

但是我只想要前 5 名 这几乎是我想要的 这需要分区或排名吗？

标签： sqlpostgresqlpostgresql-9.3rank

Answer 1

为什么不将它们放在单独的行中？

select ctf.text, count(*)
from student s join
     pupil p
     on p.id = s.personid join
     pupilnationality pn
     on pn.pupilid = p.id join
     country ctf
     on ctf.id = pn.countryid
group by ctf.text
order by count(*) desc
limit 5;