Quelle est la bonne façon de joindre deux instructions de sélection mysql complexes ?

Question

Je peux extraire deux listes de ma table. Toutes les unités pour chaque étudiant de chaque cohorte sont affichées. L'autre indique si toutes les sections de chaque unité ont été soumises au travail d'un étudiant spécifique dans un groupe spécifique. Je souhaite rejoindre la liste afin de pouvoir visualiser les soumissions (ou non-soumissions) pour chaque section de chaque unité pour chaque étudiant de chaque cohorte.

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

Cela affichera une liste d'organisations avec des identifiants d'utilisateur et des identifiants de groupe.

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

Si le devoir comporte plus de parties que celles qui ont été soumises, cette opération renverra une ligne pour chaque unité que chaque étudiant de chaque cohorte doit terminer en fonction de l'ID de cohorte, de l'ID d'utilisateur et du nom de l'unité.

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;

Comment utiliser les trois colonnes de la première instruction select comme informations de connexion pour intégrer la deuxième instruction select à la première instruction select ?

Je souhaite exécuter une deuxième requête sur chaque résultat de la première requête. En utilisant les données ci-dessus, je souhaite extraire l'ID utilisateur 21 car ils n'ont soumis qu'une partie de l'unité en deux parties.

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

Est-ce une participation ? Ou une sous-requête ?

Answer 1

(Pour ce que ça vaut, je crois que groupe est un terme accepté dans diverses disciplines des sciences sociales.)

Votre problème sera plus facile si vos assignments 表有一个与您的 cohort_unitspart 表相匹配的 part colonnes sont Code>Table. Commençons donc par une sous-requête et générons une table factice contenant la colonne.

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)

Nous utiliserons plutôt cette sous-requête 赋值. C'est un peu compliqué, mais cela rendra les travaux futurs plus clairs.

Ensuite, nous avons besoin du nombre de pièces dans chaque unité. Voici une agrégation simple :

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

Nous pouvons utiliser des expressions de table courantes pour organiser les requêtes comme indiqué ci-dessous.

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

C'est un violon. https://dbfiddle.uk/FvGkiAnl

L'une des astuces pour y parvenir est de regrouper individuellement pour obtenir le nombre de pièces.