Sqlite - Gibt Zeilen zurück, in denen einige Werte unterschiedlich sind

Ich habe eine große Tabelle, eine SQLite-Datenbank mit Prüfungsergebnissen von Studenten. Studenten wiederholen häufig nicht bestandene Prüfungen und bestehen sie anschließend, wie unten mit Student 10001 gezeigt.

+─────────────+────────+──────────────+──────────+────────+─────────────+
| student_id | level | name | outcome | grade | date |
+─────────────+────────+──────────────+──────────+────────+─────────────+
| 10001 | Higher | Mathematics | Pass | A | 01/04/2022 |
| 10001 | Higher | Mathematics | Fail | F | 01/02/2022 |
| 10002 | Higher | English | Pass | B | 01/04/2022 |
+─────────────+────────+──────────────+──────────+────────+─────────────+

Ich möchte mit diesen Daten zwei Dinge tun können.

Geben Sie nur die nicht bestandene Prüfung zurück, wenn diese auch einen Bestanden-Eintrag enthält. Also die Zeile 2. von oben in der Bildtabelle.

Geben Sie die gesamte Tabelle zurück, diesmal jedoch ohne die erste nicht bestandene Prüfung. Also alles abzüglich der Ergebnisse von 1.

Die Prüfung gilt als Wiederholung, wenn student_id, level und name alle übereinstimmen. Die anderen Werte in den Zeilen können unterschiedlich sein.

Jede Hilfe geschätzt.

Lösung des Problems

Für die erste gewünschte Ergebnismenge können Sie EXISTSin der WHEREKlausel Folgendes verwenden:

SELECT t1.*
FROM tablename t1
WHERE t1.outcome = 'Fail'
 AND EXISTS (
 SELECT 1
 FROM tablename t2
 WHERE (t2.student_id, t2.level, t2.name) = (t1.student_id, t1.level, t1.name)
 AND t2.date > t1.date 
 AND t2.outcome = 'Pass'
 );

und für die 2. Ergebnismenge, da die Anforderung lautet:

alles abzüglich der Ergebnisse von 1

Der einfachste Weg, es zu bekommen, ist mit der 1. Abfrage und EXCEPT:

SELECT * 
FROM tablename
EXCEPT
SELECT t1.*
FROM tablename t1
WHERE t1.outcome = 'Fail'
 AND EXISTS (
 SELECT 1
 FROM tablename t2
 WHERE (t2.student_id, t2.level, t2.name) = (t1.student_id, t1.level, t1.name)
 AND t2.date > t1.date 
 AND t2.outcome = 'Pass'
 );

Sehen Sie sich die Demo an.

Beachten Sie, dass diese Daten nicht vergleichbar sind, wenn das Format der Daten in der Tabelle mit den Beispieldaten in Ihrer Frage übereinstimmt.
Sie sollten das Format auf ändern YYYY-MM-DD.