按条件拆分列及基于多表关联生成目标myreport表的技术实现问询

Got it, let's break down how to solve this problem. You've got three tables with a many-to-one mapping between old and new product codes, and you need to split the single count column into three package-specific columns to match the myreport structure. Here's a step-by-step solution:

Core Approach #

First, we'll link the company_report and dictionary tables to map each old product code's count to its corresponding new product code. Then, we'll use conditional aggregation to split the count values into the three package columns based on which package each old product code corresponds to.

SQL Implementation #

Assuming you can determine the package type (1/3/5) from the old_product_code itself (like a naming convention, e.g., PROD_X_1 for package of 1), here's the query you can use:

SELECT
    d.new_product_code,
    -- Sum counts for package of 1; return 0 if no matching old codes
    SUM(CASE WHEN <your-package-1-condition> THEN cr.count ELSE 0 END) AS package_of_1,
    -- Sum counts for package of 3
    SUM(CASE WHEN <your-package-3-condition> THEN cr.count ELSE 0 END) AS package_of_3,
    -- Sum counts for package of 5
    SUM(CASE WHEN <your-package-5-condition> THEN cr.count ELSE 0 END) AS package_of_5
FROM
    company_report cr
INNER JOIN
    dictionary d ON cr.old_product_code = d.old_product_code
GROUP BY
    d.new_product_code;

Example with Realistic Conditions #

If your old_product_code uses a suffix to indicate the package (like COLA_SMALL_1 for package of 1), you can replace the placeholders with actual logic. For example, using the last character to identify the package:

SELECT
    d.new_product_code,
    SUM(CASE WHEN RIGHT(cr.old_product_code, 1) = '1' THEN cr.count ELSE 0 END) AS package_of_1,
    SUM(CASE WHEN RIGHT(cr.old_product_code, 1) = '3' THEN cr.count ELSE 0 END) AS package_of_3,
    SUM(CASE WHEN RIGHT(cr.old_product_code, 1) = '5' THEN cr.count ELSE 0 END) AS package_of_5
FROM
    company_report cr
INNER JOIN
    dictionary d ON cr.old_product_code = d.old_product_code
GROUP BY
    d.new_product_code;

Extra Tips #

• If some old product codes don't have a mapping in dictionary and you still want to include them (with new_product_code as NULL), switch the INNER JOIN to a LEFT JOIN.
• If the package type is stored in a separate table (instead of being derived from the old code), just add another JOIN to that table and adjust the CASE WHEN conditions to use the package type column.
• To directly populate the myreport table, wrap the query in an INSERT statement:

  INSERT INTO myreport (new_product_code, package_of_1, package_of_3, package_of_5)
  -- Paste the SELECT query above here
  

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