SQL统计各地点多类服务时段总数结果分行问题排查优化求助

• 外层查询的行粒度为「单条服务时段记录」，而非「单个地点」，即使加DISTINCT也仅能去重完全相同的行，同一地点下对应三类不同服务时自然会拆分为3行，每行仅对应服务类型的子查询能计算出数值，其余两类返回0
• 子查询中存在笔误：你写的SUM(c.TimeSlotsCount)里的别名c不存在，子查询内Schedule表的别名是s，存在语法报错风险
• 三次重复写关联Schedule的子查询，需要扫描三次表，性能损耗极大

如果仅需要返回有可用时段的地点，用以下写法即可：

SELECT
    lc.locationName,
    ISNULL(SUM(CASE WHEN sv.ServiceName LIKE '%TYPE_ONE%' THEN sc.TimeSlotsCount ELSE 0 END), 0) AS TYPE_ONE_COUNT,
    ISNULL(SUM(CASE WHEN sv.ServiceName LIKE '%TYPE_TWO%' THEN sc.TimeSlotsCount ELSE 0 END), 0) AS TYPE_TWO_COUNT,
    ISNULL(SUM(CASE WHEN sv.ServiceName LIKE '%TYPE_THREE%' THEN sc.TimeSlotsCount ELSE 0 END), 0) AS TYPE_THREE_COUNT
FROM 
    Schedule AS sc WITH (NOLOCK)
INNER JOIN 
    ServiceList AS sv ON sv.ServiceId = sc.ServiceId
INNER JOIN 
    Location AS lc ON sv.LocationId = lc.LocationId
WHERE
    sc.Date >= GETDATE()
GROUP BY
    lc.LocationId, lc.locationName -- 按LocationId分组避免同名地点合并错误

如果需要返回所有地点（包括没有任何可用时段的地点，三类值都返回0），可以调整关联逻辑：

SELECT
    lc.locationName,
    ISNULL(SUM(CASE WHEN sv.ServiceName LIKE '%TYPE_ONE%' THEN sc.TimeSlotsCount ELSE 0 END), 0) AS TYPE_ONE_COUNT,
    ISNULL(SUM(CASE WHEN sv.ServiceName LIKE '%TYPE_TWO%' THEN sc.TimeSlotsCount ELSE 0 END), 0) AS TYPE_TWO_COUNT,
    ISNULL(SUM(CASE WHEN sv.ServiceName LIKE '%TYPE_THREE%' THEN sc.TimeSlotsCount ELSE 0 END), 0) AS TYPE_THREE_COUNT
FROM 
    Location AS lc
LEFT JOIN 
    ServiceList AS sv ON sv.LocationId = lc.LocationId
LEFT JOIN 
    Schedule AS sc WITH (NOLOCK) ON sv.ServiceId = sc.ServiceId AND sc.Date >= GETDATE()
GROUP BY
    lc.LocationId, lc.locationName

• 提前过滤今日及之后的时段数据，减少后续关联、计算的数据量
• 用条件聚合替代重复子查询，仅需要扫描一次Schedule表，性能比原写法提升3倍以上
• 按地点维度分组，保证每个地点仅返回一行结果
• 用ISNULL包裹聚合结果，无对应服务数据时返回0而非NULL

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