SQL分组查询报错排查及优化、参数化等技术问题咨询

一、为什么出现GROUP BY报错？ #

你遇到的错误核心是SQL的分组语法规则：当使用GROUP BY进行分组时，SELECT列表里的列要么必须出现在GROUP BY子句中，要么被聚合函数（比如COUNT、SUM）包裹。你的原查询里SELECT Users.*包含了Users.Name，但GROUP BY只指定了Users.Id——虽然Id是主键，每个Id对应的Name必然唯一，但数据库会严格遵循语法规则，不会自动推断这层关联，所以会报错。

解决这个问题的直接办法就是把SELECT中用到的非聚合列都加到GROUP BY里，就像你后来测试的GROUP BY u.Id, u.Name那样。

二、更高效的查询语句？ #

有几种更高效的写法，适配不同场景：

1. EXISTS子查询（适合小批量技能筛选） #

这种写法避免了分组聚合，数据库可以提前终止匹配，在UserSkills表有(UserId, SkillId)索引的情况下，性能非常好：

SELECT u.*
FROM @Users u
WHERE EXISTS (
    SELECT 1 FROM @UserSkills us 
    WHERE us.UserId = u.Id AND us.SkillId = 149
)
AND EXISTS (
    SELECT 1 FROM @UserSkills us 
    WHERE us.UserId = u.Id AND us.SkillId = 305
)

2. 带DISTINCT的分组（防止重复技能记录） #

如果UserSkills可能存在同一用户同一技能的重复记录，用COUNT(DISTINCT SkillId)更严谨：

SELECT u.Id, u.Name
FROM @Users u
JOIN @UserSkills us ON u.Id = us.UserId
WHERE us.SkillId IN (149, 305)
GROUP BY u.Id, u.Name
HAVING COUNT(DISTINCT us.SkillId) = 2

3. 窗口函数筛选（适合复杂关联场景） #

如果还要关联其他表，窗口函数可以减少分组操作的开销：

SELECT DISTINCT u.*
FROM @Users u
JOIN (
    SELECT us.UserId, 
           COUNT(*) OVER (PARTITION BY us.UserId) AS SkillMatchCount
    FROM @UserSkills us
    WHERE us.SkillId IN (149, 305)
) us_filtered ON u.Id = us_filtered.UserId
WHERE us_filtered.SkillMatchCount = 2

三、如何将SkillId集合作为参数传入？ #

在SQL Server中，推荐用表值参数或临时表传递技能集合，动态获取集合数量，不用写固定数字：

方法1：表值参数（适合存储过程场景） #

先创建一个表值类型：

CREATE TYPE SkillList AS TABLE (SkillId INT);

然后在查询中使用：

DECLARE @Skills SkillList;
INSERT INTO @Skills VALUES (149), (305);

SELECT u.Id, u.Name
FROM @Users u
JOIN @UserSkills us ON u.Id = us.UserId
JOIN @Skills s ON us.SkillId = s.SkillId
GROUP BY u.Id, u.Name
HAVING COUNT(*) = (SELECT COUNT(*) FROM @Skills);

方法2：临时表（适合临时查询） #

CREATE TABLE #TempSkills (SkillId INT PRIMARY KEY);
INSERT INTO #TempSkills VALUES (149), (305);

SELECT u.*
FROM @Users u
JOIN @UserSkills us ON u.Id = us.UserId
JOIN #TempSkills s ON us.SkillId = s.SkillId
GROUP BY u.Id, u.Name
HAVING COUNT(*) = (SELECT COUNT(*) FROM #TempSkills);

DROP TABLE #TempSkills;

方法3：字符串拆分（SQL Server 2016+） #

如果参数是逗号分隔的字符串（比如'149,305'），可以用STRING_SPLIT处理：

DECLARE @SkillIds NVARCHAR(MAX) = '149,305';

SELECT u.Id, u.Name
FROM @Users u
JOIN @UserSkills us ON u.Id = us.UserId
JOIN STRING_SPLIT(@SkillIds, ',') s ON us.SkillId = CAST(s.value AS INT)
GROUP BY u.Id, u.Name
HAVING COUNT(*) = (SELECT COUNT(*) FROM STRING_SPLIT(@SkillIds, ','));

四、User表有40列，如何避免枚举所有列到GROUP BY？ #

最实用的办法是先筛选符合条件的UserId，再关联User表取所有列：

DECLARE @Skills SkillList;
INSERT INTO @Skills VALUES (149), (305);

SELECT u.*
FROM @Users u
WHERE u.Id IN (
    SELECT us.UserId
    FROM @UserSkills us
    JOIN @Skills s ON us.SkillId = s.SkillId
    GROUP BY us.UserId
    HAVING COUNT(*) = (SELECT COUNT(*) FROM @Skills)
);

这个写法的优势是：子查询只处理UserSkills和参数表，返回符合条件的UserId列表，再关联User表直接取所有列，完全不用对User表的列进行分组，完美适配列多的场景。

另外还有一种取巧的办法（不推荐）：因为Id是主键，每个Id对应的其他列值唯一，所以可以用MAX()或MIN()包裹所有非分组列，这样不用加到GROUP BY里，但这种写法繁琐且对部分数据类型不友好，所以还是优先推荐子查询的方式。

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