Comment compter les valeurs distinctes de deux colonnes en un seul nombre (suite)

Question

Il s'agit d'une question de suivi de Comment compter les valeurs distinctes de deux colonnes en un seul nombre ?

Je voulais savoir comment faire la partie comptage et j'ai négligé le fait que je suis déjà en train de joindre d'autres tables dans le mélange.

La réponse donnée à la question précédente est la bonne dans ce cas.

Voici maintenant mon problème supplémentaire.

J'ai 3 tables :

Assignations

+----+-------------------+
| id |       name        |
+----+-------------------+
| 1  | first-assignment  |
| 2  | second-assignment |
+----+-------------------+

Soumissions

+----+---------------+------------+
| id | assignment_id | student_id |
+----+---------------+------------+
|  1 |             1 |          2 |
|  2 |             2 |          1 |
|  3 |             1 |          3 |
+----+---------------+------------+

Soumissions du groupe

+----+---------------+------------+
| id | submission_id | student_id |
+----+---------------+------------+
| 1  |             1 |          1 |
| 2  |             2 |          2 |
+----+---------------+------------+

Chaque soumission appartient à une mission.

Les soumissions peuvent être individuelles ou collectives.

Quand ils sont individuels, celui qui a fait la soumission dans un devoir (assignment_id) va dans la table des soumissions (student_id).

Lorsqu'ils sont soumis à un groupe, la même chose se produit avec deux détails supplémentaires :

1. Celui qui fait la soumission va dans la table des soumissions.
2. Les autres vont dans la table group_submissions et sont associés à l'id dans la table submissions (donc submission_id est un FK de la table submissions).

Je veux renvoyer chaque devoir avec ses colonnes, mais aussi ajouter le nombre d'étudiants qui ont fait des soumissions pour ce devoir. Gardez à l'esprit que les étudiants qui n'ont pas fait de soumission (ne sont pas dans la table des soumissions) mais qui ont participé à une soumission de groupe (sont dans la table des soumissions de groupe) comptent également.

Quelque chose comme ça :

+----+-------------------+----------+
| id |       name        | students |
+----+-------------------+----------+
| 1  | first-assignment  |       11 |
| 2  | second-assignment |        2 |
+----+-------------------+----------+

J'ai essayé deux façons d'obtenir les chiffres :

count(distinct case when group_submissions.student_id is not null then
group_submissions.student_id when assignment_submissions.student_id is
not null then assignment_submissions.student_id end)

Cela ne fonctionne pas, car l'instruction case sera court-circuitée dès que la première condition sera remplie. Par exemple : Si un étudiant a fait des soumissions de groupe mais n'a jamais réellement fait la soumission, il sera affiché dans la table des soumissions de groupe uniquement. Ainsi, si dans la table des soumissions, l'identifiant est 1 et dans la table des soumissions de groupe, l'identifiant est 2, et que l'identifiant 2 n'apparaît pas dans la table des soumissions, il ne sera pas compté.

count(distinct case when group_submissions.student_id is not null then group_submissions.student_id end) 
+ count(distinct case when submissions.student_id is not null then submissions.student_id end)

Celui-ci ne fonctionne pas car il donne des doublons si un étudiant est dans les deux tableaux.

NOTE : Il s'agit d'une base de données MySQL

Answer 1

Comme les étudiants sont soit en submissions ou dans group_submissions vous pouvez simplement joindre les tables et ajouter les colonnes :

SELECT a.id,COUNT(s.student_id)+COUNT(gs.student_id) FROM assignments a
JOIN submissions s ON a.id = s.assignment_id
LEFT JOIN group_submissions gs ON s.id = gs.submission_id
GROUP BY a.id;

S'il y a des doublons, c'est-à-dire que l'étudiant peut être à la fois dans submissions y group_submissions vous pouvez alors réunir les deux et sélectionner à partir de là :

SELECT assignment_id,COUNT(DISTINCT student_id)
FROM (
    SELECT assignment_id,student_id
    FROM submissions
    UNION
    SELECT assignment_id,gs.student_id
    FROM group_submissions gs
        JOIN submissions s on gs.submission_id = s.id) T1
GROUP BY assignment_id;

Answer 2

Puisque vous ne pouvez pas modifier les données, vous devrez utiliser une sous-requête UNION, puis agréger sur cette dernière.

SELECT a.id, a.name, COUNT(DISTINCT x.student_id) AS students
FROM Assignments AS a
LEFT JOIN (
   SELECT assignment_id, student_id FROM Submissions
   UNION 
   SELECT s.assignment_id, g.student_id
   FROM Submissions AS s
   INNER JOIN Group_submissions AS g ON s.id = g.submission_id
) AS x ON a.id = x.assignment_id
GROUP BY a.id, a.name
;

Edit : la première partie de vhu est meilleure tant que vous ne pouvez pas avoir le devoir X soumis par l'étudiant Y avec un crédit de groupe_submission de l'étudiant Z, et un autre pour le devoir X soumis directement par l'étudiant Z ou ayant un crédit de groupe_submission de l'étudiant Y (car alors ils seraient comptés deux fois).

Answer 3

Vous avez déjà marqué la question comme mysqk, le numéro de version est généralement intéressant pour une bonne réponse.

Le texte suivant vous donne une réponse correcte

SELECT  
  a.id,a.name
  , LENGTH(CONCAT(GROUP_CONCAT(s.`student_id`) ,IF(GROUP_CONCAT(gs.student_id) is NULL,'',','),IF(GROUP_CONCAT(gs.student_id) is NULL,'',GROUP_CONCAT(gs.student_id))))
   - LENGTH(REPLACE(CONCAT(GROUP_CONCAT(s.`student_id`) ,IF(GROUP_CONCAT(gs.student_id) is NULL,'',','),IF(GROUP_CONCAT(gs.student_id) is NULL,'',GROUP_CONCAT(gs.student_id))), ',', '')) + 1 as count_studints
FROM 
  Submissions s 
  LEFT JOIN Group_submissions gs ON gs.submission_id = s.id 
  INNER JOIN Assignments a on s.assignment_id = a.id
WHERE s.`student_id` NOT IN (SELECT student_id 
                           FROM Group_submissions gs 
                           WHERE gs.submission_id = s.id)
GROUP BY a.id,a.name;

CREATE TABLE Group_submissions (
  `id` INTEGER,
  `submission_id` INTEGER,
  `student_id` INTEGER
);

INSERT INTO Group_submissions
  (`id`, `submission_id`, `student_id`)
VALUES
  ('1', '1', '1'),
  ('2', '2', '2');

CREATE TABLE Submissions (
  `id` INTEGER,
  `assignment_id` INTEGER,
  `student_id` INTEGER
);

INSERT INTO Submissions
  (`id`, `assignment_id`, `student_id`)
VALUES
  ('1', '1', '2'),
  ('2', '2', '1'),
  ('3', '1', '3'),
  ('4', '3', '1');

CREATE TABLE Assignments (
  `id` INTEGER,
  `name` VARCHAR(17)
);

INSERT INTO Assignments
  (`id`, `name`)
VALUES
  ('1', 'first-assignment'),
  ('2', 'second-assignment'),
  ('3', 'third-assignment');

SELECT  
  a.id,a.name
  , LENGTH(CONCAT(GROUP_CONCAT(s.`student_id`) ,IF(GROUP_CONCAT(gs.student_id) is NULL,'',','),IF(GROUP_CONCAT(gs.student_id) is NULL,'',GROUP_CONCAT(gs.student_id))))
   - LENGTH(REPLACE(CONCAT(GROUP_CONCAT(s.`student_id`) ,IF(GROUP_CONCAT(gs.student_id) is NULL,'',','),IF(GROUP_CONCAT(gs.student_id) is NULL,'',GROUP_CONCAT(gs.student_id))), ',', '')) + 1 as count_studints
from 
  Submissions s 
  LEFT JOIN Group_submissions gs ON gs.submission_id = s.id 
  INNER JOIN Assignments a on s.assignment_id = a.id
WHERE s.`student_id` NOT IN (SELECT student_id 
                           FROM Group_submissions gs 
                           WHERE gs.submission_id = s.id)
GROUP BY a.id,a.name;

id | name              | count\_studints
-: | :---------------- | -------------:
 1 | first-assignment  |              3
 2 | second-assignment |              2
 3 | third-assignment  |              1

_db<>fidèle aquí_