MySQL背包问题复杂查询：调整字段值匹配指定求和需求

Alright, let's break down this knapsack-style adjustment problem you're dealing with. The core ask is: you have a target decimal value that's less than the sum of a column in your table, but it's not an exact subset sum of the column's values. You can't increase the target, but you can decrease any of the column's values to make their total match the target. Here are practical, actionable ways to handle this in MySQL.

First, Lay Out the Basics #

Let's assume your table is named items, with columns id (primary key) and value (the DECIMAL column we're adjusting). Let's call the user-provided target @target. First, confirm the prerequisite:

SET @target = 123.45; -- Replace with user's input
SET @total = (SELECT SUM(value) FROM items);

-- Verify target is valid (should return 1 if true)
SELECT IF(@target < @total, 1, 0) AS is_valid_target;

We'll also calculate the total amount we need to subtract from the column values to hit the target:

SET @delta = @total - @target;

Option 1: Proportional Adjustment (Simple, Even Distribution) #

If you want to spread the reduction evenly across all values (no preference for which items to adjust), use proportional scaling. This is the easiest approach, though you might need to handle decimal precision.

SELECT
    id,
    value AS original_value,
    -- Round to 2 decimal places to match typical currency/decimal use cases
    ROUND(value - (value * @delta / @total), 2) AS adjusted_value
FROM items;

Note: #

• Adjust the ROUND parameter to match your column's decimal precision (e.g., ROUND(..., 4) for 4 decimal places).
• If precision is critical, use TRUNCATE instead of ROUND to avoid rounding up beyond the target.

Option 2: Prioritize Reducing Larger Values (Minimize Number of Changes) #

If you want to modify as few items as possible, start by cutting the largest values first until we've covered the full @delta. This uses CTEs and variables to handle the iterative reduction:

SET @target = 123.45;
SET @total = (SELECT SUM(value) FROM items);
SET @delta = @total - @target;

WITH ranked_items AS (
    -- Sort items from largest to smallest value
    SELECT
        id,
        value,
        ROW_NUMBER() OVER (ORDER BY value DESC) AS rn
    FROM items
),
adjusted_deductions AS (
    -- Deduct from largest items first until delta is exhausted
    SELECT
        id,
        value,
        LEAST(value, @delta) AS deduction,
        -- Update remaining delta after each deduction
        @delta := @delta - LEAST(value, @delta) AS remaining_delta
    FROM ranked_items
    WHERE @delta > 0
    UNION ALL
    -- Keep items unchanged once delta is gone
    SELECT
        id,
        value,
        0 AS deduction,
        0 AS remaining_delta
    FROM ranked_items
    WHERE @delta <= 0
)
SELECT
    id,
    value AS original_value,
    value - deduction AS adjusted_value
FROM adjusted_deductions
ORDER BY id;

Option 3: Stored Procedure for Large Datasets #

For bigger tables, using a stored procedure with a cursor can be more efficient and easier to debug than complex CTEs. This does the same prioritized reduction but with explicit looping:

DELIMITER //
CREATE PROCEDURE adjust_to_target(IN target DECIMAL(10,2))
BEGIN
    DECLARE total DECIMAL(10,2);
    DECLARE delta DECIMAL(10,2);
    DECLARE done INT DEFAULT FALSE;
    DECLARE item_id INT;
    DECLARE item_val DECIMAL(10,2);
    DECLARE cur CURSOR FOR SELECT id, value FROM items ORDER BY value DESC;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- Calculate total and required delta
    SELECT SUM(value) INTO total FROM items;
    SET delta = total - target;

    -- Create temp table to store adjustments (avoids modifying original data directly)
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_adjustments (
        id INT PRIMARY KEY,
        original_value DECIMAL(10,2),
        adjusted_value DECIMAL(10,2)
    );
    INSERT INTO temp_adjustments SELECT id, value, value FROM items;

    -- Loop through largest items and deduct until delta is gone
    OPEN cur;
    adjustment_loop: LOOP
        FETCH cur INTO item_id, item_val;
        IF done OR delta <= 0 THEN
            LEAVE adjustment_loop;
        END IF;
        SET @deduct_amount = LEAST(item_val, delta);
        UPDATE temp_adjustments 
        SET adjusted_value = adjusted_value - @deduct_amount 
        WHERE id = item_id;
        SET delta = delta - @deduct_amount;
    END LOOP;
    CLOSE cur;

    -- Return the final adjusted values
    SELECT * FROM temp_adjustments;
    DROP TEMPORARY TABLE IF EXISTS temp_adjustments;
END //
DELIMITER ;

-- Call the procedure with your target
CALL adjust_to_target(123.45);

Key Considerations #

• Precision Handling: Always use ROUND, TRUNCATE, or explicit CAST to maintain your DECIMAL column's precision and avoid floating-point errors.
• Performance: For extremely large datasets (100k+ rows), MySQL isn't the most efficient tool for complex iterative logic. Consider exporting the data to an application layer (Python, Java) and using dynamic programming algorithms to compute adjustments, then write the results back to the database.
• Data Safety: Never modify the original table directly without first testing on a copy or using a temp table to preview results.

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