基于双列值生成日期并转置其他列的SQL实现问题

你的核心问题是转置时丢失了周数标识，导致生产和销售数据无法对应到同一周，同时缺少日期字段。下面是修正后的实现方案：

关键思路 #

1. 转置宽表时，为每一行添加对应的周编号（比如ProductionWeek1对应week_num=1），确保生产和销售数据能通过Product、Plant、week_num精准关联。
2. 利用周编号计算对应日期：首周为2001-01-01，后续每周递增7天。

完整SQL实现 #

方案1：直接生成最终表（无需中间表） #

CREATE TABLE FINAL_TABLE AS
SELECT
    a.Product,
    a.Plant,
    a.Production,
    b.Sales,
    -- 根据周编号计算日期，不同SQL方言函数略有差异
    -- MySQL 写法
    DATE_ADD('2001-01-01', INTERVAL (a.week_num - 1) * 7 DAY) AS WeekDate,
    -- PostgreSQL 写法：'2001-01-01'::DATE + (a.week_num - 1)*7 || ' days'::INTERVAL
    -- SQL Server 写法：DATEADD(day, (a.week_num - 1)*7, '2001-01-01')
    a.week_num AS WeekNumber
FROM
    -- 转置TableA，保留周编号
    (SELECT Product, Plant, ProductionWeek1 AS Production, 1 AS week_num FROM TableA
     UNION ALL
     SELECT Product, Plant, ProductionWeek2 AS Production, 2 AS week_num FROM TableA
     UNION ALL
     SELECT Product, Plant, ProductionWeek3 AS Production, 3 AS week_num FROM TableA
     UNION ALL
     SELECT Product, Plant, ProductionWeek4 AS Production, 4 AS week_num FROM TableA
     UNION ALL
     SELECT Product, Plant, ProductionWeek5 AS Production, 5 AS week_num FROM TableA
     UNION ALL
     SELECT Product, Plant, ProductionWeek6 AS Production, 6 AS week_num FROM TableA
     UNION ALL
     SELECT Product, Plant, ProductionWeek7 AS Production, 7 AS week_num FROM TableA
     UNION ALL
     SELECT Product, Plant, ProductionWeek8 AS Production, 8 AS week_num FROM TableA
     UNION ALL
     SELECT Product, Plant, ProductionWeek9 AS Production, 9 AS week_num FROM TableA
     UNION ALL
     SELECT Product, Plant, ProductionWeek10 AS Production, 10 AS week_num FROM TableA
     UNION ALL
     SELECT Product, Plant, ProductionWeek11 AS Production, 11 AS week_num FROM TableA) a
JOIN
    -- 转置TableB，保留周编号
    (SELECT Product, Plant, SalesWeek1 AS Sales, 1 AS week_num FROM TableB
     UNION ALL
     SELECT Product, Plant, SalesWeek2 AS Sales, 2 AS week_num FROM TableB
     UNION ALL
     SELECT Product, Plant, SalesWeek3 AS Sales, 3 AS week_num FROM TableB
     UNION ALL
     SELECT Product, Plant, SalesWeek4 AS Sales, 4 AS week_num FROM TableB
     UNION ALL
     SELECT Product, Plant, SalesWeek5 AS Sales, 5 AS week_num FROM TableB
     UNION ALL
     SELECT Product, Plant, SalesWeek6 AS Sales, 6 AS week_num FROM TableB
     UNION ALL
     SELECT Product, Plant, SalesWeek7 AS Sales, 7 AS week_num FROM TableB
     UNION ALL
     SELECT Product, Plant, SalesWeek8 AS Sales, 8 AS week_num FROM TableB
     UNION ALL
     SELECT Product, Plant, SalesWeek9 AS Sales, 9 AS week_num FROM TableB
     UNION ALL
     SELECT Product, Plant, SalesWeek10 AS Sales, 10 AS week_num FROM TableB
     UNION ALL
     SELECT Product, Plant, SalesWeek11 AS Sales, 11 AS week_num FROM TableB) b
ON
    a.Product = b.Product
    AND a.Plant = b.Plant
    AND a.week_num = b.week_num;

方案2：先创建中间表（如果需要复用转置结果） #

如果后续需要多次使用转置后的生产/销售数据，可以先创建带周编号的中间表：

-- 创建TableA转置表
CREATE TABLE TABLE_A1 AS
SELECT Product, Plant, ProductionWeek1 AS Production, 1 AS week_num FROM TableA
UNION ALL
SELECT Product, Plant, ProductionWeek2 AS Production, 2 AS week_num FROM TableA
... -- 依次补充到week11的查询

-- 创建TableB转置表
CREATE TABLE TABLE_B1 AS
SELECT Product, Plant, SalesWeek1 AS Sales, 1 AS week_num FROM TableB
UNION ALL
SELECT Product, Plant, SalesWeek2 AS Sales, 2 AS week_num FROM TableB
... -- 依次补充到week11的查询

-- 生成最终表
CREATE TABLE FINAL_TABLE AS
SELECT
    a.Product,
    a.Plant,
    a.Production,
    b.Sales,
    DATE_ADD('2001-01-01', INTERVAL (a.week_num - 1)*7 DAY) AS WeekDate,
    a.week_num AS WeekNumber
FROM TABLE_A1 a
JOIN TABLE_B1 b
ON a.Product = b.Product AND a.Plant = b.Plant AND a.week_num = b.week_num;

对原代码问题的说明 #

• 原代码用UNION而非UNION ALL：UNION会自动去重并排序，不仅效率低，还破坏了原始数据的周对应关系。
• 未保留周编号：转置后无法区分数据属于第几周，关联时只能靠Product匹配，导致不同周的生产/销售数据错误关联。
• 缺少日期计算：通过week_num可以轻松推导对应周的起始日期，解决排序和日期展示需求。

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