连接两个复杂的 mysql select 语句的正确方法是什么？

Question

我可以从我的表格中取出两个列表。其中显示了每个队列中每个学生的所有单元。另一个显示是否已针对特定群体中特定学生的作业提交每个单元的所有部分。我想加入列表，以便我可以查看每个队列中每个学生的每个单元的每个部分的提交（或未提交）。

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

这会提取带有用户 ID 和群组 ID 的单位列表。

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;

result:
user_id    unit    cohort_id
21         ABC    235
24         ABC    235
43         ABC    235
53         DEF    236

如果作业的部分多于已提交的部分，则对于每个队列中的每个学生应在给定队列 ID、用户 ID 和单元名称的情况下完成的每个单元，此操作将返回一行。

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;

如何使用第一个 select 语句上的三列作为连接信息，使第二个 select 语句成为第一个 select 语句的一部分？

我想对第一个查询的每个结果运行第二个查询。使用上面的数据，我希望提取出用户 ID 21，因为他们只提交了两部分单元的一部分。

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

这是一个加入吗？或者子查询？

Answer 1

（就其价值而言，我相信群体是各个社会科学学科中公认的术语。）

如果您的 assignments 表有一个与您的 cohort_unitspart 表相匹配的 part 列，您的问题将会变得更容易代码>表。因此，让我们从子查询开始，生成一个包含该列的虚拟表。

SELECT assignments.user_id, assignments.cohort_id, 
       cohort_units.unit, cohort_units.part,
       assignments.draft1recdt
  FROM assignments
  JOIN cohort_units 
     ON assignments.unit = CONCAT(cohort_units.unit, cohort_units.part)

我们将使用此子查询来代替赋值。这有点杂乱，但它会让以后的工作更加清晰。

接下来我们需要每个单元中的零件数量。这是一个简单的聚合：

SELECT COUNT(*) num_parts,
       cohort_id,
       unit
  FROM cohort_units
 GROUP BY cohort_id, unit

我们可以使用通用表表达式来组织查询，如下所示。

WITH completed AS (
 SELECT assignments.user_id, assignments.cohort_id, 
       cohort_units.unit, cohort_units.part,
       assignments.draft1recdt
  FROM assignments
  JOIN cohort_units 

      ON assignments.unit = CONCAT(cohort_units.unit, cohort_units.part) 
),
partcount AS (
SELECT COUNT(*) num_parts,
       cohort_id,
       unit
  FROM cohort_units
 GROUP BY cohort_id, unit
)
SELECT completed.user_id, cohort_units.cohort_id, cohort_units.unit, 
       GROUP_CONCAT(completed.part) parts,
       COUNT(*) completed_parts,
       partcount.num_parts
  FROM cohort_units
  JOIN partcount
         ON cohort_units.cohort_id = partcount.cohort_id
        AND cohort_units.unit = partcount.unit
  JOIN completed
         ON completed.cohort_id = cohort_units.cohort_id
        AND completed.unit = cohort_units.unit
        AND completed.part = cohort_units.part
GROUP BY completed.user_id, cohort_units.cohort_id, cohort_units.unit, num_parts
HAVING COUNT(*) < partcount.num_parts

这是一个小提琴。 https://dbfiddle.uk/FvGkiAnl

实现这一点的技巧之一是单独聚合来获取零件计数。