如何在MySQL中创建整合多表价格（表名为列名）的主表？

嘿，这个需求其实就是把多张结构一致的表横向合并成一张宽表对吧？咱们可以通过MySQL的JOIN操作来搞定，核心是得用所有表共有的维度列（Date、City、Country、Pin）来关联，不然只按Date匹配的话，很容易出现错误的笛卡尔积或者数据不对应。

方案1：内连接（适合各表维度数据完全匹配） #

如果所有表在相同的Date+City+Country+Pin组合下都有对应的Price数据，用内连接最直接：

CREATE TABLE main_table AS
SELECT 
    a.Date,
    a.Price AS A,
    b.Price AS B,
    c.Price AS C,
    d.Price AS D,
    e.Price AS E,
    f.Price AS F,
    a.City,
    a.Country,
    a.Pin
FROM A a
JOIN B b ON a.Date = b.Date 
         AND a.City = b.City 
         AND a.Country = b.Country 
         AND a.Pin = b.Pin
JOIN C c ON a.Date = c.Date 
         AND a.City = c.City 
         AND a.Country = c.Country 
         AND a.Pin = c.Pin
JOIN D d ON a.Date = d.Date 
         AND a.City = d.City 
         AND a.Country = d.Country 
         AND a.Pin = d.Pin
JOIN E e ON a.Date = e.Date 
         AND a.City = e.City 
         AND a.Country = e.Country 
         AND a.Pin = e.Pin
JOIN F f ON a.Date = f.Date 
         AND a.City = f.City 
         AND a.Country = f.Country 
         AND a.Pin = f.Pin;

代码说明： #

• CREATE TABLE main_table AS：直接创建主表并插入查询结果，如果只是需要临时查看结果，删掉这句直接运行SELECT即可。
• 用AS给各表的Price列起别名，对应表名A-F，这样结果列名就是你想要的A、B、C...F。
• 关联条件用了所有共有维度列，确保匹配的是同一地区同一日期的Price数据。

方案2：左连接（处理部分表缺数据的情况） #

如果有些表在某些维度组合下没有Price数据，用内连接会丢失这些行，这时候可以换成左连接，并用COALESCE填充缺失值：

CREATE TABLE main_table AS
SELECT 
    COALESCE(a.Date, b.Date, c.Date, d.Date, e.Date, f.Date) AS Date,
    COALESCE(a.Price, 0) AS A, -- 把NULL替换成0，也可以换成NULL保留空值
    COALESCE(b.Price, 0) AS B,
    COALESCE(c.Price, 0) AS C,
    COALESCE(d.Price, 0) AS D,
    COALESCE(e.Price, 0) AS E,
    COALESCE(f.Price, 0) AS F,
    COALESCE(a.City, b.City, c.City, d.City, e.City, f.City) AS City,
    COALESCE(a.Country, b.Country, c.Country, d.Country, e.Country, f.Country) AS Country,
    COALESCE(a.Pin, b.Pin, c.Pin, d.Pin, e.Pin, f.Pin) AS Pin
FROM A a
LEFT JOIN B b ON a.Date = b.Date 
             AND a.City = b.City 
             AND a.Country = b.Country 
             AND a.Pin = b.Pin
LEFT JOIN C c ON a.Date = c.Date 
             AND a.City = c.City 
             AND a.Country = c.Country 
             AND a.Pin = c.Pin
LEFT JOIN D d ON a.Date = d.Date 
             AND a.City = d.City 
             AND a.Country = d.Country 
             AND a.Pin = d.Pin
LEFT JOIN E e ON a.Date = e.Date 
             AND a.City = e.City 
             AND a.Country = e.Country 
             AND a.Pin = e.Pin
LEFT JOIN F f ON a.Date = f.Date 
             AND a.City = f.City 
             AND a.Country = f.Country 
             AND a.Pin = f.Pin;

代码说明： #

• LEFT JOIN会保留左表（这里是A表）的所有行，其他表没有匹配数据时，对应的Price会是NULL。
• COALESCE函数可以把NULL替换成你需要的值，比如0或者其他默认值。

方案3：全维度覆盖（保留所有表的所有数据） #

如果各表的维度组合不统一（比如A表有的日期城市组合，B表没有，反之亦然），可以先收集所有可能的维度组合，再左连接各表：

CREATE TABLE main_table AS
WITH all_dims AS (
    SELECT Date, City, Country, Pin FROM A
    UNION
    SELECT Date, City, Country, Pin FROM B
    UNION
    SELECT Date, City, Country, Pin FROM C
    UNION
    SELECT Date, City, Country, Pin FROM D
    UNION
    SELECT Date, City, Country, Pin FROM E
    UNION
    SELECT Date, City, Country, Pin FROM F
)
SELECT 
    ad.Date,
    COALESCE(a.Price, 0) AS A,
    COALESCE(b.Price, 0) AS B,
    COALESCE(c.Price, 0) AS C,
    COALESCE(d.Price, 0) AS D,
    COALESCE(e.Price, 0) AS E,
    COALESCE(f.Price, 0) AS F,
    ad.City,
    ad.Country,
    ad.Pin
FROM all_dims ad
LEFT JOIN A a ON ad.Date = a.Date 
             AND ad.City = a.City 
             AND ad.Country = a.Country 
             AND ad.Pin = a.Pin
LEFT JOIN B b ON ad.Date = b.Date 
             AND ad.City = b.City 
             AND ad.Country = b.Country 
             AND ad.Pin = b.Pin
LEFT JOIN C c ON ad.Date = c.Date 
             AND ad.City = c.City 
             AND ad.Country = c.Country 
             AND ad.Pin = c.Pin
LEFT JOIN D d ON ad.Date = d.Date 
             AND ad.City = d.City 
             AND ad.Country = d.Country 
             AND ad.Pin = d.Pin
LEFT JOIN E e ON ad.Date = e.Date 
             AND ad.City = e.City 
             AND ad.Country = e.Country 
             AND ad.Pin = e.Pin
LEFT JOIN F f ON ad.Date = f.Date 
             AND ad.City = f.City 
             AND ad.Country = f.Country 
             AND ad.Pin = f.Pin;

代码说明： #

• 用WITH子句创建临时表all_dims，通过UNION收集所有表的维度组合（自动去重）。
• 以all_dims为基础左连接所有表，这样就能保留所有可能的维度组合，不会丢失任何数据。

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