Was ist der richtige Weg, zwei komplexe MySQL-Select-Anweisungen zu verbinden?

Question

Ich kann zwei Listen von meinem Tisch hervorholen. Alle Einheiten für jeden Schüler in jeder Kohorte werden angezeigt. Das andere zeigt, ob alle Abschnitte jeder Einheit zur Arbeit für einen bestimmten Schüler in einer bestimmten Gruppe eingereicht wurden. Ich möchte der Liste beitreten, damit ich die Einreichungen (oder Nichteinreichungen) für jeden Abschnitt jeder Einheit für jeden Studenten in jeder Kohorte sehen kann.

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

Dadurch wird eine Liste von Organisationen mit Benutzer-IDs und Gruppen-IDs abgerufen.

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

Wenn die Aufgabe mehr Teile enthält, als eingereicht wurden, gibt dieser Vorgang eine Zeile für jede Einheit zurück, die jeder Schüler in jeder Kohorte unter Berücksichtigung der Kohorten-ID, der Benutzer-ID und des Einheitennamens abschließen sollte.

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;

Wie verwende ich die drei Spalten der ersten Select-Anweisung als Verbindungsinformationen, um die zweite Select-Anweisung zu einem Teil der ersten Select-Anweisung zu machen?

Ich möchte für jedes Ergebnis der ersten Abfrage eine zweite Abfrage ausführen. Anhand der oben genannten Daten möchte ich die Benutzer-ID 21 extrahieren, da sie nur einen Teil der zweiteiligen Einheit übermittelt hat.

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

Ist das ein Beitritt? Oder Unterabfrage?

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

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