如何在postgresql中按自定义计数排序？

Question

我有两张表：

公司：(id，名称)

工人：(id，姓名，国家)

我想要获得所有公司，并根据来自给定国家的员工数量对它们进行排序。

对于查找拥有更多来自美国的员工的公司的查询，结果应该是：

#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;

但这是将所有工人计算在内，而不考虑他们的国家。

有什么想法吗？

Answer 1

使用相关子查询可以很容易地做到这一点：

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

Answer 2

用COUNT(*)替换COUNT(w.country='USA')有帮助吗？我相信您的查询需要引用Companies表中的字段，而不是workers表上的字段，因为您的left join保留了为特定公司/国家拉出的workers集是空集的可能性。

尝试：

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