postgresql 如何使用postgresql按计数排序？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1402682/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to sort by count with postgresql?
提问by Ggolo
I have two tables:
我有两个表：
Companies: (id, name, city)
Workers: (id, name)
I would like to get all companies and sort them by numbers of employes.
我想获取所有公司并按员工人数对它们进行排序。
The result should give:
结果应该给出：
count | company id | company name | city
------------------------------------------
90 6 foo corp NY
45 9 bar corp LA
0 3 foobar corp HO
I tried:
我试过：
select
c.*,
count(w.id) as c
from
companies c
left join
workers w
on
c.id = w.company_id
group by
c.id
order by
c desc;
But that's not working as it tells me to group by g.name too :/
但这不起作用，因为它告诉我也按 g.name 分组：/
Any ideas?
有任何想法吗？
回答by Eric
You've aliased the table and column as the same thing, so don't do that. It's not invalid, just tough to follow.
您已将表和列别名为同一事物，所以不要这样做。这不是无效的，只是很难遵循。
Anyway, include all columns that you're selecting that aren't aggregates in your group by:
无论如何，将您选择的所有列都包含在您的group by:
select
count(w.id) as mycount,
w.company_id,
c.company_name,
c.city
from
companies c
left join workers w on
c.id=w.company_id
group by
w.company_id,
c.company_name,
c.city
order by mycount desc;
回答by EarthMind
If you don't want the count result to be return (because of an ORM framework or so) you could apply it directly in the order by clause:
如果您不希望返回计数结果（由于 ORM 框架左右），您可以直接在 order by 子句中应用它：
select
c.*
from
companies c
left join
workers w
on
c.id = w.company_id
group by
c.id
order by
count(w.id) desc;
Tested in postgreSQL 11
在 postgreSQL 11 中测试
回答by David Andres
Try this as a subquery:
试试这个作为子查询：
SELECT C.*
FROM
(
SELECT C.Id, C.Company_Name, C.City, COUNT(W.Id) AS CNT
FROM Companies C
LEFT JOIN Workers W ON W.Company_Id = C.Id
GROUP BY C.Id, C.Company_Name, C.City
) T
ORDER BY T.CNT
