连接两表分组后获取多列的最优SQL查询方案咨询

为什么"仅按主键分组却无法直接查询其他字段"？ #

这是因为标准SQL的语法规则要求：SELECT子句中出现的非聚合函数列，必须全部出现在GROUP BY子句中，或者与GROUP BY中的列存在明确的函数依赖关系（比如主键决定其他列）。

虽然User.Id是主键，每个Id对应的Name唯一，但不是所有数据库默认都会自动识别这种依赖关系（比如SQL Server默认遵循ANSI SQL标准，不会做自动推断）。只有部分数据库（如MySQL关闭ONLY_FULL_GROUP_BY模式时）或特定版本（如SQL Server 2017+开启SET COMPATIBILITY_LEVEL = 140及以上，支持GROUP BY扩展）允许这种写法，但为了兼容性和代码可读性，不建议依赖这类非标准特性。

方案A vs 方案B：哪个更合适？ #

方案A（GROUP BY包含多列） #

SELECT u.Id, u.[Name], SUM(p.Quantity) as Quantity
FROM dbo.[User] u
LEFT JOIN dbo.Purchases p ON p.UserId = u.Id
GROUP BY u.Id, u.[Name]

• 优势：完全符合标准SQL，可读性极强，一眼就能明确分组逻辑；数据库优化器会自动识别Id是主键、Name依赖于Id，不会产生额外的分组开销，性能和方案B几乎无差异。
• 劣势：看似多写了一个分组列，但这是标准要求，不影响实际执行效率。

方案B（用聚合函数包裹非分组列） #

SELECT u.Id, MAX(u.[Name]), SUM(p.Quantity) as Quantity
FROM dbo.[User] u
LEFT JOIN dbo.Purchases p ON p.UserId = u.Id
GROUP BY u.Id

• 优势：满足语法要求，结果正确（因为每个Id对应唯一的Name，MAX/MIN/ANY_VALUE都会返回正确值）。
• 劣势：可读性差，其他开发者看到时会疑惑为何对Name用MAX，容易误解业务逻辑；如果后续Name规则发生变化（虽然主键场景下不可能），会导致结果错误。

更优的第三种方案：先聚合再关联 #

当Purchases表数据量较大时，先对采购数据做聚合，再和用户表关联，能大幅减少JOIN的数据集大小，提升执行性能：

SELECT u.Id, u.[Name], COALESCE(p.TotalQuantity, 0) as Quantity
FROM dbo.[User] u
LEFT JOIN (
    SELECT UserId, SUM(Quantity) as TotalQuantity
    FROM dbo.Purchases
    GROUP BY UserId
) p ON p.UserId = u.Id

这种写法逻辑更清晰：先计算每个用户的总采购量，再和用户基础信息关联，避免了先JOIN产生大量中间数据再分组的额外开销。

最终选择建议 #

1. 追求兼容性和代码可读性，优先选方案A，它是标准写法，维护成本最低。
2. 若Purchases数据量较大，优先选第三种方案，性能表现更优。
3. 尽量避免方案B，除非是特定场景下的临时写法。

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