Postgresql 如何计算 count() 的最大值

Question

我试图找到一种计算最大计数(r.customer_id(的方法。运行脚本时，有 4 个具有相同最大值的值，所以我需要生成的是一个包含这 4 个值的表

SELECT     c1.customer_id, c1.first_name, c1.last_name, count(r.customer_id)
FROM       customer c1 
INNER JOIN rental r 
ON         (c1.customer_id = r.customer_id)  
WHERE      r.staff_id = 2 
GROUP      BY c1.customer_id, c1.first_name, c1.last_name
ORDER BY   4 desc

Answer 1

    SELECT customer_id, first_name, last_name, count 
      FROM (
      SELECT c1.customer_id as customer_id, c1.first_name as first_name, c1.last_name as last_name, count(r.customer_id) as count
      FROM customer c1 
      INNER JOIN rental r ON ( c1.customer_id = r.customer_id  )  
      WHERE r.staff_id = 2 
      GROUP BY c1.customer_id, c1.first_name, c1.last_name
    ) x 
    GROUP BY customer_id, first_name, last_name
    HAVING count = max(count)
    order by 4 desc