mysql - 如何只显示出现次数第二多的值？

Question

编写一个查询来显示访问次数第二多的客户名称

select customer_id,count(*)  from booking group by customer_id ; 

使用此查询，我得到了每个客户的访问次数，如下所示

CUSTOMER_ID,COUNT(*)
C001,6
C002,1
C003,1
C004,1
C005,4

但我只想显示 c005，因为他访问了第二最大时间

Answer 1

SELECT customer_id, COUNT(*)
FROM booking
GROUP BY customer_id
HAVING COUNT(*) <> (SELECT MAX(t.custCount)
                    FROM (SELECT COUNT(*) AS custCount
                          FROM booking
                          GROUP BY customer_id) t )
ORDER BY COUNT(*) DESC
LIMIT 1

顺便说一句，如果第二名并列，则此方法不起作用。在这种情况下，您可以使用上述查询作为 WHERE 子句中的条件，例如

SELECT customer_id
FROM booking
GROUP BY customer_id
HAVING COUNT(*) = (query given above)