SQL实现：将物料编码及其替代项库存合并至同一行

Alright, let's break this down. First, I’ll make some reasonable assumptions about your Stock Table structure since you didn’t share it—this is a common setup where we have main items, their alternatives, and respective stock quantities. Let’s start with a typical scenario:

Example Table Structure #

Suppose your Stock_Table includes these columns:

• ITEM_CODE: Unique ID for each material (main or alternative)
• STOCK_QTY: Current stock quantity for the material
• ALT_FOR_ITEM: The main item code this material is an alternative for (NULL if it’s a main item itself)

Sample data might look like this:

Solution SQL Query #

To roll up stock quantities for each main item plus all its alternatives into a single row, use this query:

SELECT
    -- Group main items and their alternatives under the main item code
    COALESCE(s.ALT_FOR_ITEM, s.ITEM_CODE) AS MAIN_ITEM,
    SUM(s.STOCK_QTY) AS STOCK_COLUMN
FROM
    Stock_Table s
GROUP BY
    COALESCE(s.ALT_FOR_ITEM, s.ITEM_CODE)
ORDER BY
    MAIN_ITEM;

How This Works #

• COALESCE(s.ALT_FOR_ITEM, s.ITEM_CODE): This function uses the ALT_FOR_ITEM value if it exists (meaning the row is an alternative part), otherwise it falls back to the ITEM_CODE itself (the main item). This lets us group all related items under one main item ID.
• SUM(s.STOCK_QTY): Adds up all stock quantities for the grouped main item and its alternatives.
• GROUP BY: Groups the results by the derived main item code to ensure we get one row per main item with total stock.

If Your Table Structure Differs #

If alternative relationships are stored in a separate table (e.g., Alternative_Items with MAIN_ITEM and ALT_ITEM columns), adjust the query like this:

-- Sum stock for main items and their alternatives from the relationship table
SELECT
    ai.MAIN_ITEM,
    SUM(s.STOCK_QTY) AS STOCK_COLUMN
FROM
    Alternative_Items ai
JOIN
    Stock_Table s ON s.ITEM_CODE IN (ai.MAIN_ITEM, ai.ALT_ITEM)
GROUP BY
    ai.MAIN_ITEM

-- Add main items that have no alternatives
UNION

SELECT
    ITEM_CODE AS MAIN_ITEM,
    STOCK_QTY AS STOCK_COLUMN
FROM
    Stock_Table
WHERE
    ITEM_CODE NOT IN (SELECT MAIN_ITEM FROM Alternative_Items);

This covers both cases: items with alternatives and standalone items with no substitutes.

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