Mit der folgenden Abfrage wurde ermittelt, wer für die zweite Phase des Projekts geeignet war:
SELECT CONCAT(p1.id, p1.tvab) + 0 AS tvid, CONCAT(p1.christian_name, ' ', p1.surname) AS Name, p1.postal_code AS Code, p1.city AS City, pg.abrev AS Area, IF(td.participation = 'Aborted', 'A', ' ') AS A, p1.dead AS dead1, l.ereignis AS event1, td.suspect AS tsuspect1, id.suspect AS isuspect1, td.severe AS tsevere1, id.severe AS isevere1, p2.dead AS dead2, l2.ereignis AS event2, h2.nurse AS nurse2, h2.doctor AS doctor2, td2.suspect AS tsuspect2, id2.suspect AS isuspect2, td2.severe AS tsevere2, id2.severe AS isevere2, l.finish_datum FROM twin_project AS tp /* For Twin 1 */ LEFT JOIN twin_data AS td ON tp.id = td.id AND tp.tvab = td.tvab LEFT JOIN informant_data AS id ON tp.id = id.id AND tp.tvab = id.tvab LEFT JOIN harmony AS h ON tp.id = h.id AND tp.tvab = h.tvab LEFT JOIN lentus AS l ON tp.id = l.id AND tp.tvab = l.tvab /* For Twin 2 */ LEFT JOIN twin_data AS td2 ON p2.id = td2.id AND p2.tvab = td2.tvab LEFT JOIN informant_data AS id2 ON p2.id = id2.id AND p2.tvab = id2.tvab LEFT JOIN harmony AS h2 ON p2.id = h2.id AND p2.tvab = h2.tvab LEFT JOIN lentus AS l2 ON p2.id = l2.id AND p2.tvab = l2.tvab, person_data AS p1, person_data AS p2, postal_groups AS pg WHERE /* p1 gets main twin and p2 gets his/her twin. */ /* ptvab is a field inverted from tvab */ p1.id = tp.id AND p1.tvab = tp.tvab AND p2.id = p1.id AND p2.ptvab = p1.tvab AND /* Just the screening survey */ tp.survey_no = 5 AND /* Skip if partner died before 65 but allow emigration (dead=9) */ (p2.dead = 0 OR p2.dead = 9 OR (p2.dead = 1 AND (p2.todestag_datum = 0 OR (((TO_DAYS(p2.todestag_datum) - TO_DAYS(p2.Geburtstag)) / 365) >= 65)))) AND ( /* Twin is suspect */ (td.future_contact = 'Yes' AND td.suspect = 2) OR /* Twin is suspect - Informant is Blessed */ (td.future_contact = 'Yes' AND td.suspect = 1 AND id.suspect = 1) OR /* No twin - Informant is Blessed */ (ISNULL(td.suspect) AND id.suspect = 1 AND id.future_contact = 'Yes') OR /* Twin broken off - Informant is Blessed */ (td.participation = 'Aborted' AND id.suspect = 1 AND id.future_contact = 'Yes') OR /* Twin broken off - No inform - Have partner */ (td.participation = 'Aborted' AND ISNULL(id.suspect) AND p2.dead = 0)) AND l.ereignis = 'Finished' /* Get at area code */ AND SUBSTRING(p1.postal_code, 1, 2) = pg.code /* Not already distributed */ AND (h.nurse IS NULL OR h.nurse=00 OR h.doctor=00) /* Has not refused or been aborted */ AND NOT (h.status = 'Refused' OR h.status = 'Aborted' OR h.status = 'Died' OR h.status = 'Other') ORDER BY tvid;
Hierzu ein paar Anmerkungen:
CONCAT(p1.id, p1.tvab) + 0 AS tvid
Die verketteten Werte id
und
tvab
sollen in numerischer Reihenfolge
sortiert werden. Durch Hinzufügen von 0
zum Ergebnis wird MySQL dazu veranlasst, dieses Ergebnis als
Zahl zu betrachten.
Spalte id
Bezeichnet ein Zwillingspaar. Die Spalte wird als Schlüssel in allen Tabellen verwendet.
Spalte tvab
Bezeichnet einen Zwilling in einem Paar. Der Wert ist
entweder 1
oder 2
.
Spalte ptvab
Dies ist das Gegenstück zu tvab
. Wenn
tvab
1
ist, dann ist
dieser Wert 2
und umgekehrt. Zweck ist
die Einsparung von Tipparbeit und eine vereinfachte
Optimierung der Abfrage durch MySQL.
Diese Abfrage veranschaulicht unter anderem, wie man Daten in
einer Tabelle mithilfe eines Joins von derselben Tabelle aus
durchführt (p1
und p2
).
In diesem Beispiel wird diese Methode verwendet, um zu
ermitteln, ob einer von zwei Zwillingen eines Paares vor dem 65.
Lebensjahr starb. Ist dies der Fall, dann wird der Datensatz
nicht zurückgegeben.
Alle genannten Elemente sind in allen Tabellen mit Informationen
zu den Zwillingen vorhanden. Um Abfragen zu beschleunigen, wurde
ein Schlüssel sowohl auf id,tvab
(alle
Tabellen) als auch auf id,ptvab
(person_data
) erstellt.
Auf unserem Produktionssystem (UltraSPARC, 200 MHz) gibt diese Abfrage etwa 150 bis 200 Datensätze zurück und benötigt dafür weniger als eine Sekunde.
Die aktuelle Anzahl der Datensätze in den in dieser Abfrage verwendeten Tabellen sieht wie folgt aus:
Tabelle | Datensätze |
person_data |
71074 |
lentus |
5291 |
twin_project |
5286 |
twin_data |
2012 |
informant_data |
663 |
harmony |
381 |
postal_groups |
100 |
Dies ist eine Übersetzung des MySQL-Referenzhandbuchs, das sich auf dev.mysql.com befindet. Das ursprüngliche Referenzhandbuch ist auf Englisch, und diese Übersetzung ist nicht notwendigerweise so aktuell wie die englische Ausgabe. Das vorliegende deutschsprachige Handbuch behandelt MySQL bis zur Version 5.1.