Warunek wstępny:
Musisz utworzyć bazę danych i kilka powiązanych tabel, w których wiersze jednej tabeli zostaną przekonwertowane na kolumny, takie jak funkcja PIVOT(). Uruchom następujące instrukcje SQL, aby utworzyć bazę danych o nazwie „ unidb ’ i utwórz trzy tabele o nazwie ‘ studenci „”,„ kursy ' oraz ' wynik „. studenci oraz wynik tabele będą powiązane relacją jeden-do-wielu i kursy oraz wyniki tabele będą tutaj powiązane relacją jeden-do-wielu. UTWÓRZ oświadczenie z wynik tabela zawiera dwa ograniczenia klucza obcego dla pól, std_id , oraz Identyfikator kursu .
TWORZENIE BAZY DANYCH unidb;
UŻYJ unidb;
STWÓRZ STÓŁ studentów(
NSWEWN. KLUCZ PODSTAWOWY,
imię varchar(pięćdziesiąt)NIE JEST ZEREM,
dział VARCHAR(piętnaście)NIE JEST ZEREM);
Kursy CREATE TABLE(
identyfikator kursu VARCHAR(20)GŁÓWNY KLUCZ,
imię varchar(pięćdziesiąt)NIE JEST ZEREM,
kredyt MAŁY NIE NULL);
UTWÓRZ TABELĘ wynik(
std_id INT NIE NULL,
identyfikator kursu VARCHAR(20)NIE JEST ZEREM,
mark_type VARCHAR(20)NIE JEST ZEREM,
zaznacza SMALLINT NOT NULL,
KLUCZ OBCY(std_id)REFERENCJE studenci(NS),
KLUCZ OBCY(Identyfikator kursu)REFERENCJE kursy(Identyfikator kursu),
GŁÓWNY KLUCZ(std_id, course_id, mark_type));
Wstaw kilka rekordów do studenci, kursy i wynik tabele. Wartości należy wstawić do tabel na podstawie ograniczeń ustawionych w momencie tworzenia tabeli.
WSTAW W UCZNIÓW WARTOŚCI
( '1937463',„Harper Lee”,„CSE”),
( '1937464','Garcia Marquez',„CSE”),
( '1937465',- Forster, E.M.,„CSE”),
( '1937466',„Ralph Ellison”,„CSE”);
WSTAWIĆ W KURSY WARTOŚCI
( „CSE-401”,„Programowanie obiektowe”,3),
( „CSE-403”,'Struktura danych',2),
( „CSE-407”,„Programowanie uniksowe”,2);
WSTAW W WYNIK WARTOŚCI
( '1937463',„CSE-401”,„Egzamin wewnętrzny”,piętnaście),
( '1937463',„CSE-401”,„Egzamin śródsemestralny”,20),
( '1937463',„CSE-401”,'Egzamin końcowy',35),
( '1937464',„CSE-403”,„Egzamin wewnętrzny”,17),
( '1937464',„CSE-403”,„Egzamin śródsemestralny”,piętnaście),
( '1937464',„CSE-403”,'Egzamin końcowy',30),
( '1937465',„CSE-401”,„Egzamin wewnętrzny”,18),
( '1937465',„CSE-401”,„Egzamin śródsemestralny”,2. 3),
( '1937465',„CSE-401”,'Egzamin końcowy',38),
( '1937466',„CSE-407”,„Egzamin wewnętrzny”,20),
( '1937466',„CSE-407”,„Egzamin śródsemestralny”,22),
( '1937466',„CSE-407”,'Egzamin końcowy',40);
Tutaj, wynik tabela zawiera wiele takich samych wartości dla std_id , typ_znacznika oraz Identyfikator kursu kolumny w każdym rzędzie. Jak przekonwertować te wiersze na kolumny tej tabeli, aby wyświetlić dane w bardziej zorganizowanym formacie, pokazano w następnej części tego samouczka.
Obróć wiersze do kolumn za pomocą instrukcji CASE:
Uruchom następującą prostą instrukcję SELECT, aby wyświetlić wszystkie rekordy wynik Tabela.
WYBIERZ*Z wynik;
Dane wyjściowe pokazują cztery oceny studenta z trzech rodzajów egzaminów z trzech kursów. Więc wartości std_id , Identyfikator kursu oraz typ_znacznika są powtarzane wielokrotnie dla różnych uczniów, kursów i rodzajów egzaminów.
Dane wyjściowe będą bardziej czytelne, jeśli zapytanie SELECT będzie można napisać wydajniej za pomocą instrukcji CASE. Poniższa instrukcja SELECT z instrukcją CASE przekształci powtarzające się wartości wierszy w nazwy kolumn i wyświetli zawartość tabel w bardziej zrozumiałym dla użytkownika formacie.
WYBIERZ wynik.std_id, wynik.id_kursu,MAX(PRZYPADEK GDY wynik.mark_type =„Egzamin wewnętrzny”THEN wynik.marks END) „Egzamin wewnętrzny”,
MAX(PRZYPADEK GDY wynik.mark_type =„Egzamin śródsemestralny”THEN wynik.marks END) „Egzamin śródsemestralny”,
MAX(PRZYPADEK GDY wynik.mark_type ='Egzamin końcowy'THEN wynik.marks END) 'Egzamin końcowy'
OD wyniku
GROUP BY wynik.std_id, wynik.kurs_id
ORDER BY wynik.std_id, wynik.kurs_id ASC;
Poniższe dane wyjściowe pojawią się po uruchomieniu powyższej instrukcji, która jest bardziej czytelna niż poprzednie dane wyjściowe.
Obróć wiersze do kolumn za pomocą funkcji CASE i SUM():
Jeśli chcesz policzyć całkowitą liczbę kursów każdego ucznia z tabeli, musisz użyć funkcji agregującej SUMA() Grupuj według std_id oraz Identyfikator kursu z instrukcją CASE. Następujące zapytanie jest tworzone przez zmodyfikowanie poprzedniego zapytania za pomocą funkcji SUM() i klauzuli GROUP BY.
WYBIERZ wynik.std_id,wynik.id_kursu,MAX(PRZYPADEK GDY wynik.mark_type =„Egzamin wewnętrzny”THEN wynik.marks END) „Egzamin wewnętrzny”,
MAX(PRZYPADEK GDY wynik.mark_type =„Egzamin śródsemestralny”THEN wynik.marks END) „Egzamin śródsemestralny”,
MAX(PRZYPADEK GDY wynik.mark_type ='Egzamin końcowy'THEN wynik.marks END) 'Egzamin końcowy',
SUMA(wynik.znaki) jakCałkowity
OD wyniku
GROUP BY wynik.std_id, wynik.kurs_id
ORDER BY wynik.std_id, wynik.kurs_id ASC;
Dane wyjściowe pokazują nową kolumnę o nazwie Całkowity czyli wyświetlenie sumy ocen wszystkich typów egzaminów z każdego przedmiotu uzyskanych przez każdego konkretnego studenta.
Obróć wiersze do kolumn w wielu tabelach:
Poprzednie dwa zapytania są stosowane do wynik Tabela. Ta tabela jest powiązana z pozostałymi dwoma tabelami. To są studenci oraz kursy . Jeśli chcesz wyświetlić imię i nazwisko ucznia zamiast identyfikatora ucznia i nazwę kursu zamiast identyfikatora kursu, musisz napisać zapytanie SELECT, używając trzech powiązanych tabel, studenci , kursy oraz wynik . Następujące zapytanie SELECT jest tworzone przez dodanie trzech nazw tabel po klauzuli FORM i ustawienie odpowiednich warunków w klauzuli WHERE, aby pobrać dane z trzech tabel i wygenerować bardziej odpowiednie dane wyjściowe niż poprzednie zapytania SELECT.
SELECT studenci.imięjak ``Imię i nazwisko ucznia``, nazwa.kursówjak ``Nazwa kursu``,MAX(PRZYPADEK GDY wynik.mark_type =„Egzamin wewnętrzny”THEN wynik.marks END) 'CT',
MAX(PRZYPADEK GDY wynik.mark_type =„Egzamin śródsemestralny”THEN wynik.marks END) 'Środek',
MAX(PRZYPADEK GDY wynik.mark_type ='Egzamin końcowy'THEN wynik.marks END) 'Finał',
SUMA(wynik.znaki) jakCałkowity
OD studentów, kursy, wynik
GDZIE wynik.std_id = studenci.id i wynik.kurs_id= kursy.kurs_id
GROUP BY wynik.std_id, wynik.kurs_id
ORDER BY wynik.std_id, wynik.kurs_id ASC;
Poniższe dane wyjściowe zostaną wygenerowane po wykonaniu powyższego zapytania.
Wniosek:
W tym artykule pokazano, w jaki sposób można zaimplementować funkcjonalność funkcji Pivot() bez obsługi funkcji Pivot() w MySQL, używając niektórych danych fikcyjnych. Mam nadzieję, że po przeczytaniu tego artykułu czytelnicy będą w stanie przekształcić dowolne dane z poziomu wiersza w dane z poziomu kolumny za pomocą zapytania SELECT.