SQL如何基于关联表同组值批量填充RegNumber字段

实现方案 #

场景1：直接查询得到目标结果（SQL） #

核心逻辑是先关联两表拿到「课程+人员」分组和对应登记号的映射关系，再关联表1的全量ID补全所有取值：

SELECT 
    t1.ID,
    t_group.RegNumber
FROM 表1 t1
INNER JOIN (
    -- 先拿到每个课程+人员分组对应的唯一登记号
    SELECT 
        t1_inner.Course,
        t1_inner.Person,
        t2_inner.RegNumber
    FROM 表1 t1_inner
    LEFT JOIN 表2 t2_inner ON t1_inner.ID = t2_inner.ID
    WHERE t2_inner.RegNumber IS NOT NULL
    GROUP BY t1_inner.Course, t1_inner.Person, t2_inner.RegNumber
) t_group 
ON t1.Course = t_group.Course AND t1.Person = t_group.Person

场景2：直接修改现有表2，补全缺失数据（SQL） #

先清理表2里的无效空行，再插入缺失的ID和对应登记号：

1. 清理占位空行

DELETE FROM 表2 WHERE ID IS NULL OR RegNumber IS NULL;

2. 插入缺失数据

INSERT INTO 表2 (ID, RegNumber)
SELECT 
    t1.ID,
    t_group.RegNumber
FROM 表1 t1
INNER JOIN (
    SELECT 
        t1_inner.Course,
        t1_inner.Person,
        t2_inner.RegNumber
    FROM 表1 t1_inner
    JOIN 表2 t2_inner ON t1_inner.ID = t2_inner.ID
) t_group ON t1.Course = t_group.Course AND t1.Person = t_group.Person
WHERE t1.ID NOT IN (SELECT ID FROM 表2)

场景3：用Pandas处理数据 #

如果是在Python里做数据清洗，逻辑更简单：

import pandas as pd
# 先关联两表
df_merge = pd.merge(df1, df2, on='ID', how='left')
# 按课程+人员分组，用组内非空的登记号填充所有空值
df_merge['RegNumber'] = df_merge.groupby(['Course', 'Person'])['RegNumber'].transform('first')
# 提取得到目标表2
df_result = df_merge[['ID', 'RegNumber']]

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