如何优化SQL查询，按ProductId汇总Quantity并返回单条记录

看起来你的原查询想要实现两个核心目标：每个ProductId返回唯一一条记录，同时汇总该产品的Quantity，还要拼接对应的LandingIds。不过原查询存在几个潜在问题，比如子查询没有关联到外层的ProductId，导致LandingIds是所有符合条件的LandingId的集合，而不是当前ProductId对应的；另外用DISTINCT无法正确实现Quantity的汇总，只是去重而已。

我来给你调整并优化这个查询，同时解释每个部分的作用：

优化后的查询代码 #

SELECT 
    ld.ProductId,
    SUM(ld.Quantity) AS TotalQuantity, -- 汇总该ProductId的总Quantity
    MAX(ld.UnitPrice) AS UnitPrice, -- 假设同一ProductId的UnitPrice一致，用MAX/MIN都可；若有不同需按业务调整
    STUFF((
        SELECT ',' + CAST(lh1.LandingId AS VARCHAR) 
        FROM dbo.LandingHeaders lh1
        INNER JOIN dbo.LandingDetails ld1 ON lh1.LandingId = ld1.LandingId
        WHERE 
            lh1.LandingDate BETWEEN '20171101' AND '20171107' 
            AND lh1.VesselOwnerId = 42
            AND ld1.ProductId = ld.ProductId -- 关键：关联外层的ProductId，只拼接当前产品的LandingId
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS LandingIds
FROM dbo.LandingHeaders lh
INNER JOIN dbo.LandingDetails ld ON lh.LandingId = ld.LandingId -- 补全原查询中省略的关联
WHERE 
    lh.LandingDate BETWEEN '20171101' AND '20171107' 
    AND lh.VesselOwnerId = 42
GROUP BY ld.ProductId; -- 按ProductId分组，确保每个产品只返回一条记录

关键优化点说明 #

• 用GROUP BY替代DISTINCT
DISTINCT只是对结果集去重，但如果同一个ProductId有多个行（比如不同的LandingId对应同一产品），它无法帮你汇总Quantity。而GROUP BY ld.ProductId可以确保每个ProductId只生成一条记录，同时允许你使用聚合函数（比如SUM）来计算总Quantity。

• 修复LandingIds的拼接逻辑
  原查询的子查询没有和外层的ProductId关联，导致所有符合日期和VesselOwnerId的LandingId都被拼在一起。优化后通过ld1.ProductId = ld.ProductId关联，确保每个ProductId只拼接自己对应的LandingId。另外，添加TYPE和.value('.', 'NVARCHAR(MAX)')是为了避免特殊字符（如&、<）被转义成XML实体，保证结果的准确性。

• 处理UnitPrice的逻辑
  如果同一ProductId的UnitPrice在所有记录中都相同，你也可以把ld.UnitPrice加入GROUP BY子句；如果存在不同的UnitPrice，需要根据业务需求选择聚合方式（比如AVG取平均，SUM总价，或者MIN/MAX取极值）。

性能优化建议 #

• 给LandingHeaders创建复合索引：CREATE INDEX IX_LandingHeaders_VesselOwner_LandingDate ON dbo.LandingHeaders (VesselOwnerId, LandingDate) INCLUDE (LandingId);，加速过滤和关联。
• 给LandingDetails创建索引：CREATE INDEX IX_LandingDetails_ProductId_LandingId ON dbo.LandingDetails (ProductId, LandingId) INCLUDE (Quantity, UnitPrice);，加速分组、聚合和子查询的关联。
• 如果LandingIds拼接后的字符串非常长，考虑把拼接逻辑放到应用层处理，避免SQL服务器承担过多字符串拼接的压力。

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