如何关联两个复杂MySQL SELECT语句？学生作业提交场景问询

我需要从三张数据表生成两个关联的列表：

1. 展示每个 cohort 中每个学生对应的所有 unit
2. 判断特定学生在特定 cohort 的特定 unit 中是否存在未提交的作业部分（当作业总部分数大于已提交数时返回结果）

最终要将两个列表关联，查看每个 cohort 中每个学生的各 unit 作业提交情况。

数据表结构 #

cohort_units:
cohort_id    unit    part
235          ABC     A
235          ABC     B
246          DEF     A
246          DEF     B
246          DEF     C

cohort_students:
user_id      cohort_id
21           235
24           235
43           235
53           246

assignments:
user_id    cohort_id    unit     draft1recdt 
21         235          ABCA     2023-01-03
21         235          ABCB     NULL
24         235          ABCA     2023-02-01
24         235          ABCB     2023-02-02  

原查询语句 #

第一个查询：获取学生-Unit-Cohort关联列表 #

SELECT cohort_students.user_id,
       cohort_units.unit,
       cohort_units.cohort_id 
FROM cohort_units 
LEFT JOIN cohort_students 
    ON cohort_units.cohort_id = cohort_students.cohort_id 
GROUP BY cohort_units.unit,cohort_students.user_id 
ORDER BY cohort_students.user_id;

查询结果：

user_id    unit    cohort_id
21         ABC     235
24         ABC     235
43         ABC     235
53         DEF     246

第二个查询：判断单个学生的未提交作业情况 #

SELECT GROUP_CONCAT(CASE WHEN draft1recdt IS NOT NULL THEN draft1recdt END) AS drafts,
       (LENGTH(GROUP_CONCAT(DISTINCT draft1recdt))-LENGTH(REPLACE(GROUP_CONCAT(DISTINCT draft1recdt), ',', '')))+1 as numDrafts,
       cohort_units.unit,
       GROUP_CONCAT(cohort_units.part) as parts,
       (LENGTH(GROUP_CONCAT(DISTINCT cohort_units.part))-LENGTH(REPLACE(GROUP_CONCAT(DISTINCT cohort_units.part), ',', '')))+1 as numParts 
FROM assignments 
LEFT JOIN cohort_units 
    ON assignments.cohort_id = cohort_units.cohort_id 
    AND assignments.unit = CONCAT(cohort_units.unit,cohort_units.part) 
WHERE assignments.cohort_id = 235 
    AND cohort_units.unit = 'ABC' AND assignments.user_id = 21 
GROUP BY cohort_units.unit 
HAVING numParts > numDrafts;

核心疑问 #

如何将第二个查询的逻辑整合到第一个查询中，以第一个查询的 user_id、unit、cohort_id 作为关联条件？希望对第一个查询的每条结果执行第二个查询的逻辑，最终返回类似如下的结果：

user_id    unit   cohort_id   parts   numParts   numDrafts
21         ABC    235         A,B     2          1

应该使用 JOIN 还是子查询实现？

可以用子查询结合 JOIN的方式实现，把第二个查询的逻辑封装成关联子查询，关联第一个查询的三个核心字段（user_id、unit、cohort_id），同时通过 HAVING 过滤出存在未提交作业的记录。

完整SQL语句 #

SELECT 
    base.user_id,
    base.unit,
    base.cohort_id,
    sub.parts,
    sub.numParts,
    sub.numDrafts
FROM (
    -- 第一个查询作为基础数据集
    SELECT cs.user_id,
           cu.unit,
           cu.cohort_id 
    FROM cohort_units cu
    LEFT JOIN cohort_students cs
        ON cu.cohort_id = cs.cohort_id 
    GROUP BY cu.unit, cs.user_id 
) AS base
LEFT JOIN (
    -- 封装第二个查询的逻辑，去掉固定条件，改为关联字段
    SELECT 
        a.user_id,
        cu.unit,
        cu.cohort_id,
        GROUP_CONCAT(cu.part) AS parts,
        (LENGTH(GROUP_CONCAT(DISTINCT cu.part)) - LENGTH(REPLACE(GROUP_CONCAT(DISTINCT cu.part), ',', ''))) + 1 AS numParts,
        COUNT(DISTINCT CASE WHEN a.draft1recdt IS NOT NULL THEN a.unit END) AS numDrafts
    FROM assignments a
    LEFT JOIN cohort_units cu
        ON a.cohort_id = cu.cohort_id 
        AND a.unit = CONCAT(cu.unit, cu.part)
    GROUP BY a.user_id, cu.unit, cu.cohort_id
    HAVING numParts > numDrafts
) AS sub 
    ON base.user_id = sub.user_id 
    AND base.unit = sub.unit 
    AND base.cohort_id = sub.cohort_id
WHERE sub.user_id IS NOT NULL; -- 只保留存在未提交作业的记录

逻辑说明 #

1. 基础数据集：第一个查询被封装成 base 子查询，生成所有学生与对应unit、cohort的关联列表。
2. 作业统计子查询：把第二个查询的固定条件（比如特定user_id、cohort_id、unit）去掉，改为按user_id、unit、cohort_id分组，统计每个学生每个unit的总作业部分数numParts和已提交部分数numDrafts，并用HAVING numParts > numDrafts过滤出有未提交作业的记录。
   • 这里把原numDrafts的计算方式简化为COUNT(DISTINCT CASE WHEN a.draft1recdt IS NOT NULL THEN a.unit END)，逻辑更清晰，避免字符串处理的潜在问题。
3. 关联过滤：将base和sub通过三个核心字段关联，最后用WHERE sub.user_id IS NOT NULL只保留存在未提交作业的学生记录，符合需求结果。

执行结果 #

针对提供的测试数据，执行上述SQL会返回：

user_id    unit   cohort_id   parts   numParts   numDrafts
21         ABC    235         A,B     2          1

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