MySQL动态参数查询：从外部表取值匹配最接近运输成本

To replace the hardcoded values in your query and dynamically pull volumen (m3) and gewicht (weight) from database.matrixtable, you’ll need to join the two tables and use window functions to rank transport options by their proximity to the target values. Here’s a practical solution tailored to your needs:

Core Logic Breakdown #

We need to:

• Pair each row in matrixtable (containing orderitemid, country, volumen, gewicht) with matching country entries in transportcost.
• Calculate how close each transport option’s m3 and weight are to the target values from matrixtable.
• Rank options by their combined proximity to pick the closest match.
• Insert the top-ranked result into transportcostPerItem.

Rewritten Query (Query2) #

WITH ranked_transport_options AS (
    SELECT
        mt.orderitemid,
        tc.country,
        tc.transportername,
        tc.m3,
        tc.weight,
        tc.price,
        -- Calculate absolute differences for m3 and weight
        ABS(tc.m3 - mt.volumen) AS abs_m3_diff,
        ABS(tc.weight - mt.gewicht) AS abs_weight_diff,
        -- Rank options by combined proximity (adjust weighting if needed)
        ROW_NUMBER() OVER (
            PARTITION BY mt.orderitemid
            ORDER BY (ABS(tc.m3 - mt.volumen) + ABS(tc.weight - mt.gewicht)) ASC
        ) AS proximity_rank
    FROM database.matrixtable mt
    INNER JOIN database.transportcost tc 
        ON mt.country = tc.country
)
-- Insert the closest match for each orderitem into the target table
INSERT INTO database.transportcostPerItem (
    orderitemid, 
    country, 
    transportername, 
    m3, 
    weight, 
    price
)
SELECT 
    orderitemid, 
    country, 
    transportername, 
    m3, 
    weight, 
    price
FROM ranked_transport_options
WHERE proximity_rank = 1;

Key Customization Tips #

• Handling Ties: If you want to include all transport options with the exact same minimal difference (instead of just one), replace ROW_NUMBER() with RANK().
• Weighted Proximity: If one metric (m3 vs weight) is more critical, adjust the ranking formula to prioritize it. For example: ABS(tc.m3 - mt.volumen)*2 + ABS(tc.weight - mt.gewicht) to double the importance of m3 closeness.
• Including Missing Matches: Use LEFT JOIN instead of INNER JOIN if you need to retain orderitems even when no matching country entries exist in transportcost (you’ll need to handle NULL values in the select clause).

Expected Output #

This query will populate transportcostPerItem exactly as you specified:

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