在SQL中使用子查询来查找max(count()) [英] Using sub-queries in SQL to find max(count())

问题描述

我正在使用一个名为SQLfire的程序进行编码，但我不完全确定我们使用的是哪个版本，但是我被告知它需要与SQL Server 2008一起使用.

I am using a program called SQLfire to code and I'm not entirely sure what version we are using but I'm told it needs to work with SQL Server 2008.

这就是我想要做的:

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

我知道如何正确实现max(count())的问题已经被多次回答，但是，我还没有找到解决SQLfire的方法.因此，我尝试使用如下相关子查询来解决该问题:

I am aware that the question of how to properly implement max(count()) has already been answered multiple times, however, I have not found any way to solve it that works with SQLfire. So, I tried solving it using a correlated sub-query like so:

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

但是我发现我完全不知道自己在做什么.有没有办法使用基本命令和子查询来解决此问题?

but I found out that I have absolutely no idea what I'm doing. Is there a way to solve this problem using basic commands and sub-queries?

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

For reference, we are only using the column CustomerNum (1000,1001,1002 etc) in table Rentals. I am trying to find the customer whose CustomerNum appears the most times in table Rentals. I am thinking around using sub-queries to first count the number of times each customernum appears in the table, then find the customernum with the highest count.

推荐答案

您不需要使用关联的子查询来执行操作.这是根据您的查询的一种方法:

You don't need a correlated subquery for what you are doing. Here is one way based on your query:

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子句并使用子查询:

I would be inclined to move the subquery to the from clause and use subqueries:

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 Server 2008上使用top或row_number()的方法.

These are standard SQL and should work in both systems. In practice, I'd probably find a way to use top or row_number() on SQL Server 2008.

这篇关于在SQL中使用子查询来查找max(count())的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！