在SQL中按COUNT()排序 [英] Ordering by COUNT() in SQL

问题描述

假设我有一个这样的数据库表:

Let's say I have a database table like this:

users
------
id
email
referrerID

我该如何按推荐人数最多的成员进行排序?我正在尝试以下方法:

How could I sort by the members with the most referrals? I was trying something along the lines of:

SELECT id,email FROM users WHERE 1 ORDER BY COUNT(referrerID) DESC;

但这似乎不起作用.怎么了?

But this does not seem to work. What is wrong?

我认为默认值0也可能会对此产生影响?

I think that the default value 0 may also be affecting this somehow?

推荐答案

以下澄清

SELECT referrerID,
       COUNT(id) as Num
FROM   users
GROUP  BY referrerID
ORDER  BY CASE
            WHEN referrerID = 0 THEN -1
            ELSE COUNT(id)
          END DESC;  

这篇关于在SQL中按COUNT()排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！