如何在现有SQL结构中实现按参考值分组前5行求和+其余行按1累加？

Absolutely, you can expand your existing SQL structure to meet this requirement! Let's refactor and extend the code to handle both the top 5 Approved rows (summing their actual weights) and all other rows (counting each as 1), while retaining the Details summary you had before.

Adjusted SQL Query #

SELECT 
    wsf_ref,
    SUM(weight_contribution) AS Total_Weight,
    GROUP_CONCAT(DISTINCT CONCAT('TOP 5 ', type, ' (', top5_sum, ')') SEPARATOR ' ') AS Details
FROM (
    SELECT 
        i.wsf_ref,
        i.type,
        -- Calculate contribution per row: use weight if Approved and top 5, else use 1
        CASE 
            WHEN r.rn <= 5 THEN i.weight 
            ELSE 1 
        END AS weight_contribution,
        -- Pull the precomputed top 5 sum for each wsf_ref+type group for the Details field
        MAX(r.top5_sum) OVER (PARTITION BY i.wsf_ref, i.type) AS top5_sum
    FROM individual i
    LEFT JOIN (
        -- Rank Approved rows per wsf_ref+type and compute the top 5 weight sum
        SELECT 
            id,
            wsf_ref,
            type,
            CASE 
                WHEN @prev_wsf = wsf_ref AND @prev_type = type THEN @row_num := @row_num + 1 
                ELSE @row_num := 1 
            END AS rn,
            -- Calculate total weight for top 5 rows in the group
            SUM(CASE WHEN @row_num <= 5 THEN weight ELSE 0 END) OVER (PARTITION BY wsf_ref, type) AS top5_sum,
            @prev_wsf := wsf_ref,
            @prev_type := type
        FROM individual
        CROSS JOIN (SELECT @row_num := 1, @prev_wsf := '', @prev_type := '') AS vars
        WHERE status = 'Approved'
        ORDER BY wsf_ref, type, id ASC
    ) r ON i.id = r.id
) AS all_rows
GROUP BY wsf_ref
ORDER BY Total_Weight DESC;

How This Works #

Let's break down the key parts:

1. Ranking Approved Rows: The inner subquery r assigns a row number to each Approved row within its wsf_ref+type group, and precomputes the total weight of the top 5 rows (top5_sum) for each group. This replaces your repetitive UNION ALL blocks with a single, maintainable subquery.
2. Calculating Row Contributions: For every row in the individual table:
   • If it's an Approved row and falls in the top 5 of its group, we use its actual weight as the contribution.
   • All other rows (Approved rows beyond the top 5, plus any non-Approved rows) contribute a value of 1.
3. Final Aggregation: We group by wsf_ref to sum all contributions into Total_Weight, and use GROUP_CONCAT to build the Details summary showing each type's top 5 weight total.

Verification Against Your Sample Data #

• For wsf_ref = 1:
  • Pike group: 5 rows ×10 =50, plus 1 extra row counted as 1 → total 51
  • Asp group: 5 rows ×10=50, plus 1 extra row counted as1 → total51
  • Combined total: 51+51=102 (matches your expected result)
• For wsf_ref=2:
  • Only 1 Approved Pike row (top 5 eligible) → contribution of10 (matches your expected result)

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