SQL Server：不使用TOP语句获取患者最多的医生及患者数

错误原因 #

你写的外层查询里，DoctorLicenceNo既没有被包含在聚合函数中，也没有出现在GROUP BY子句里，数据库无法确定这个字段和MAX(c)的对应关系，所以抛出了报错信息。

解决方案1：使用窗口函数（推荐） #

用RANK()或ROW_NUMBER()窗口函数给每个医生的患者数排名，再筛选出排名第一的记录。如果有多个医生患者数相同且都是最多的，RANK()会返回所有符合条件的医生；ROW_NUMBER()只会返回其中一个，可根据实际需求选择：

SELECT DoctorLicenceNo, PatientCount
FROM (
    SELECT 
        DoctorLicenceNo, 
        COUNT(SSN) AS PatientCount,
        RANK() OVER(ORDER BY COUNT(SSN) DESC) AS RankNum
    FROM PATIENT
    GROUP BY DoctorLicenceNo
) AS TempTable
WHERE RankNum = 1;

解决方案2：子查询匹配最大患者数 #

先通过子查询统计所有医生的患者数，再找出其中的最大值，最后匹配出患者数等于这个最大值的医生：

SELECT DoctorLicenceNo, PatientCount
FROM (
    SELECT DoctorLicenceNo, COUNT(SSN) AS PatientCount
    FROM PATIENT
    GROUP BY DoctorLicenceNo
) AS DoctorPatientCounts
WHERE PatientCount = (
    SELECT MAX(PatientCount)
    FROM (
        SELECT COUNT(SSN) AS PatientCount
        FROM PATIENT
        GROUP BY DoctorLicenceNo
    ) AS MaxCounts
);

内容的提问来源于stack exchange，提问作者Βασίλης Χατζηπαντελή