如何将单表列拆分后关联至另一表生成多列？（食品表示例）

咱先把你的表结构和数据明确下，方便后续讲解：

主表：FoodTable #

属性表：FoodAttributeTable #

你的核心需求是把键值对格式的属性表转换成扁平化的多列结构，再和主表关联，生成包含所有属性列的宽表。下面分不同SQL数据库给出具体实现：

1. MySQL（无原生PIVOT，用CASE WHEN + GROUP BY） #

MySQL没有内置的行转列函数，我们可以用CASE WHEN匹配属性名，再通过GROUP BY聚合得到每个食物的属性值：

SELECT 
  ft.Food,
  ft.Price,
  -- 匹配Vitamin属性，没有则返回NULL
  MAX(CASE WHEN fat.AttributeName = 'Vitamin' THEN fat.AttributeValue END) AS Vitamin,
  -- 匹配Weight属性
  MAX(CASE WHEN fat.AttributeName = 'Weight' THEN fat.AttributeValue END) AS Weight,
  -- 处理特殊命名的属性（比如...），需要用反引号转义
  MAX(CASE WHEN fat.AttributeName = '...' THEN fat.AttributeValue END) AS `...`
  -- 可以继续添加更多属性的CASE语句
FROM FoodTable ft
-- 用LEFT JOIN保证即使没有属性的食物（比如Broccoli）也能保留在结果中
LEFT JOIN FoodAttributeTable fat ON ft.Food = fat.Food
GROUP BY ft.Food, ft.Price;

说明：MAX()函数用来过滤掉每个分组中的NULL值，确保每个属性只保留对应食物的有效值。

2. SQL Server（原生PIVOT函数） #

SQL Server支持PIVOT语法，写法更简洁：

SELECT 
  Food,
  Price,
  Vitamin,
  Weight,
  -- 特殊属性名用方括号转义
  [--] AS `...`
FROM (
  -- 先关联两张表，得到基础数据集
  SELECT 
    ft.Food,
    ft.Price,
    fat.AttributeName,
    fat.AttributeValue
  FROM FoodTable ft
  LEFT JOIN FoodAttributeTable fat ON ft.Food = fat.Food
) AS SourceData
-- 对AttributeName进行行转列，聚合AttributeValue的值
PIVOT (
  MAX(AttributeValue)
  FOR AttributeName IN (Vitamin, Weight, [--]) -- 这里列出所有要转成列的属性名
) AS PivotedData;

3. PostgreSQL（crosstab函数，动态支持多属性） #

PostgreSQL需要借助tablefunc扩展的crosstab函数，适合属性较多或动态新增的场景：

-- 先启用tablefunc扩展（首次使用需要执行）
CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT 
  *
FROM crosstab(
  -- 第一步：获取关联后的基础数据，必须按Food排序
  'SELECT ft.Food, ft.Price, fat.AttributeName, fat.AttributeValue
   FROM FoodTable ft
   LEFT JOIN FoodAttributeTable fat ON ft.Food = fat.Food
   ORDER BY 1,2',
  -- 第二步：动态获取所有属性名（也可以手动指定）
  'SELECT DISTINCT AttributeName FROM FoodAttributeTable ORDER BY 1'
) AS ResultTable(
  Food VARCHAR, 
  Price INT, 
  Vitamin VARCHAR, 
  Weight VARCHAR, 
  "..." VARCHAR -- 对应属性名...的列
);

关键注意事项 #

• 动态属性处理：如果属性是动态新增的，静态的CASE WHEN或PIVOT就不适用了，需要用动态SQL生成语句（比如MySQL的PREPARE，SQL Server的EXEC）
• 数据类型统一：如果不同属性的值类型不同（比如Weight是数值，Vitamin是字符串），转列后可能需要统一类型或单独处理
• 关联方式选择：如果只需要保留有属性的食物，把LEFT JOIN换成INNER JOIN即可

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