Voraussetzung:
Sie müssen eine Datenbank und einige zugehörige Tabellen erstellen, in denen Zeilen einer Tabelle in Spalten wie der PIVOT()-Funktion umgewandelt werden. Führen Sie die folgenden SQL-Anweisungen aus, um eine Datenbank namens ‘ unidb ’ und erstellen Sie drei Tabellen mit dem Namen ‘ Studenten ’,’ Kurse ' und ' Ergebnis '. Studenten und Ergebnis Tabellen werden durch eine Eins-zu-Viele-Beziehung verbunden und Kurse und Ergebnisse Tabellen werden hier durch eine Eins-zu-Viele-Beziehung verbunden. CREATE-Anweisung des Ergebnis Tabelle enthält zwei Fremdschlüsseleinschränkungen für die Felder, std_id , und Kurs-ID .
DATENBANK ERSTELLEN unidb;
UNIDB VERWENDEN;
CREATE TABLE-Studenten(
Ich würdeINT PRIMÄRSCHLÜSSEL,
name varchar(fünfzig)NICHT NULL,
Abteilung VARCHAR(fünfzehn)NICHT NULL);
CREATE TABLE-Kurse(
kurs_id VARCHAR(zwanzig)PRIMÄRSCHLÜSSEL,
name varchar(fünfzig)NICHT NULL,
Gutschrift SMALLINT NICHT NULL);
CREATE TABLE-Ergebnis(
std_id INT NICHT NULL,
kurs_id VARCHAR(zwanzig)NICHT NULL,
mark_type VARCHAR(zwanzig)NICHT NULL,
markiert SMALLINT NOT NULL,
UNBEKANNTER SCHLÜSSEL(std_id)REFERENZEN Studierende(Ich würde),
UNBEKANNTER SCHLÜSSEL(Kurs-ID)REFERENZEN Kurse(Kurs-ID),
PRIMÄRSCHLÜSSEL(std_id, kurs_id, mark_type));
Fügen Sie einige Datensätze in ein Studierende, Kurse und Ergebnis Tabellen. Die Werte müssen gemäß den zum Zeitpunkt der Tabellenerstellung festgelegten Einschränkungen in die Tabellen eingefügt werden.
EINFÜGEN IN DIE WERTE der Schüler
( '1937463','Harper Lee','CSE'),
( '1937464','Garcia Marquez','CSE'),
( '1937465','Forster, E. M.','CSE'),
( '1937466','Ralph Ellison','CSE');
IN Kurse EINFÜGEN WERTE
( 'CSE-401','Objekt orientierte Programmierung',3),
( 'CSE-403','Datenstruktur',2),
( 'CSE-407','Unix-Programmierung',2);
INSERT IN TO result VALUES
( '1937463','CSE-401','Interne Prüfung',fünfzehn),
( '1937463','CSE-401','Zwischenprüfung',zwanzig),
( '1937463','CSE-401','Abschlussprüfung',35),
( '1937464','CSE-403','Interne Prüfung',17),
( '1937464','CSE-403','Zwischenprüfung',fünfzehn),
( '1937464','CSE-403','Abschlussprüfung',30),
( '1937465','CSE-401','Interne Prüfung',18),
( '1937465','CSE-401','Zwischenprüfung',2. 3),
( '1937465','CSE-401','Abschlussprüfung',38),
( '1937466','CSE-407','Interne Prüfung',zwanzig),
( '1937466','CSE-407','Zwischenprüfung',22),
( '1937466','CSE-407','Abschlussprüfung',40);
Hier, Ergebnis Tabelle enthält mehrere gleiche Werte für std_id , mark_type und Kurs-ID Spalten in jeder Zeile. Wie Sie diese Zeilen in Spalten dieser Tabelle konvertieren, um die Daten in einem organisierteren Format anzuzeigen, wird im nächsten Teil dieses Tutorials gezeigt.
Drehen Sie Zeilen mit der CASE-Anweisung in Spalten:
Führen Sie die folgende einfache SELECT-Anweisung aus, um alle Datensätze der Ergebnis Tisch.
AUSWÄHLEN*FROM-Ergebnis;
Die Ausgabe zeigt die vier Schülernoten für drei Prüfungsarten von drei Lehrveranstaltungen. Also die Werte von std_id , Kurs-ID und mark_type werden für die verschiedenen Studierenden, Lehrveranstaltungen und Prüfungsarten mehrfach wiederholt.
Die Ausgabe ist besser lesbar, wenn die SELECT-Abfrage mit der CASE-Anweisung effizienter geschrieben werden kann. Das folgende SELECT mit der CASE-Anweisung wandelt die sich wiederholenden Werte der Zeilen in die Spaltennamen um und zeigt den Inhalt der Tabellen in einem für den Benutzer verständlicheren Format an.
SELECT result.std_id, result.course_id,MAX(CASE WHEN result.mark_type ='Interne Prüfung'DANN Ergebnis.Markierungen ENDE) 'Interne Prüfung',
MAX(CASE WHEN result.mark_type ='Zwischenprüfung'DANN Ergebnis.Markierungen ENDE) 'Zwischenprüfung',
MAX(CASE WHEN result.mark_type ='Abschlussprüfung'DANN Ergebnis.Markierungen ENDE) 'Abschlussprüfung'
FROM-Ergebnis
GRUPPE NACH result.std_id, result.course_id
ORDER BY result.std_id, result.course_id ASC;
Die folgende Ausgabe erscheint, nachdem die obige Anweisung ausgeführt wurde, die besser lesbar ist als die vorherige Ausgabe.
Zeilen mit CASE und SUM() in Spalten drehen:
Wenn Sie die Gesamtzahl jedes Kurses jedes Studenten aus der Tabelle zählen möchten, müssen Sie die Aggregatfunktion verwenden SUMME() gruppiere nach std_id und Kurs-ID mit der CASE-Anweisung. Die folgende Abfrage wird erstellt, indem die vorherige Abfrage mit der SUM()-Funktion und der GROUP BY-Klausel geändert wird.
SELECT result.std_id,result.course_id,MAX(CASE WHEN result.mark_type ='Interne Prüfung'DANN Ergebnis.Markierungen ENDE) 'Interne Prüfung',
MAX(CASE WHEN result.mark_type ='Zwischenprüfung'DANN Ergebnis.Markierungen ENDE) 'Zwischenprüfung',
MAX(CASE WHEN result.mark_type ='Abschlussprüfung'DANN Ergebnis.Markierungen ENDE) 'Abschlussprüfung',
SUMME(Ergebnis.Markierungen) wieGesamt
FROM-Ergebnis
GRUPPE NACH result.std_id, result.course_id
ORDER BY result.std_id, result.course_id ASC;
Die Ausgabe zeigt eine neue Spalte namens Gesamt das heißt, die Summe der Noten aller Prüfungsarten jedes Kurses anzuzeigen, die von jedem einzelnen Studenten erhalten wurden.
Zeilen in Spalten in mehreren Tabellen drehen:
Die vorherigen beiden Abfragen gelten für die Ergebnis Tisch. Diese Tabelle ist mit den anderen beiden Tabellen verwandt. Diese sind Studenten und Kurse . Wenn Sie den Studentennamen anstelle der Studenten-ID und den Kursnamen anstelle der Kurs-ID anzeigen möchten, müssen Sie die SELECT-Abfrage mithilfe von drei verwandten Tabellen schreiben, Studenten , Kurse und Ergebnis . Die folgende SELECT-Abfrage wird erstellt, indem drei Tabellennamen nach der FORM-Klausel hinzugefügt und geeignete Bedingungen in der WHERE-Klausel festgelegt werden, um die Daten aus den drei Tabellen abzurufen und eine geeignetere Ausgabe als die vorherigen SELECT-Abfragen zu generieren.
SELECT student.namewie ``Name des Studenten``, kurse.namewie ``Kursname``,MAX(CASE WHEN result.mark_type ='Interne Prüfung'DANN Ergebnis.Markierungen ENDE) 'CT',
MAX(CASE WHEN result.mark_type ='Zwischenprüfung'DANN Ergebnis.Markierungen ENDE) 'Mitte',
MAX(CASE WHEN result.mark_type ='Abschlussprüfung'DANN Ergebnis.Markierungen ENDE) 'Finale',
SUMME(Ergebnis.Markierungen) wieGesamt
VON Studenten, Kurse, Ergebnis
WHERE result.std_id = Students.id und result.course_id=courses.course_id
GRUPPE NACH result.std_id, result.course_id
ORDER BY result.std_id, result.course_id ASC;
Die folgende Ausgabe wird generiert, nachdem die obige Abfrage ausgeführt wurde.
Abschluss:
Wie Sie die Funktionalität der Pivot()-Funktion ohne die Unterstützung der Pivot()-Funktion in MySQL implementieren können, wird in diesem Artikel anhand von Dummy-Daten gezeigt. Ich hoffe, dass die Leser nach dem Lesen dieses Artikels alle Daten auf Zeilenebene in Daten auf Spaltenebene umwandeln können, indem sie die SELECT-Abfrage verwenden.