聚合函数中子查询应用：优化重复CASE逻辑的SQL查询

Hey there! Let's fix that repetitive SQL query you've got. The duplicate MAX(CASE...) blocks are making the code clunky and a pain to maintain, but we can clean this up nicely using subqueries with aggregate functions.

First, let's restate your original query (I fixed the duplicate column aliases since having three columns named APS Dev would cause ambiguity—feel free to tweak them to match your needs):

Select 
    Style_Color, 
    Style_Color_Desc, 
    RPT, 
    Weeks, 
    Minimum, 
    Max(CASE WHEN Cluster_ID in ('0150') then CAST(c.APS_Dev as decimal(10, 2)) end) AS 'APS Dev 0150',
    Max(CASE WHEN Cluster_ID in ('0082') then CAST(c.APS_Dev as decimal(10, 2)) end) AS 'APS Dev 0082',
    Max(CASE WHEN Cluster_ID in ('0096') then CAST(c.APS_Dev as decimal(10, 2)) end) AS 'APS Dev 0096'
From Cluster_Data c
group by Style_Color, Style_Color_Desc,RPT, Weeks, Minimum 
Order by rpt, Style_Color

The Best Optimization Approach #

Instead of repeating the same MAX(CASE...) logic for each Cluster_ID, we can centralize the list of target clusters using a CTE (common table expression—a type of subquery) and then use a cross join to simplify the aggregation. This way, if you ever need to add or remove a Cluster_ID, you only update one spot in the code:

-- First, define the Cluster_IDs we care about in a CTE (subquery)
WITH TargetClusters AS (
    SELECT '0150' AS ClusterID UNION ALL
    SELECT '0082' UNION ALL
    SELECT '0096'
)
SELECT 
    c.Style_Color, 
    c.Style_Color_Desc, 
    c.RPT, 
    c.Weeks, 
    c.Minimum,
    -- Single aggregation rule that works for all clusters in the CTE
    MAX(CASE WHEN c.Cluster_ID = tc.ClusterID THEN CAST(c.APS_Dev AS DECIMAL(10,2)) END) AS [APS Dev - ' + tc.ClusterID + ']
FROM Cluster_Data c
CROSS JOIN TargetClusters tc
GROUP BY c.Style_Color, c.Style_Color_Desc, c.RPT, c.Weeks, c.Minimum, tc.ClusterID
ORDER BY c.RPT, c.Style_Color, tc.ClusterID;

If you need the results as separate columns (one per cluster) instead of rows, you can use the PIVOT operator with the CTE to get that structure without repeating code. Alternatively, if you specifically want to use a subquery inside the aggregate function (as you mentioned), here's how to do that:

Subquery Inside the Aggregate Function #

This approach uses correlated subqueries directly within the MAX() function to fetch the value for each Cluster_ID, eliminating the repeated CASE syntax:

SELECT 
    Style_Color, 
    Style_Color_Desc, 
    RPT, 
    Weeks, 
    Minimum,
    MAX(
        -- Subquery to get the value for Cluster '0150'
        SELECT CAST(APS_Dev AS DECIMAL(10,2)) 
        FROM Cluster_Data c2 
        WHERE c2.Style_Color = c.Style_Color 
          AND c2.Style_Color_Desc = c.Style_Color_Desc 
          AND c2.RPT = c.RPT 
          AND c2.Weeks = c.Weeks 
          AND c2.Minimum = c.Minimum 
          AND c2.Cluster_ID = '0150'
    ) AS 'APS Dev 0150',
    MAX(
        -- Subquery for Cluster '0082'
        SELECT CAST(APS_Dev AS DECIMAL(10,2)) 
        FROM Cluster_Data c2 
        WHERE c2.Style_Color = c.Style_Color 
          AND c2.Style_Color_Desc = c.Style_Color_Desc 
          AND c2.RPT = c.RPT 
          AND c2.Weeks = c.Weeks 
          AND c2.Minimum = c.Minimum 
          AND c2.Cluster_ID = '0082'
    ) AS 'APS Dev 0082',
    MAX(
        -- Subquery for Cluster '0096'
        SELECT CAST(APS_Dev AS DECIMAL(10,2)) 
        FROM Cluster_Data c2 
        WHERE c2.Style_Color = c.Style_Color 
          AND c2.Style_Color_Desc = c.Style_Color_Desc 
          AND c2.RPT = c.RPT 
          AND c2.Weeks = c.Weeks 
          AND c2.Minimum = c.Minimum 
          AND c2.Cluster_ID = '0096'
    ) AS 'APS Dev 0096'
FROM Cluster_Data c
GROUP BY Style_Color, Style_Color_Desc, RPT, Weeks, Minimum 
ORDER BY RPT, Style_Color;

Quick Notes #

• The CTE approach is my top recommendation—it's cleaner, easier to maintain, and usually performs better because it avoids running multiple correlated subqueries.
• If you need columnar output (one cluster per column), combining the CTE with PIVOT will give you that clean structure without repetition.

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