技术求助：将related_product_id映射至product_id并新增关联SKU列

Based on your requirement, here's a straightforward approach using SQL to achieve the mapping and column addition:

Step 1: Create a Lookup Mapping Table #

First, you'll need a table that defines the relationship between related_product_id, product_id, and the corresponding color_related_sku. This acts as your reference for all mappings:

-- Create the mapping table (adjust data types as per your actual schema)
CREATE TABLE product_relation_mappings (
    related_product_id INT PRIMARY KEY,
    product_id VARCHAR(50) NOT NULL,
    color_related_sku VARCHAR(50) NOT NULL
);

-- Insert your example mappings (add all your required pairs here)
INSERT INTO product_relation_mappings (related_product_id, product_id, color_related_sku)
VALUES
    (100, 'PROD-123', 'HL-010817-FMWP-BK'), -- Replace 'PROD-123' with actual product_id for 100
    (101, 'PROD-456', 'HL-020817-FMWP-BK'); -- Replace 'PROD-456' with actual product_id for 101

Step 2: Enrich Your Main Data with Mapped Values #

Assuming you have a main table (let's call it products_data) that contains the related_product_id (e.g., color_related_id_1), use a JOIN to pull in the matching product_id and color_related_sku:

SELECT
    pd.*, -- Keep all existing columns from your main table
    prm.product_id,
    prm.color_related_sku
FROM products_data pd
JOIN product_relation_mappings prm 
    ON pd.color_related_id_1 = prm.related_product_id;

This query will add the product_id and color_related_sku columns to your result set, with values that match the related_product_id in your main table.

Step 3: List All Unique Product IDs #

To get a list of all product_id values from your mapping (or the enriched data), run:

-- List all distinct product IDs from the mapping table
SELECT DISTINCT product_id FROM product_relation_mappings;

-- Or, if you want to include their related SKUs and IDs
SELECT product_id, related_product_id, color_related_sku 
FROM product_relation_mappings 
ORDER BY product_id;

Notes: #

• Replace table/column names (like products_data, color_related_id_1, PROD-123) with your actual schema details.
• If you don't want to create a permanent table, you can use a CTE (Common Table Expression) for the mapping instead:

  WITH product_mappings AS (
      SELECT 100 AS related_product_id, 'PROD-123' AS product_id, 'HL-010817-FMWP-BK' AS color_related_sku
      UNION ALL
      SELECT 101 AS related_product_id, 'PROD-456' AS product_id, 'HL-020817-FMWP-BK' AS color_related_sku
  )
  SELECT pd.*, pm.product_id, pm.color_related_sku
  FROM products_data pd
  JOIN product_mappings pm ON pd.color_related_id_1 = pm.related_product_id;
  

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