MySQL Pivot rotierende Zeilen zu Spalten

Eine Datenbanktabelle kann verschiedene Datenarten speichern, und manchmal müssen wir Daten auf Zeilenebene in Daten auf Spaltenebene umwandeln. Dieses Problem kann durch die Verwendung der Pivot () -Funktion gelöst werden. Diese Funktion wird verwendet, um die Zeilen einer Tabelle in Spaltenwerte zu drehen. Diese Funktion wird jedoch von sehr wenigen Datenbankservern wie Oracle oder SQL Server unterstützt. Wenn Sie dieselbe Aufgabe in der MySQL -Datenbanktabelle erledigen möchten. Der Artikel zeigt die Möglichkeit, die Funktion von Pivot () in den verwandten MySQL -Datenbanktabellen zu erledigen.

Voraussetzung:

Sie müssen eine Datenbank und einige zugehörige Tabellen erstellen, in denen Zeilen einer Tabelle in die Spalten wie Pivot () -Funktion konvertiert werden. Führen Sie die folgenden SQL -Anweisungen aus, um eine Datenbank mit dem Namen 'zu erstellen.'Unidb"und erstellen Sie drei Tabellen mit dem Namen"Studenten',' 'Kurse' Und 'Ergebnis''. Studenten Und Ergebnis Die Tabellen werden durch eins-zu-Viele-Verhältnis in Verbindung gebracht und Kurse Und Ergebnisse Die Tabellen werden hier von einer eins-zu-Viele-Beziehung verwandt sein. Erstellen Sie Anweisung der Ergebnis Die Tabelle enthält zwei fremde Schlüsselbeschränkungen für die Felder, std_id, Und Kurs-ID.

Datenbank erstellen unidb;
Verwenden Sie Unidb;
Erstellen Sie Tabellenstudenten (
ID int Primärschlüssel,
Nennen Sie Varchar (50) NICHT NULL,
Abteilung Varchar (15) nicht null);
Tischkurse erstellen (
CURTS_ID VARCHAR (20) Primärschlüssel,
Nennen Sie Varchar (50) NICHT NULL,
Credit Smallint nicht null);
Tabellenergebnis erstellen (
std_id int nicht null,
CURTS_ID VARCHAR (20) NICHT NULL,
mark_type varchar (20) nicht null,
markiert smallint nicht null,
Fremd Key (STD_ID) Referenzen von Studenten (ID),
Fremd Key (COURS_ID) REFERENZENKURSEN (COURS_ID),
Primärschlüssel (STD_ID, COURS_ID, mark_type));

Fügen Sie einige Datensätze in ein Studenten, Kurse und Ergebnis Tische. Die Werte müssen basierend auf den zum Zeitpunkt der Tabellenerstellung festgelegten Einschränkungen in die Tabellen eingefügt werden.

In die Schülerwerte einfügen
('1937463', 'Harper Lee', 'CSE'),
('1937464', 'Garcia Marquez', 'CSE'),
('1937465', 'Forster, e.M.',' Cse '),
('1937466', 'Ralph Ellison', 'CSE');
In Kursewerte einfügen Werte
('CSE-401', 'Objektorientiertes Programmieren', 3),
('CSE-403', 'Datenstruktur', 2),
('CSE-407', 'Unix Programming', 2);
In Ergebniswerte einfügen
('1937463', 'CSE-401', 'Internal Exam', 15),
('1937463', 'CSE-401', 'Mid Term Exam', 20),
('1937463', 'CSE-401', 'Abschlussprüfung', 35),
('1937464', 'CSE-403', 'Internal Exam', 17),
('1937464', 'CSE-403', 'Mid Term Exam', 15),
('1937464', 'CSE-403', 'Abschlussprüfung', 30),
('1937465', 'CSE-401', 'Internal Exam', 18),
('1937465', 'CSE-401', 'Mid Term Exam', 23),
('1937465', 'CSE-401', 'Abschlussprüfung', 38),
('1937466', 'CSE-407', 'Internal Exam', 20),
('1937466', 'CSE-407', 'Mid Term Exam', 22),
('1937466', 'CSE-407', 'Abschlussprüfung', 40);

Hier, Ergebnis Die Tabelle enthält mehrere gleiche Werte für std_id, mark_type Und Kurs-ID Spalten in jeder Zeile. So konvertieren Sie diese Zeilen in Spalten dieser Tabelle, um die Daten in einem organisierten Format anzuzeigen, im nächsten Teil dieses Tutorials.

Drehen Sie Zeilen in Spalten mit der Fallanweisung:

Führen Sie die folgende einfache Auswahlanweisung aus, um alle Datensätze der anzuzeigen Ergebnis Tisch.

Aus dem Ergebnis auswählen;

Die Ausgabe zeigt die vier Schülermarken für drei Prüfungsarten von drei Kursen. Also die Werte von std_id, Kurs-ID Und mark_type werden mehrmals für die verschiedenen Studenten, Kurse und Prüfungstypen wiederholt.

Die Ausgabe ist lesbarer, wenn die ausgewählte Abfrage mithilfe der Fallanweisung effizienter geschrieben werden kann. Die folgende Auswahl mit der Fallanweisung transformiert die Wiederholungswerte der Zeilen in die Spaltennamen und zeigt den Inhalt der Tabellen in einem verständlicheren Format für den Benutzer an.

Wählen Sie Ergebnis.STD_ID, Ergebnis.Kurs-ID,
Max (Fall, wenn Ergebnis.mark_type = "interne prüfung" dann resultieren Sie dann.Markierungen enden) "interne Prüfung",
Max (Fall, wenn Ergebnis.mark_type = "Mid Term Exam" und dann Ergebnis.Markierungen enden) "Mid -Term Exam",
Max (Fall, wenn Ergebnis.mark_type = "endgültige Prüfung" und dann Ergebnis.Markierungen enden) "Abschlussprüfung"
Aus Ergebnis
Gruppe nach Ergebnis.STD_ID, Ergebnis.Kurs-ID
Bestellung nach Ergebnis.STD_ID, Ergebnis.Kurs_id ASC;

Die folgende Ausgabe wird nach dem Ausführen der obigen Anweisung angezeigt, die lesbarer als die vorherige Ausgabe ist.

Drehen Sie Zeilen mit Case und Summe () zu Spalten: SUM ():

Wenn Sie die Gesamtzahl jedes Schülerverlaufs aus der Tabelle zählen möchten, müssen Sie die Gesamtfunktion verwenden SUMME() gruppiere nach std_id Und Kurs-ID mit der Fallerklärung. Die folgende Abfrage wird erstellt, indem die vorherige Abfrage mit SUM () -Funktion und Gruppe nach Klausel geändert wird.

Wählen Sie Ergebnis.STD_ID, Ergebnis.Kurs-ID,
Max (Fall, wenn Ergebnis.mark_type = "interne prüfung" dann resultieren Sie dann.Markierungen enden) "interne Prüfung",
Max (Fall, wenn Ergebnis.mark_type = "Mid Term Exam" und dann Ergebnis.Markierungen enden) "Mid -Term Exam",
Max (Fall, wenn Ergebnis.mark_type = "endgültige Prüfung" und dann Ergebnis.Markierungen enden) "Abschlussprüfung",
Summe (Ergebnis.Markierungen) als total
Aus Ergebnis
Gruppe nach Ergebnis.STD_ID, Ergebnis.Kurs-ID
Bestellung nach Ergebnis.STD_ID, Ergebnis.Kurs_id ASC;

Die Ausgabe zeigt eine neue Spalte mit dem Namen Gesamt Dies zeigt die Summe der Markierungen aller Prüfungstypen jedes Kurses, die von jedem bestimmten Schüler erhalten wurden.

Drehen Sie die Zeilen zu Spalten in mehreren Tabellen:

Die beiden vorherigen Abfragen werden auf die angewendet Ergebnis Tisch. Diese Tabelle bezieht sich auf die beiden anderen Tabellen. Diese sind Studenten Und Kurse. Wenn Sie den Schülernamen anstelle von Studenten -ID und Kursname anstelle der Kurs -ID anzeigen möchten, müssen Sie die Auswahl der Abfrage mit drei zugehörigen Tabellen schreiben, Studenten, Kurse Und Ergebnis. Die folgende Auswahlabfrage wird erstellt, indem drei Tabellennamen nach Formularklausel hinzugefügt und die entsprechenden Bedingungen in der Klausel festgelegt werden, um die Daten aus den drei Tabellen abzurufen und eine angemessenere Ausgabe zu generieren.

Schüler auswählen.Name als "Schülername", Kurse.Name als "Kursname",
Max (Fall, wenn Ergebnis.mark_type = "interne prüfung" dann resultieren Sie dann.Markierungen enden) "CT",
Max (Fall, wenn Ergebnis.mark_type = "Mid Term Exam" und dann Ergebnis.Markierungen enden) "Mid",
Max (Fall, wenn Ergebnis.mark_type = "endgültige Prüfung" und dann Ergebnis.Markierungen enden) "Finale",
Summe (Ergebnis.Markierungen) als total
Von Studenten, Kursen, Ergebnis
Wo Ergebnis.STD_ID = Schüler.ID und Ergebnis.curse_id = Kurse.Kurs-ID
Gruppe nach Ergebnis.STD_ID, Ergebnis.Kurs-ID
Bestellung nach Ergebnis.STD_ID, Ergebnis.Kurs_id ASC;

Die folgende Ausgabe erzeugt nach der Ausführung der obigen Abfrage.

Abschluss:

In diesem Artikel können Sie die Funktionalität der Funktion pivot () implementieren, ohne dass die Funktion pivot () in MySQL unterstützt wird, indem Sie einige Dummydaten verwenden. Ich hoffe, die Leser können alle Daten auf Zeilenebene in Daten auf Spaltenebene umwandeln, indem Sie die Abfrage auswählen, nachdem Sie diesen Artikel gelesen haben.