Obrotowe rzędy obrotowe mysql do kolumn

Tabela bazy danych może przechowywać różne typy danych, a czasem musimy przekształcić dane na poziomie wiersza w dane na poziomie kolumny. Problem ten można rozwiązać za pomocą funkcji PIVOT (). Ta funkcja służy do obracania wierszy tabeli w wartości kolumny. Ale ta funkcja jest obsługiwana przez bardzo niewiele serwerów bazy danych, takich jak Oracle lub SQL Server. Jeśli chcesz wykonać to samo zadanie w tabeli bazy danych MySQL, musisz napisać zapytanie Wybierz za pomocą instrukcji Case, aby obrócić wiersze w kolumnach. Artykuł pokazuje sposób wykonania zadania funkcji Pivot () w ramach powiązanych tabel bazy danych MySQL.

Warunek wstępny:

Musisz utworzyć bazę danych i niektóre powiązane tabe. Uruchom następujące instrukcje SQL, aby utworzyć bazę danych o nazwie 'UNIDB„i utwórz trzy nazwy”studenci','kursy' I 'wynik'. studenci I wynik tabele będą powiązane przez relacje jeden do wielu i kursy I wyniki Tabele będą powiązane przez relacje jeden do wielu tutaj. Utwórz instrukcję wynik Tabela zawiera dwa obce kluczowe ograniczenia dla pól, std_id, I Identyfikator kursu.

Utwórz bazę danych UNIDB;
Użyj UNIDB;
Utwórz studentów tabeli (
Id int podstawowy klucz,
Nazwa varchar (50) nie null,
Departament Varchar (15) nie null);
Utwórz kursy stołowe (
kurs_id varchar (20) klucz podstawowy,
Nazwa varchar (50) nie null,
Credit Smallint nie null);
Utwórz wynik tabeli (
std_id int not null,
kurs_id varchar (20) nie null,
Mark_type varchar (20) nie null,
oznacza, że ​​Smallint nie null,
Klucz zagraniczny (STD_ID) odwołuje się do studentów (id),
Klucz zagraniczny (kurs_id) odwołuje się do kursów (kurs_id),
Klucz podstawowy (std_id, kurs_id, mark_type));

Wstaw niektóre rekordy do studenci, kursy i wyniki tabele. Wartości należy wstawić do tabel na podstawie ustalonych ograniczeń w momencie tworzenia tabeli.

Wstaw do wartości uczniów
(„1937463”, „Harper Lee”, „cse”),
(„1937464”, „Garcia Marquez”, „cse”),
(„1937465”, „Forster, e.M.„,„ Cse ”),
(„1937466”, „Ralph Ellison”, „cse”);
Wstaw do wartości kursów
(„CSE-401”, „Programowanie obiektowe”, 3),
(„CSE-403”, „Struktura danych”, 2),
(„CSE-407”, „UNIX Programming”, 2);
Wstaw do wartości wyników
(„1937463”, „CSE-401”, „Egzamin wewnętrzny”, 15),
(„1937463”, „CSE-401”, „Mid Term Exam”, 20),
(„1937463”, „cse-401”, „egzamin końcowy”, 35),
(„1937464”, „CSE-403”, „Egzamin wewnętrzny”, 17),
(„1937464”, „CSE-403”, „Mid Term Exam”, 15),
(„1937464”, „cse-403”, „egzamin końcowy”, 30),
(„1937465”, „CSE-401”, „Egzamin wewnętrzny”, 18),
(„1937465”, „CSE-401”, „Mid Term Exam”, 23),
(„1937465”, „cse-401”, „egzamin końcowy”, 38),
(„1937466”, „CSE-407”, „Egzamin wewnętrzny”, 20),
(„1937466”, „CSE-407”, „Mid Term Exam”, 22),
(„1937466”, „cse-407”, „egzamin końcowy”, 40);

Tutaj, wynik Tabela zawiera wiele samych wartości dla std_id, Mark_type I Identyfikator kursu kolumny w każdym rzędzie. Jak przekonwertować te wiersze na kolumny tej tabeli w celu wyświetlania danych w bardziej zorganizowanym formacie, pokazano w następnej części tego samouczka.

Obróć rzędy na kolumny za pomocą instrukcji Case:

Uruchom następującą instrukcję Simple Select, aby wyświetlić wszystkie rekordy wynik tabela.

Wybierz * z wyniku;

Wyjście pokazuje cztery oceny uczniów dla trzech rodzajów egzaminów trzech kursów. Więc wartości std_id, Identyfikator kursu I Mark_type są wielokrotnie powtarzane dla różnych studentów, kursów i rodzajów egzaminów.

Dane wyjściowe będzie bardziej czytelne, jeśli zapytanie wyboru można zapisać bardziej efektywnie za pomocą instrukcji Case. Poniższe wybierz instrukcję sprawy przekształci powtarzające się wartości wierszy w nazwy kolumn i wyświetli zawartość tabel w bardziej zrozumiały format dla użytkownika.

Wybierz wynik.std_id, wynik.Identyfikator kursu,
Max (przypadek, gdy wynik.Mark_Type = „Egzamin wewnętrzny”, a następnie wynikaj.Znaki koniec) „Egzamin wewnętrzny”,
Max (przypadek, gdy wynik.Mark_Type = "Mid Termin Exam", a następnie wynik.Znaki koniec) „Egzamin w połowie semestru”,
Max (przypadek, gdy wynik.Mark_Type = „egzamin końcowy”, a następnie wynik.Znaki koniec) „egzamin końcowy”
Z wyniku
Grupa według wyniku.std_id, wynik.Identyfikator kursu
Zamów według wyniku.std_id, wynik.kurs_id ASC;

Po uruchomieniu powyższej instrukcji pojawi się następujące dane wyjściowe niż poprzednie wyjście.

Obróć rzędy na kolumny za pomocą Case and Sum ():

Jeśli chcesz policzyć całkowitą liczbę każdego kursu każdego ucznia z tabeli, musisz użyć funkcji agregatów SUMA() Grupuj według std_id I Identyfikator kursu Z oświadczeniem sprawy. Poniższe zapytanie jest tworzone przez modyfikację poprzedniego zapytania za pomocą funkcji sum () i grupy według klauzuli.

Wybierz wynik.std_id, wynik.Identyfikator kursu,
Max (przypadek, gdy wynik.Mark_Type = „Egzamin wewnętrzny”, a następnie wynikaj.Znaki koniec) „Egzamin wewnętrzny”,
Max (przypadek, gdy wynik.Mark_Type = "Mid Termin Exam", a następnie wynik.Znaki koniec) „Egzamin w połowie semestru”,
Max (przypadek, gdy wynik.Mark_Type = „egzamin końcowy”, a następnie wynik.Znaki koniec) „egzamin końcowy”,
Suma (wynik.Znaki) jako całkowite
Z wyniku
Grupa według wyniku.std_id, wynik.Identyfikator kursu
Zamów według wyniku.std_id, wynik.kurs_id ASC;

Wyjście pokazuje nową kolumnę o nazwie Całkowity To wyświetla sumę znaków wszystkich rodzajów egzaminów każdego kursu uzyskanego przez każdego konkretnego ucznia.

Obróć rzędy na kolumny w wielu tabelach:

Dwa poprzednie zapytania są stosowane do wynik tabela. Ta tabela jest związana z pozostałymi dwoma tabelami. To są studenci I kursy. Jeśli chcesz wyświetlić nazwę ucznia zamiast identyfikatora studenta i nazwę kursu zamiast identyfikatora kursu, musisz napisać zapytanie Wybierz za pomocą trzech powiązanych tabel, studenci, kursy I wynik. Poniższe zapytanie wyboru jest tworzone przez dodanie trzech nazw tabel po klauzuli formularza i ustawianie odpowiednich warunków w klauzuli Where to odzyskanie danych z trzech tabel i wygenerowanie bardziej odpowiedniego wyjścia niż poprzednie zapytania Wybierz.

Wybierz uczniów.Nazwa jako „nazwa studenta”, kursy.Nazwa jako „nazwa kursu”,
Max (przypadek, gdy wynik.Mark_Type = „Egzamin wewnętrzny”, a następnie wynikaj.Znaki koniec) „CT”,
Max (przypadek, gdy wynik.Mark_Type = "Mid Termin Exam", a następnie wynik.Znaki koniec) „Mid”,
Max (przypadek, gdy wynik.Mark_Type = „egzamin końcowy”, a następnie wynik.Marks End) „Final”,
Suma (wynik.Znaki) jako całkowite
Od studentów, kursów, wyniku
Gdzie wynik.std_id = uczniowie.ID i wynik.kurs_id = kursy.Identyfikator kursu
Grupa według wyniku.std_id, wynik.Identyfikator kursu
Zamów według wyniku.std_id, wynik.kurs_id ASC;

Następujące dane wyjściowe wygeneruje się po wykonaniu powyższego zapytania.

Wniosek:

Jak możesz zaimplementować funkcję funkcji PIVOT () bez obsługi funkcji PIVOT () w MySQL, jest pokazany w tym artykule za pomocą niektórych danych manekinowych. Mam nadzieję, że czytelnicy będą mogli przekształcić dane na poziomie wiersza w dane na poziomie kolumny za pomocą zapytania Wybierz po przeczytaniu tego artykułu.