SQL查询含最多项目的分组时substr函数报错问题排查

报错原因 #

• 你使用的SQL Fiddle对应环境为SQL Server，SQL Server未内置SUBSTR()函数，该函数是MySQL、Oracle等数据库的语法，SQL Server中对应子串截取函数为SUBSTRING()，这是你收到报错的直接原因。
• 除此之外你的SQL查询逻辑完全不符合需求：你当前的语法试图对groupid字符串做截取运算，但你的需求是统计每个分组包含的项目数量，再筛选出数量最多的分组，二者逻辑完全不匹配。

正确查询写法 #

仅取最多的1个分组（不处理并列） #

直接按groupid分组统计项目数，倒序排序后取第一条即可：

SELECT TOP 1 groupid, COUNT(itemid) AS item_count
FROM itemgroup
GROUP BY groupid
ORDER BY item_count DESC

结合你提供的测试数据，执行后返回结果为Group1，对应项目数量为4，符合预期。

兼容并列最多的情况 #

如果存在多个分组项目数相同且均为最大值的情况，使用窗口函数RANK()实现：

WITH group_stat AS (
    SELECT 
        groupid, 
        COUNT(itemid) AS item_count,
        RANK() OVER(ORDER BY COUNT(itemid) DESC) AS rank_num
    FROM itemgroup
    GROUP BY groupid
)
SELECT groupid, item_count
FROM group_stat
WHERE rank_num = 1

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