如何将多组带共同过滤条件的SQL聚合查询合并为单一查询？

当然可以把这些重复的查询合并成一个更高效的单一查询！你的所有查询共享了大量的过滤条件，完全可以通过**条件聚合（CASE WHEN）**来实现，同时还能根据需求整合标签统计的部分，下面分两种场景给你具体方案：

一、合并评分类查询（单行多列输出） #

前10个查询都是针对不同QuestionId的评分统计，我们可以用CASE WHEN配合聚合函数，把每个指标转成单独的列，同时计算统一的总Count：

declare @salesforceId int 
set @salesforceId = 109924 

SELECT
    -- 各个评分指标
    AVG(CASE WHEN qr.QuestionId = 1 THEN CAST(qr.RatingScaleOptionId as float) END) AS GeneralFeedback,
    AVG(CASE WHEN qr.QuestionId = 3 THEN CAST(qr.RatingScaleOptionId as float) END) AS FoodRating,
    AVG(CASE WHEN qr.QuestionId = 4 THEN CAST(qr.RatingScaleOptionId as float) END) AS DrinkRating,
    AVG(CASE WHEN qr.QuestionId = 5 THEN CAST(qr.RatingScaleOptionId as float) END) AS AtmosphereRating,
    AVG(CASE WHEN qr.QuestionId = 6 THEN CAST(qr.RatingScaleOptionId as float) END) AS ServiceRating,
    AVG(CASE WHEN qr.QuestionId = 7 THEN CAST(qr.RatingScaleOptionId as float) END) AS BookingServiceRating,
    AVG(CASE WHEN qr.QuestionId = 12 THEN CAST(qr.RatingScaleOptionId as float) END) AS RecommendRestaurantRating,
    AVG(CASE WHEN qr.QuestionId = 13 THEN CAST(qr.RatingScaleOptionId as float) END) AS OverallRating,
    AVG(CASE WHEN qr.QuestionId = 525 THEN CAST(qr.RatingScaleOptionId as float) END) AS ValueForMoneyRating,
    AVG(CASE WHEN qr.QuestionId = 526 THEN CAST(qr.RatingScaleOptionId as float) END) AS LocationRating,
    -- 统一的总符合条件响应数（即你说的所有查询一致的Count）
    COUNT(DISTINCT sr.SurveyResponseId) AS TotalCount
FROM QuestionResponse qr 
JOIN SurveyResponse sr 
    ON qr.SurveyResponseId = sr.SurveyResponseId 
    AND sr.StatusId IN (5, 7) 
    AND sr.RestaurantNetworkId = @salesforceId
-- 过滤仅需要的QuestionId，减少数据处理量
WHERE qr.QuestionId IN (1,3,4,5,6,7,12,13,525,526)

关键说明： #

• 用CASE WHEN在AVG函数内部做条件判断，只对对应QuestionId的行计算平均值，没有匹配的行返回NULL，不影响聚合结果。
• COUNT(DISTINCT sr.SurveyResponseId)统计符合过滤条件的唯一响应数，确保这个Count在所有指标中数值一致。
• 添加WHERE子句过滤指定QuestionId，避免扫描无关数据，提升查询效率。

二、整合标签统计查询（可选） #

最后一个标签统计的查询是分组结果，如果需要和评分数据整合到同一结果集，可以用PIVOT把标签转成列（如果标签是固定的）：

declare @salesforceId int 
set @salesforceId = 109924 

-- 先获取评分数据
WITH RatingData AS (
    SELECT
        AVG(CASE WHEN qr.QuestionId = 1 THEN CAST(qr.RatingScaleOptionId as float) END) AS GeneralFeedback,
        AVG(CASE WHEN qr.QuestionId = 3 THEN CAST(qr.RatingScaleOptionId as float) END) AS FoodRating,
        AVG(CASE WHEN qr.QuestionId = 4 THEN CAST(qr.RatingScaleOptionId as float) END) AS DrinkRating,
        AVG(CASE WHEN qr.QuestionId = 5 THEN CAST(qr.RatingScaleOptionId as float) END) AS AtmosphereRating,
        AVG(CASE WHEN qr.QuestionId = 6 THEN CAST(qr.RatingScaleOptionId as float) END) AS ServiceRating,
        AVG(CASE WHEN qr.QuestionId = 7 THEN CAST(qr.RatingScaleOptionId as float) END) AS BookingServiceRating,
        AVG(CASE WHEN qr.QuestionId = 12 THEN CAST(qr.RatingScaleOptionId as float) END) AS RecommendRestaurantRating,
        AVG(CASE WHEN qr.QuestionId = 13 THEN CAST(qr.RatingScaleOptionId as float) END) AS OverallRating,
        AVG(CASE WHEN qr.QuestionId = 525 THEN CAST(qr.RatingScaleOptionId as float) END) AS ValueForMoneyRating,
        AVG(CASE WHEN qr.QuestionId = 526 THEN CAST(qr.RatingScaleOptionId as float) END) AS LocationRating,
        COUNT(DISTINCT sr.SurveyResponseId) AS TotalCount
    FROM QuestionResponse qr 
    JOIN SurveyResponse sr 
        ON qr.SurveyResponseId = sr.SurveyResponseId 
        AND sr.StatusId IN (5, 7) 
        AND sr.RestaurantNetworkId = @salesforceId
    WHERE qr.QuestionId IN (1,3,4,5,6,7,12,13,525,526)
),
-- 获取标签数据并转成列（需要替换[Tag1],[Tag2]为实际标签值）
TagData AS (
    SELECT *
    FROM (
        SELECT 
            CultureInvariantText AS Tag,
            COUNT(*) AS TagCount
        FROM SurveyResponse SR 
        INNER JOIN [QuestionResponseFixedOptions] QR ON SR.SurveyResponseId = QR.SurveyResponseId 
        INNER JOIN QuestionResponseOption QRO ON QR.[ResponseOptionId] = QRO.[ResponseOptionId] 
        INNER JOIN Question Q ON QRO.QuestionId = Q.QuestionId 
        INNER JOIN LocalizableText LT ON QRO.ResponseValue = LT.LocalizableTextId 
        WHERE sr.RestaurantNetworkId = @salesforceId 
        GROUP BY CultureInvariantText
    ) t
    PIVOT (
        SUM(TagCount)
        FOR Tag IN ([Tag1], [Tag2], [Tag3]) -- 替换为你的实际标签值
    ) p
)
-- 合并评分和标签数据
SELECT rd.*, td.*
FROM RatingData rd
CROSS JOIN TagData td

注意： #

如果标签是动态变化的，需要用动态SQL来生成PIVOT的列列表，否则硬编码标签值即可。如果不需要整合，单独保留标签查询也可以，毕竟它的结果结构和评分查询不同。

这种合并方式的最大好处是减少了多次扫描数据库表的次数，尤其是数据量较大时，性能提升会非常明显。

内容的提问来源于stack exchange，提问作者Farhad-Taran