Předpoklad:
Musíte vytvořit databázi a některé související tabulky, kde budou řádky jedné tabulky převedeny do sloupců jako funkce PIVOT (). Spuštěním následujících příkazů SQL vytvořte databázi s názvem ‘ unidb “A vytvořte tři tabulky s názvem„ studenti “,„ kurzy ' a ' výsledek '. studenti a výsledek tabulky budou spojeny vztahem jedna k mnoha a kurzy a Výsledek zde budou tabulky propojeny vztahem jedna k mnoha. VYTVOŘENÍ prohlášení výsledek tabulka obsahuje dvě omezení cizího klíče pro pole, std_id , a course_id .
VYTVOŘIT DATABÁZI unidb;
USE unidb;
VYTVOŘTE TABULKU studentů(
idVNITŘNÍ PRIMÁRNÍ KLÍČ,
jméno varchar(padesátka)NENULOVÝ,
oddělení VARCHAR(patnáct)NENULOVÝ);
VYTVOŘTE kurzy TABULKY(
course_id VARCHAR(dvacet)PRIMÁRNÍ KLÍČ,
jméno varchar(padesátka)NENULOVÝ,
kredit SMALLINT NENÍ NULL);
Výsledek CREATE TABLE(
std_id INT NOT NULL,
course_id VARCHAR(dvacet)NENULOVÝ,
typ_značky VARCHAR(dvacet)NENULOVÝ,
značky SMALLINT NENÍ NULL,
CIZÍ KLÍČ(std_id)REFERENCE studenti(id),
CIZÍ KLÍČ(course_id)REFERENČNÍ kurzy(course_id),
PRIMÁRNÍ KLÍČ(std_id, course_id, mark_type));
Vložte nějaké záznamy do studenti, kurzy a výsledek stoly. Hodnoty musí být vloženy do tabulek na základě omezení nastavených v době vytváření tabulky.
VLOŽTE DO HODNOT studentů
( '1937463','Harper Lee','CSE'),
( '1937464','Garcia Marquez','CSE'),
( '1937465','Forster, E.M.','CSE'),
( '1937466','Ralph Ellison','CSE');
VLOŽTE DO HODNOT kurzů
( 'CSE-401','Objektově orientované programování',3),
( 'CSE-403','Datová struktura',2),
( 'CSE-407','Unix programování',2);
VLOŽTE DO VÝSLEDKŮ HODNOTY
( '1937463','CSE-401','Interní zkouška',patnáct),
( '1937463','CSE-401','Pololetní zkoušky',dvacet),
( '1937463','CSE-401','Závěrečná zkouška',35),
( '1937464','CSE-403','Interní zkouška',17),
( '1937464','CSE-403','Pololetní zkoušky',patnáct),
( '1937464','CSE-403','Závěrečná zkouška',30),
( '1937465','CSE-401','Interní zkouška',18),
( '1937465','CSE-401','Pololetní zkoušky',2. 3),
( '1937465','CSE-401','Závěrečná zkouška',38),
( '1937466','CSE-407','Interní zkouška',dvacet),
( '1937466','CSE-407','Pololetní zkoušky',22),
( '1937466','CSE-407','Závěrečná zkouška',40);
Tady, výsledek tabulka obsahuje více stejných hodnot pro std_id , typ_značky a course_id sloupce v každém řádku. Jak převést tyto řádky na sloupce této tabulky pro zobrazení dat v organizovanějším formátu, je uvedeno v další části tohoto kurzu.
Otočte řádky do sloupců pomocí příkazu CASE:
Spuštěním následujícího jednoduchého příkazu SELECT zobrazíte všechny záznamy výsledek stůl.
VYBRAT*FROM výsledek;
Výstup ukazuje čtyři známky studentů pro tři typy zkoušek ze tří kurzů. Takže hodnoty std_id , course_id a typ_značky se opakují několikrát pro různé studenty, kurzy a typy zkoušek.
Výstup bude čitelnější, pokud lze dotaz SELECT psát efektivněji pomocí příkazu CASE. Následující příkaz SELECT s příkazem CASE transformuje opakující se hodnoty řádků na názvy sloupců a zobrazí obsah tabulek v srozumitelnějším formátu pro uživatele.
VYBRAT result.std_id, result.course_id,MAX(PŘÍPAD KDY result.mark_type ='Interní zkouška'POTOM výsledek. Značky KONEC) 'Interní zkouška',
MAX(PŘÍPAD KDY result.mark_type ='Pololetní zkoušky'POTOM výsledek. Značky KONEC) 'Pololetní zkoušky',
MAX(PŘÍPAD KDY result.mark_type ='Závěrečná zkouška'POTOM výsledek. Značky KONEC) 'Závěrečná zkouška'
FROM výsledek
SKUPINA PODLE result.std_id, result.course_id
OBJEDNAT PODLE result.std_id, result.course_id ASC;
Následující výstup se objeví po spuštění výše uvedeného příkazu, který je čitelnější než předchozí výstup.
Otočit řádky do sloupců pomocí CASE a SUM ():
Pokud chcete z tabulky spočítat celkový počet každého kurzu každého studenta, musíte použít agregační funkci SOUČET() skupina vytvořená std_id a course_id s příkazem CASE. Následující dotaz je vytvořen úpravou předchozího dotazu pomocí funkce SUM () a klauzule GROUP BY.
VYBRAT result.std_id, result.course_id,MAX(PŘÍPAD KDY result.mark_type ='Interní zkouška'POTOM výsledek. Značky KONEC) 'Interní zkouška',
MAX(PŘÍPAD KDY result.mark_type ='Pololetní zkoušky'POTOM výsledek. Značky KONEC) 'Pololetní zkoušky',
MAX(PŘÍPAD KDY result.mark_type ='Závěrečná zkouška'POTOM výsledek. Značky KONEC) 'Závěrečná zkouška',
SOUČET(výsledek.značky) tak jakoCelkový
FROM výsledek
SKUPINA PODLE result.std_id, result.course_id
OBJEDNAT PODLE result.std_id, result.course_id ASC;
Výstup zobrazí nový sloupec s názvem Celkový to je součet známek všech typů zkoušek každého kurzu získaný každým konkrétním studentem.
Otočit řádky na sloupce ve více tabulkách:
Předchozí dva dotazy se použijí na výsledek stůl. Tato tabulka souvisí s dalšími dvěma tabulkami. Tyto jsou studenti a kurzy . Pokud chcete místo ID kurzu zobrazit jméno studenta a název kurzu místo ID kurzu, musíte napsat dotaz SELECT pomocí tří souvisejících tabulek, studenti , kurzy a výsledek . Následující dotaz SELECT se vytvoří přidáním tří názvů tabulek za klauzuli FORM a nastavením příslušných podmínek v klauzuli WHERE k načtení dat ze tří tabulek a vygenerování vhodnějšího výstupu než předchozí dotazy SELECT.
VYBRAT studenty.názevtak jako ``Jméno studenta``, kurzy.názevtak jako ``Název kurzu``,MAX(PŘÍPAD KDY result.mark_type ='Interní zkouška'POTOM výsledek. Značky KONEC) 'CT',
MAX(PŘÍPAD KDY result.mark_type ='Pololetní zkoušky'POTOM výsledek. Značky KONEC) 'Střední',
MAX(PŘÍPAD KDY result.mark_type ='Závěrečná zkouška'POTOM výsledek. Značky KONEC) 'Finále',
SOUČET(výsledek.značky) tak jakoCelkový
OD studentů, kurzy, výsledek
WHERE result.std_id = students.id a result.course_id = courses.course_id
SKUPINA PODLE result.std_id, result.course_id
OBJEDNAT PODLE result.std_id, result.course_id ASC;
Následující výstup se vygeneruje po provedení výše uvedeného dotazu.
Závěr:
Jak můžete implementovat funkčnost funkce Pivot () bez podpory funkce Pivot () v MySQL, je v tomto článku ukázáno pomocí některých fiktivních dat. Doufám, že čtenáři budou schopni po přečtení tohoto článku transformovat jakákoli data na úrovni řádků na data na úrovni sloupců.