如何返回以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子句，并假设您在parentId上具有索引，FOREIGN KEY或PRIMARY KEY，性能应为很好. (parentId看起来很可能是FORIEGN 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屋！