数据表错误数据识别请求：跨地区学生校属关联异常排查

我来帮你搞定这个错误数据识别的问题！先明确咱们的场景：数据库里有四张核心表——Student、School、School_Student、Location，表间关系是：一个Location包含多所School；学生允许在同一Location的不同School就读，但现在出现了同名但ID不同的Student被错误分配到不同Location的School的情况，比如你提到的‘Adam Mike’就存在这类问题。

下面我给你一套具体的SQL查询方案，帮你快速定位所有这类错误数据：

第一步：先获取每个学生对应的Location信息 #

首先咱们把所有学生和他们就读学校所属的Location关联起来，方便后续排查：

SELECT 
    s.student_id,
    s.student_name,
    l.location_id,
    l.location_name
FROM 
    Student s
JOIN 
    School_Student ss ON s.student_id = ss.student_id
JOIN 
    School sch ON ss.school_id = sch.school_id
JOIN 
    Location l ON sch.location_id = l.location_id

第二步：筛选出同名且跨Location的学生 #

基于上面的关联结果，咱们可以按学生姓名分组，统计每个姓名对应的不同Location数量——如果数量大于1，就说明这个姓名下的学生存在跨Location的错误分配：

WITH StudentLocations AS (
    SELECT 
        s.student_id,
        s.student_name,
        l.location_id
    FROM 
        Student s
    JOIN 
        School_Student ss ON s.student_id = ss.student_id
    JOIN 
        School sch ON ss.school_id = sch.school_id
    JOIN 
        Location l ON sch.location_id = l.location_id
)
SELECT 
    student_name,
    COUNT(DISTINCT location_id) AS distinct_location_count,
    GROUP_CONCAT(DISTINCT location_id SEPARATOR ', ') AS location_ids,
    GROUP_CONCAT(student_id SEPARATOR ', ') AS related_student_ids
FROM 
    StudentLocations
GROUP BY 
    student_name
HAVING 
    COUNT(DISTINCT location_id) > 1;

这个查询会返回所有有问题的学生姓名、对应的不同Location数量、涉及的Location ID以及相关的Student ID——比如针对‘Adam Mike’，你能直接看到他名下的所有ID和对应的Location，快速锁定错误点。

第三步：查看错误数据的详细明细 #

如果需要更具体的分配情况（比如每个学生具体在哪个Location的哪所学校），可以用下面的查询：

WITH StudentLocations AS (
    SELECT 
        s.student_id,
        s.student_name,
        sch.school_name,
        l.location_name,
        l.location_id
    FROM 
        Student s
    JOIN 
        School_Student ss ON s.student_id = ss.student_id
    JOIN 
        School sch ON ss.school_id = sch.school_id
    JOIN 
        Location l ON sch.location_id = l.location_id
),
ProblemNames AS (
    SELECT 
        student_name
    FROM 
        StudentLocations
    GROUP BY 
        student_name
    HAVING 
        COUNT(DISTINCT location_id) > 1
)
SELECT 
    sl.student_id,
    sl.student_name,
    sl.school_name,
    sl.location_name
FROM 
    StudentLocations sl
JOIN 
    ProblemNames pn ON sl.student_name = pn.student_name
ORDER BY 
    sl.student_name, sl.student_id;

这个结果会把所有存在问题的学生的具体就读学校和Location列出来，方便你逐一核对修正。

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