SQL查询PERSONAL SERVICES SETTINGS类发证最大值时LIMIT 1不生效

核心问题 #

原有语句无法正常生效的根源是语法不兼容：
当前查询使用的[dbo].[表名$]格式是典型的SQL Server对象命名规则，而末尾的LIMIT 1是MySQL、PostgreSQL等数据库的限定取数语法，SQL Server原生不识别该关键字，直接执行会抛出语法错误。

可落地的修正写法 #

写法1：最小改动匹配原有逻辑 #

直接移除LIMIT 1，在SELECT关键字后添加SQL Server原生支持的TOP 1，完全匹配原本“按发放计数降序取第一条”的思路：

SELECT TOP 1 
    YEAR(BL.[Issued]) AS Year, 
    BL.[Category] as Category, 
    COUNT(BL.[Licence No#]) as LicenceIssued 
FROM [dbo].[businesslicences$] BL 
WHERE BL.[Category] = 'PERSONAL SERVICES SETTINGS' 
  AND YEAR(BL.[Issued]) > 2015
GROUP BY 
    BL.[Category], 
    YEAR(BL.[Issued])
ORDER BY 
    COUNT(BL.[Licence No#]) DESC 

注意：该写法在遇到多个年份许可证发放数量完全相等的并列第一名时，只会返回随机1条记录。

写法2：严谨覆盖并列最高值场景 #

如果需要把所有计数等于最大值的并列记录全部返回，避免漏统，可搭配窗口函数排名实现：

WITH YearlyLicenceCount AS (
    SELECT 
        YEAR(BL.[Issued]) AS Year, 
        BL.[Category] as Category, 
        COUNT(BL.[Licence No#]) as LicenceIssued,
        -- 按发放数量降序排名，同值并列同名次
        RANK() OVER(ORDER BY COUNT(BL.[Licence No#]) DESC) AS rank_pos
    FROM [dbo].[businesslicences$] BL 
    WHERE BL.[Category] = 'PERSONAL SERVICES SETTINGS' 
      AND YEAR(BL.[Issued]) > 2015
    GROUP BY 
        BL.[Category], 
        YEAR(BL.[Issued])
)
SELECT Year, Category, LicenceIssued
FROM YearlyLicenceCount
WHERE rank_pos = 1

两种写法的适用差异：

• TOP 1写法改动量最小，仅需调整原有查询的关键字位置，适合不需要处理并列场景的简单需求
• 窗口函数写法统计逻辑更严谨，能完整返回所有和最高计数持平的记录，不会因为排序顺序问题出现漏数

内容的提问来源于stack exchange，提问作者Ankit Sharma