如何返回按 COUNT(*) 降序列出的行? [英] How to return rows listed in descending order of COUNT(*)?

问题描述

我有一个名为 foo 的表，其中包含以下字段:

I have a table called foo with these fields:

- id

- type

- parentId

我想选择父 IDS 的列表，按照它们在表中出现的次数的 COUNT(*) 降序排列.像这样:

I want to select a list of parent IDS, in the descending order of their COUNT(*) of how many times they appear in the table. Something like this:

SELECT DISTINCT parentId FROM `foo` 
ORDER BY (COUNT(parentId) DESC where parentId = parentId)

如何以最有效的方式完成这项工作，同时将服务器的负载降至最低?

How can this be done in the most efficient way and putting the least load on the server?

表中可能有成千上万条记录，因此手动遍历每条记录是不可接受的..

There can be thousands-hundreds of thousands of records in the table, so manually going through each record is not acceptable..

推荐答案

只需应用 GROUP BY 子句，并假设您有一个索引，FOREIGN KEY，或PRIMARY KEY on parentId，性能应该还不错.(parentId 看起来很可能是一个 FORIGN KEY，所以一定要定义约束来强制索引).

Simply by applying a GROUP BY clause, and assuming you have an index , FOREIGN KEY, or PRIMARY KEY on parentId, the performance should be quite good. (parentId looks like it is likely a FORIEGN KEY, so be sure to define the constraint to enforce indexing).

SELECT `parentId`
FROM `foo`
GROUP BY `parentId`
ORDER BY COUNT(*) DESC

这篇关于如何返回按 COUNT(*) 降序列出的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！