如何基于student-subject-marks表生成指定结果（分数不硬编码）

云上老周

2026-8-20

数据表转换解决方案

需求说明

需要将student-subject-marks的行式数据表（每行对应学生某一科目的分数）转换为列式结构（每列对应一个科目，每行对应学生的所有科目分数），且实现过程中不得硬编码科目或分数相关固定值。

原始数据表结构示例

student_id	student_name	subject	marks
1	Alice	Math	85
1	Alice	Science	90
1	Alice	English	78
2	Bob	Math	92
2	Bob	Science	88
2	Bob	English	80

目标数据表结构示例

student_id	student_name	Math	Science	English
1	Alice	85	90	78
2	Bob	92	88	80

实现方案（以SQL为例）

由于不能硬编码科目，需使用动态SQL自动识别所有科目并完成列转行操作。

MySQL 实现

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN subject = ''',
      subject,
      ''' THEN marks END) AS `',
      subject,
      '`'
    )
  ) INTO @sql
FROM student_subject_marks;

SET @sql = CONCAT('SELECT student_id, student_name, ', @sql, ' 
                  FROM student_subject_marks 
                  GROUP BY student_id, student_name');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

PostgreSQL 实现

-- 方式1：以JSON格式返回科目与分数
CREATE OR REPLACE FUNCTION pivot_student_marks()
RETURNS TABLE (student_id INT, student_name VARCHAR, subjects JSONB) AS $$
BEGIN
  RETURN QUERY
  SELECT 
    student_id,
    student_name,
    JSONB_OBJECT_AGG(subject, marks) AS subjects
  FROM student_subject_marks
  GROUP BY student_id, student_name;
END;
$$ LANGUAGE plpgsql;

-- 调用函数获取结果
SELECT * FROM pivot_student_marks();

-- 方式2：动态展开为列（PostgreSQL 11+ 支持）
DO $$
DECLARE
  cols TEXT;
BEGIN
  SELECT string_agg(DISTINCT quote_ident(subject), ', ') INTO cols
  FROM student_subject_marks;
  
  EXECUTE format('
    SELECT student_id, student_name, %s
    FROM student_subject_marks
    PIVOT (MAX(marks) FOR subject IN (%s)) AS p
  ', cols, cols);
END $$;