如何选择具有最大值的行是最佳方法？

Question

我遇到了一个任务，我设法完成了目标，但是我得到的解决方案并不是最优的，我需要更优秀的解决方案。我已经使用了普通子查询，也许相关子查询可以更好地解决这个问题。

这是我创建的表：

SELECT custid, 
       count(DISTINCT bid) AS Total 
FROM   loan 
GROUP  BY custid; 

这个的输出结果如下所示： 我想要的是具有最大总数的 custid。

一种方法是使用 Order by Total DESC LIMIT 1，但这将只给出 1 个结果。

我的做法是：

SELECT custid 
FROM   (SELECT custid, 
               count(DISTINCT bid) AS Total 
        FROM   loan 
        GROUP  BY custid) c1 
WHERE  total = (SELECT max(Total) 
                FROM   (SELECT custid, 
                               count(DISTINCT bid) AS Total 
                        FROM   loan 
                        GROUP  BY custid) c2) 

这给了我正确的结果，即

我想做的是减少代码，因为我在这里再次写同样的内容。我知道一定有更简单的方法来做到这一点。也许是相关查询。

期待一些好的答案，这只是为了澄清我的概念。

如果问题太初级，对不起。我是SQL的新手。

- user3542348

Answer 1

在理解了 OP 想要的内容后，结合 @Ravinder 的提示，我猜测你需要内置的 mysql 函数 GROUP_CONCAT。相应的 SQL 语句为：

select custid_count.Total, GROUP_CONCAT(custid_count.custid order by custid_count.custid asc SEPARATOR ',') as custids from
(select custid, count(distinct bid) as Total from loan group by custid order by Total desc) as custid_count
group by custid_count.Total
order by custid_count.Total desc
limit 1;

结果列custids是由逗号连接的最大id，查询后，您需要通过逗号分隔custids，并将每个子字符串转换为所需的数字类型。

- Eric

---

不行。这会将结果限制为仅有1行。操作者期望有更多行具有相同的总值。 - Ravinder Reddy

没错。我不想使用LIMIT。它只限制了少量条目的使用 :| - user3542348

@user3542348 为什么不使用 limit 呢？如果能以正确且高效的方式解决问题。 - Eric

Answer 2

这里还有另一种方法：

select * from loan
where custid = 
(
    select custid_count.custid from
    (select custid, count(distinct bid) as Total from loan group by custid order by Total desc) as custid_count
    order by custid_count.Total desc
    limit 1

);

首先找到具有最大计数的custid，然后查询所有与该custid匹配的行，

- Eric

Answer 3

我在mysql中没有尝试过这个，但在我使用的sql语言中，使用聚合函数而不使用group by是可以的，就像这样

select custid, total, max(total) as maxtotal
from (select custid, count(distinct bid) as total
      from loan
      group by custid) c1;

在每一行上都会标记出个别客户的总计和整个表格的最大总计，您只需要筛选那些总计等于最大总计的行。这将为您提供类似以下的最终答案：

select custid
from (select custid, count(distinct bid) as total
from loan
group by custid) c1
where total = max(total);

- Dan