基于订单日期查询物料标准成本的SQL实现方案咨询

Got it, let's break down how to solve this problem—you need to map each order to the most recent standard cost that was active on or before the order date, which is exactly what your example shows. Here's a step-by-step approach with SQL implementations that work across most modern databases.

First, Clarify Table Structures #

Let's formalize the tables based on your example (I'll add a material_id field to handle multiple materials, which is essential for real-world scenarios):

• material_costs: Stores standard cost updates
  • material_id (e.g., 'ABC')
  • update_date (date when the cost took effect)
  • cost (the standard cost value)
• orders: Stores customer orders
  • material_id (matches the cost table)
  • order_date (date the order was placed)
  • price (sales price from the order)

Approach 1: Using Window Functions (Compatible with Most Databases) #

This method uses ROW_NUMBER() to rank cost updates for each material, then picks the latest valid cost for each order.

WITH ranked_cost_updates AS (
    SELECT
        mc.material_id,
        mc.update_date,
        mc.cost,
        o.order_date,
        o.price,
        -- Rank costs for each material + order date by update date (newest first)
        ROW_NUMBER() OVER (
            PARTITION BY mc.material_id, o.order_date
            ORDER BY mc.update_date DESC
        ) AS cost_rank
    FROM material_costs mc
    INNER JOIN orders o 
        ON mc.material_id = o.material_id
        AND mc.update_date <= o.order_date -- Only keep costs active on/before order date
)
SELECT
    order_date,
    price,
    cost
FROM ranked_cost_updates
WHERE cost_rank = 1 -- Pick the newest valid cost for each order
ORDER BY order_date DESC;

How This Works: #

1. The CTE ranked_cost_updates joins orders with all applicable cost records (those updated on or before the order date).
2. ROW_NUMBER() assigns a rank to each cost for a given material and order date—rank 1 goes to the most recent cost update.
3. We filter for cost_rank = 1 to get the exact cost that was in effect when the order was placed.

Approach 2: Using Lateral Joins (Cleaner for PostgreSQL/SQL Server) #

If your database supports lateral joins (like PostgreSQL, SQL Server, or Oracle 12+), this is a more concise way to get the latest valid cost per order:

SELECT
    o.order_date,
    o.price,
    mc.cost
FROM orders o
LATERAL (
    -- Get the newest cost update for the material that's <= the order date
    SELECT cost
    FROM material_costs mc
    WHERE mc.material_id = o.material_id
      AND mc.update_date <= o.order_date
    ORDER BY mc.update_date DESC
    LIMIT 1
) mc;

How This Works: #

For every row in the orders table, the lateral subquery finds the single most recent cost update that was active when the order was placed. This is straightforward and easy to read.

Testing with Your Example Data #

Let's plug in your sample data to verify:

• material_costs for 'ABC':

  material_id	update_date	cost
  ABC	2017-12-26	40
  ABC	2017-02-01	43
  ABC	2016-12-27	39

• orders for 'ABC':

  material_id	order_date	price
  ABC	2018-01-01	80
  ABC	2017-01-01	84

Both queries will return exactly your desired result:

Key Notes #

• Ensure your date columns are stored as date types (not strings) to avoid incorrect comparisons.
• If multiple cost updates exist on the same date, ROW_NUMBER() will pick one arbitrarily—use RANK() instead if you need to handle ties (though standard cost updates typically don't have duplicates on the same date).
• Add a WHERE clause to filter for specific materials (e.g., WHERE o.material_id = 'ABC') if you don't need results for all materials.

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