在SQL中使用子查询查找max(count())

Question

我正在使用一个名为SQLfire的程序来编码，我不完全确定我们使用的是哪个版本，但我被告知它需要与SQL Server2008一起工作。

这是我想要做的：

select CustomerNum, max(count(CustomerNum))
from Rentals
group by CustomerNum

我知道如何正确实现max(count())的问题已经被回答了很多次，然而，我还没有找到任何与SQLfire一起工作的方法来解决这个问题。因此，我尝试使用一个相关子查询来解决这个问题，如下所示：

select CustomerNum, count(CustomerNum)
from Rentals R
group by CustomerNum
having count(CustomerNum) =
    (select max(CustomerNum)
    from Rentals
    having count(CustomerNum) = count(R.CustomerNum))

但我发现我完全不知道我在做什么。有没有办法使用基本命令和子查询来解决这个问题？

作为参考，我们仅使用表Rentals中的列CustomerNum (1000,1001,1002等)。我正在尝试查找表Rentals中CustomerNum出现次数最多的客户。我在考虑使用子查询首先计算每个customernum在表中出现的次数，然后找到计数最高的customernum。

Answer 1

您所做的事情不需要相关子查询。以下是基于您的查询的一种方法：

select CustomerNum, count(CustomerNum)
from Rentals R
group by CustomerNum
having count(CustomerNum) = (select max(cnt)
                             from (select CustomerNum, count(CustomerNum) as cnt
                                   from Rentals
                                   group by CustomerNum
                                  ) rc
                            );

我倾向于将子查询移至from子句，并使用子查询：

select rc.*
from (select CustomerNum, count(CustomerNum) as cnt
      from Rentals R
      group by CustomerNum
     ) rc join
     (select max(cnt) as maxcnt
      from (select CustomerNum, count(CustomerNum) as cnt
            from Rentals
            group by CustomerNum
           ) rc
     ) m
     on rc.cnt = m.maxcnt;

这些都是标准的SQL，应该可以在两个系统中运行。在实践中，我可能会找到在SQL Server2008上使用top或row_number()的方法。

Answer 2

select r.* 
from Rentals r
right join (select CustomerNum, Max(cnt) from (
   select CustomerNum, Count(CustomerNum) cnt from Rentals Group by CustomerNum) tt) t on r.CustomerNum = t.CustomerNum