如何用WHILE循环逐行更新SQL表成本字段？按体积选最优费率

Got it, let's break this down step by step. The goal is to update the cost field row by row using a WHILE loop, calculating the minimum cost by prioritizing the most cost-effective shipping options first: 40' containers, then 20' containers, and finally LCL for any leftover volume that doesn't fill a full container.

Assumptions & Setup #

First, let's define the table structure and rate variables to match your scenario:

• Your table is named shipping_orders, with columns:
  • order_id: Unique ID for each row (used to iterate through records)
  • total_volume: The full volume to ship (your "volume" field)
  • cost: The field we'll update with the calculated minimum cost
• We'll use realistic rate values (adjust these to match your actual shipping costs):
  • 40' container: Fixed cost @rate_40ft = $1200, max volume @max_vol_40ft = 65 cubic units
  • 20' container: Fixed cost @rate_20ft = $750, max volume @max_vol_20ft = 30 cubic units
  • LCL: Per-unit volume cost @rate_lcl = $45 per cubic unit

SQL Code Implementation #

-- Declare rate and container capacity variables
DECLARE @rate_40ft DECIMAL(10,2) = 1200.00;
DECLARE @max_vol_40ft INT = 65;
DECLARE @rate_20ft DECIMAL(10,2) = 750.00;
DECLARE @max_vol_20ft INT = 30;
DECLARE @rate_lcl DECIMAL(10,2) = 45.00;

-- Variables for loop iteration and calculations
DECLARE @current_order_id INT;
DECLARE @remaining_volume INT;
DECLARE @calculated_cost DECIMAL(10,2);

-- Grab the first order ID to start processing
SELECT @current_order_id = MIN(order_id) FROM shipping_orders;

-- Start row-by-row processing loop
WHILE @current_order_id IS NOT NULL
BEGIN
    -- Initialize values for the current order
    SELECT @remaining_volume = total_volume, @calculated_cost = 0.00
    FROM shipping_orders
    WHERE order_id = @current_order_id;

    -- Step 1: Use as many 40' containers as possible (cheapest per unit volume)
    WHILE @remaining_volume >= @max_vol_40ft
    BEGIN
        SET @calculated_cost += @rate_40ft;
        SET @remaining_volume -= @max_vol_40ft;
    END

    -- Step 2: Check if using a 20' container is cheaper than LCL for remaining volume
    IF @remaining_volume > 0 AND @rate_20ft < (@remaining_volume * @rate_lcl)
    BEGIN
        SET @calculated_cost += @rate_20ft;
        SET @remaining_volume = 0;
    END

    -- Step 3: Use LCL for any leftover volume
    IF @remaining_volume > 0
    BEGIN
        SET @calculated_cost += (@remaining_volume * @rate_lcl);
    END

    -- Update the cost field for the current order
    UPDATE shipping_orders
    SET cost = @calculated_cost
    WHERE order_id = @current_order_id;

    -- Move to the next order
    SELECT @current_order_id = MIN(order_id) 
    FROM shipping_orders 
    WHERE order_id > @current_order_id;
END

Key Logic Breakdown #

• 40' Container Priority: We fill as many 40' containers as possible first because they offer the lowest cost per unit volume for large shipments.
• 20' Container Cost Check: Instead of automatically using a 20' for remaining volume, we compare the fixed 20' cost to the equivalent LCL cost. Only use the 20' if it's cheaper.
• LCL for Remainder: Any volume left after optimizing for full containers gets charged at the LCL per-unit rate.
• Row-by-Row Iteration: The outer WHILE loop processes each order one at a time by incrementing through unique order_id values.

Example Walkthrough (Your 75 Volume Scenario) #

For a total volume of 75:

1. 40' Container: 75 ≥ 65 → add $1200 to cost, remaining volume = 75 - 65 = 10
2. 20' Container Check: $750 > (10 * $45 = $450) → skip 20' container
3. LCL: 10 * $45 = $450 → total cost = $1200 + $450 = $1650

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