SQL中如何仅输出出现次数最多的记录，仅返回查询结果的首行？

你可以在现有SQL基础上增加结果行数限制的子句即可，不同数据库的限制语法略有区别：

• 如果是MySQL、PostgreSQL、SQLite类数据库，直接在语句末尾加LIMIT 1就可以只返回排序后的第一条结果：

SELECT  S.CUSTID, C.NAME, COUNT(S.CUSTID) as "Customer with most Purchases"
FROM CUSTOMER C, SALESTRANSACTION S
WHERE C.CUSTID = S.CUSTID
GROUP BY S.CUSTID, C.NAME, C.CUSTID 
ORDER BY COUNT(S.CUSTID) DESC
LIMIT 1;

• 如果是SQL Server数据库，使用TOP 1关键字放在SELECT后面：

SELECT TOP 1 S.CUSTID, C.NAME, COUNT(S.CUSTID) as "Customer with most Purchases"
FROM CUSTOMER C, SALESTRANSACTION S
WHERE C.CUSTID = S.CUSTID
GROUP BY S.CUSTID, C.NAME, C.CUSTID 
ORDER BY COUNT(S.CUSTID) DESC;

• 如果是Oracle 12c及以上版本，末尾加FETCH FIRST 1 ROW ONLY：

SELECT  S.CUSTID, C.NAME, COUNT(S.CUSTID) as "Customer with most Purchases"
FROM CUSTOMER C, SALESTRANSACTION S
WHERE C.CUSTID = S.CUSTID
GROUP BY S.CUSTID, C.NAME, C.CUSTID 
ORDER BY COUNT(S.CUSTID) DESC
FETCH FIRST 1 ROW ONLY;

注意：以上写法如果存在多个客户购买次数并列第一的情况，只会随机返回其中一个。如果你需要把所有并列第一的客户都返回，可以用窗口函数的方式实现，通用写法如下：

WITH cust_purchase_cnt AS (
    SELECT  
        S.CUSTID, 
        C.NAME, 
        COUNT(S.CUSTID) as purchase_cnt,
        RANK() OVER(ORDER BY COUNT(S.CUSTID) DESC) as rk
    FROM CUSTOMER C
    JOIN SALESTRANSACTION S ON C.CUSTID = S.CUSTID
    GROUP BY S.CUSTID, C.NAME, C.CUSTID 
)
SELECT CUSTID, NAME, purchase_cnt as "Customer with most Purchases"
FROM cust_purchase_cnt
WHERE rk = 1;

内容的提问来源于stack exchange，提问作者Ferry Rarri