Laravel多表数据合并为带自增后缀单数组的优化方案咨询

一、SQL动态透视（纯数据库端自动生成字段） #

手动写120个字段太折腾，用动态SQL自动生成带自增后缀的透视字段，核心是给每条多记录行加序号，再转成对应列。

以MySQL为例，拿教育经历表举例：

1. 先给每个员工的教育记录加行号：

SELECT 
    emp_id,
    education_level,
    school_name,
    ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY start_date) AS row_num
FROM employee_education

2. 动态生成所有带后缀的字段，再关联基础表：

-- 获取单员工最多的教育记录数，确定要生成多少个后缀字段
SET @max_rows = (SELECT MAX(cnt) FROM (SELECT COUNT(*) AS cnt FROM employee_education GROUP BY emp_id) t);

-- 拼接字段列表，比如education_level_1、school_name_1这类
SET @cols = NULL;
SELECT GROUP_CONCAT(
    DISTINCT CONCAT(
        'MAX(CASE WHEN ee.row_num = ', row_num, ' THEN ee.education_level END) AS education_level_', row_num, ',',
        'MAX(CASE WHEN ee.row_num = ', row_num, ' THEN ee.school_name END) AS school_name_', row_num
    )
) INTO @cols
FROM (SELECT DISTINCT row_num FROM (SELECT ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY start_date) AS row_num FROM employee_education) t) t2;

-- 拼接完整查询语句，关联基础表
SET @sql = CONCAT(
    'SELECT e.*, ', @cols, ' 
     FROM employee_basic e
     LEFT JOIN (
         SELECT emp_id, education_level, school_name,
                ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY start_date) AS row_num
         FROM employee_education
     ) ee ON e.emp_id = ee.emp_id
     GROUP BY e.emp_id'
);

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

其他多记录表格（比如任职、奖惩）可以复制这套逻辑，把多个动态生成的字段列表拼到主SQL里就行，不用手动写每个字段。

二、应用层代码处理（灵活适配复杂场景） #

用Python/Java这类语言处理，比写SQL更直观，适合字段多的场景：

1. 分别查询基础表和多记录表
2. 在代码里把多记录转成带后缀的键值对，再合并到基础信息里

拿Python+pandas举例子：

import pandas as pd
import mysql.connector

# 连接数据库
conn = mysql.connector.connect(host='localhost', user='your_user', password='your_pwd', database='emp_db')

# 读取基础员工信息
basic_df = pd.read_sql('SELECT * FROM employee_basic', conn)

# 读取教育经历并加行号
edu_df = pd.read_sql('''
    SELECT emp_id, education_level, school_name,
           ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY start_date) AS row_num
    FROM employee_education
''', conn)

# 把教育经历转成带后缀的列
edu_pivot = edu_df.pivot(index='emp_id', columns='row_num', values=['education_level', 'school_name'])
edu_pivot.columns = [f'{col[0]}_{col[1]}' for col in edu_pivot.columns]
edu_pivot = edu_pivot.reset_index()

# 合并基础表和处理后的教育表
final_df = pd.merge(basic_df, edu_pivot, on='emp_id', how='left')

# 重复上述逻辑处理其他多记录表（任职、奖惩等）
# ...

# 导出到Excel
final_df.to_excel('employee_full_info.xlsx', index=False)

这种方式写个循环就能批量处理15张表，不用对着SQL字段发愁，空值还能直接用fillna处理。

三、ETL工具可视化配置（适合定期批量导出） #

如果是固定周期的导出需求，直接用ETL工具（比如Navicat的数据传输、Apache Airflow），这类工具支持可视化配置透视转换、多表合并，拖拖拽拽就能把多记录转成带后缀的字段，不用写代码，适合非开发人员或者批量处理场景。

注意事项 #

• 给多记录加行号时，要确定排序逻辑（比如教育经历按入学日期、任职按入职日期），保证后缀字段的顺序合理
• 动态SQL或代码会自动适配单员工的最大记录数，不用手动调整字段数量
• 应用层处理时，记得填充空值，避免导出Excel后出现大量空白

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