实现同类别行求和至总和为1的数据库查询需求

Hey there! Let's work through this problem together. You need to sum the total values per category, but cap that summed result at 1—even if the actual sum of rows in a category exceeds 1. Depending on whether you just need the capped category total or want to adjust individual rows so their sum doesn't go over 1, here are practical solutions for common database systems:

First, Let's Define Our Example Table #

Let's assume we have a table named category_values with these columns and sample data:

Solution 1: Get Capped Category Totals (Single Row Per Category) #

This gives you one row per category with the sum of total, capped at 1.

MySQL/MariaDB & PostgreSQL #

Both databases support the LEAST() function, which makes this straightforward:

SELECT
    category,
    LEAST(SUM(total), 1) AS capped_total
FROM category_values
GROUP BY category;

How it works: SUM(total) calculates the full sum for each category, then LEAST() picks the smaller value between that sum and 1—automatically capping totals that would exceed 1.

SQL Server #

SQL Server doesn't have a built-in LEAST() function, but a CASE statement does the trick:

SELECT
    category,
    CASE
        WHEN SUM(total) > 1 THEN 1
        ELSE SUM(total)
    END AS capped_total
FROM category_values
GROUP BY category;

Solution 2: Adjust Individual Rows (Keep All Rows, Sum ≤1) #

If you need to retain all original rows but adjust their total values so the sum per category never exceeds 1, use window functions to scale values proportionally:

PostgreSQL #

WITH category_totals AS (
    SELECT
        category,
        SUM(total) OVER (PARTITION BY category) AS full_sum
    FROM category_values
)
SELECT
    ct.category,
    CASE
        WHEN ct.full_sum <= 1 THEN cv.total
        ELSE cv.total / ct.full_sum * 1  -- Scale values to sum to 1
    END AS adjusted_total
FROM category_values cv
JOIN category_totals ct ON cv.category = ct.category;

SQL Server #

WITH category_totals AS (
    SELECT
        category,
        total,
        SUM(total) OVER (PARTITION BY category) AS full_sum
    FROM category_values
)
SELECT
    category,
    CASE
        WHEN full_sum <= 1 THEN total
        ELSE total / full_sum * 1
    END AS adjusted_total
FROM category_totals;

MySQL 8.0+ #

MySQL 8.0 and above support window functions too:

WITH category_totals AS (
    SELECT
        category,
        total,
        SUM(total) OVER (PARTITION BY category) AS full_sum
    FROM category_values
)
SELECT
    category,
    CASE
        WHEN full_sum <= 1 THEN total
        ELSE total / full_sum * 1
    END AS adjusted_total
FROM category_totals;

Quick Tips #

• If your total column uses integer types, cast it to a decimal/float first to avoid integer division errors (e.g., CAST(total AS DECIMAL(10,2))).
• The first solution is ideal for summary reports, while the second is useful if you need to preserve individual row context but enforce the sum cap.

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