如何使用Postgresql按自定义计数排序? [英] How to sort by custom count with postgresql?

问题描述

我有两个表:

公司:(身份证，姓名)

Companies: (id, name)

工人:(身份证，姓名，国家/地区)

Workers: (id, name, country)

我想得到所有公司，并按给定国家/地区的员工人数对它们进行排序.

I would like to get all companies and sort them by numbers of employees from a given country.

对于要查询有来自美国的更多工人的公司的查询，结果应为:

For a query looking for companies that have more workers from the USA, the result should give:

#workers from USA | company id | company name 
----------------------------------------------
        90              6           foo corp     
        45              9           bar corp     
        0               3         foobar corp    

我尝试过:

select 
    count(w.country='USA') as mycount,
    w.company_id,
    c.company_name
from 
    companies c 
    left join workers w on 
        c.id=w.company_id 
group by 
    w.company_id,
    c.company_name
order by mycount desc;

但这是所有国家(地区)的工人的总数.

But that's counting all workers regardless of their countries.

有什么想法吗?

推荐答案

您可以使用相关的子查询轻松地做到这一点:

You can do that easily with a correlated subquery:

SELECT
  (SELECT count(*) FROM workers w WHERE w.company_id=c.id AND w.country='USA') AS mycount,
  c.id,
  c.company_name
FROM
  companies c
ORDER BY mycount DESC

这篇关于如何使用Postgresql按自定义计数排序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！