MySQL 8动态透视表查询：食品营养数据转置需求实现

需求概述 #

基于三张动态数据表实现两种透视查询，需满足：

• 缺失的食品/营养组合显示短横线-
• 食品数据动态变化，每次仅显示15个食品（支持分页）
  两种透视需求：

1. 以营养成分为左侧列，食品名称为表头行（每行对应一种营养，每列对应一种食品）
2. 以食品名称为左侧列，营养成分为表头行（每行对应一种食品，每列对应一种营养，按营养成分分组）

表结构与示例数据 #

DDL语句 #

CREATE TABLE food (
 food_id int,
 description text  
);
CREATE TABLE food_nutrient (
 food_id int,
 nutrient_id int,
 amount double
);
CREATE TABLE nutrient (
 nutrient_id int DEFAULT NULL,
 name text
);

示例数据插入 #

-- 插入食品数据
insert into food(food_id, description)
values
  (1, 'cookies'),
  (2, 'coffee'),
  (3, 'tea');

-- 插入营养成分数据
insert into nutrient(nutrient_id, name)
values
 (10, 'vitamin b'),
 (11, 'vitamin c'),
 (12, 'water'),
 (15, 'sugar'),
 (22, 'fatty acid'),
 (33, 'sodium');        

-- 插入食品-营养关联数据
insert into food_nutrient(food_id, nutrient_id, amount)
values
  (1, 11, 1),
  (1, 12, 2),   
  (1, 33, 3),   
  (2, 12, 10),
  (2, 33, 2),
  (3, 12, 15),
  (3, 15, 8);

原始关联查询结果 #

Description |   Name        |   Amount
---------------------------------------
cookies     |   vitamin c   |   1
cookies     |   water       |   2
cookies     |   sodium      |   3
coffee      |   water       |   10
coffee      |   sodium      |   2
tea         |   water       |   15
tea         |   sugar       |   8

实现1：营养成分左列，食品名称为表头 #

通过动态SQL生成表头列，处理缺失值并支持分页：

-- 定义分页参数：每页15个食品，此处为第1页（OFFSET 0）
SET @page = 1;
SET @page_size = 15;

-- 生成当前页的食品表头列
SET @sql_cols = (
  SELECT GROUP_CONCAT(DISTINCT
    CONCAT(
      "COALESCE(MAX(CASE WHEN f.description = '", f.description, "' THEN fn.amount END), '-') AS `", f.description, "`"
    )
  )
  FROM (
    SELECT description FROM food ORDER BY food_id LIMIT @page_size OFFSET (@page - 1)*@page_size
  ) f
);

-- 拼接完整查询SQL
SET @sql = CONCAT(
  "SELECT n.name AS Nutrients, ", @sql_cols, "
  FROM nutrient n
  LEFT JOIN food_nutrient fn ON n.nutrient_id = fn.nutrient_id
  LEFT JOIN food f ON fn.food_id = f.food_id
  GROUP BY n.nutrient_id, n.name
  ORDER BY n.nutrient_id;"
);

-- 执行动态SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

关键说明 #

• 使用LEFT JOIN确保所有营养成分都被列出，即使无对应食品数据
• COALESCE(MAX(CASE...), '-')将缺失值转为-
• 分页子查询控制每次显示的食品数量，按food_id排序保证分页顺序稳定

实现2：食品名称左列，营养成分为表头（按营养分组） #

同样通过动态SQL实现，以食品为左列，营养为表头：

-- 定义分页参数
SET @page = 1;
SET @page_size = 15;

-- 生成所有营养成分的表头列
SET @sql_cols = (
  SELECT GROUP_CONCAT(DISTINCT
    CONCAT(
      "COALESCE(MAX(CASE WHEN n.name = '", n.name, "' THEN fn.amount END), '-') AS `", n.name, "`"
    )
  )
  FROM nutrient n
);

-- 拼接完整查询SQL，分页显示食品
SET @sql = CONCAT(
  "SELECT f.description AS Food_Name, ", @sql_cols, "
  FROM (
    SELECT food_id, description FROM food ORDER BY food_id LIMIT @page_size OFFSET (@page - 1)*@page_size
  ) f
  LEFT JOIN food_nutrient fn ON f.food_id = fn.food_id
  LEFT JOIN nutrient n ON fn.nutrient_id = n.nutrient_id
  GROUP BY f.food_id, f.description
  ORDER BY f.food_id;"
);

-- 执行动态SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

关键说明 #

• 动态列基于全部营养成分生成，保证表头包含所有营养项
• 分页子查询控制每次显示的食品数量，LEFT JOIN确保无营养数据的食品也能被列出

现有代码修正说明 #

你提供的代码存在以下问题，修正后可正常运行：

1. 表名错误：代码中使用food1等不存在的表名，需替换为实际表名food/food_nutrient/nutrient
2. 字段名错误：food表的食品名称字段为description（非food_name），nutrient表的营养名称字段为name（非nutrient_name）
3. 缺失值处理：原代码用IFNULL(...,0)，需替换为COALESCE(..., '-')以符合需求
4. 关联逻辑错误：原代码用JOIN会过滤掉无对应食品的营养成分，需改为LEFT JOIN

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