Condiție preliminară:
Trebuie să creați o bază de date și câteva tabele conexe, unde rândurile unui tabel vor fi convertite în coloane, cum ar fi funcția PIVOT (). Rulați următoarele instrucțiuni SQL pentru a crea o bază de date numită „ unidb Și creați trei tabele numite „ elevi ',' cursuri ' și ' rezultat '. elevi și rezultat tabelele vor fi legate de o relație unu-la-mulți și cursuri și rezultate tabelele vor fi legate de o relație unu-la-mulți aici. Declarația CREATE a rezultat tabelul conține două constrângeri cheie străine pentru câmpuri, std_id , și curs_id .
CREAȚI BAZA DE DATE unidb;
USE unidb;
CREAȚI elevi de masă(
idCHEIE PRIMARĂ INT,
nume varchar(cincizeci)NU NUL,
departamentul VARCHAR(cincisprezece)NU NUL);
CREAȚI cursuri de masă(
course_id VARCHAR(douăzeci)CHEIA PRINCIPALA,
nume varchar(cincizeci)NU NUL,
credit SMALLINT NOT NULL);
Creați rezultatul TABLE(
std_id INT NOT NULL,
course_id VARCHAR(douăzeci)NU NUL,
mark_type VARCHAR(douăzeci)NU NUL,
marchează SMALLINT NOT NULL,
CHEIE EXTERNĂ(std_id)REFERINȚE studenți(id),
CHEIE EXTERNĂ(curs_id)CURSURI DE REFERINȚE(curs_id),
CHEIA PRINCIPALA(std_id, course_id, mark_type));
Introduceți câteva înregistrări în studenți, cursuri și rezultate Mese. Valorile trebuie inserate în tabele pe baza restricțiilor stabilite în momentul creării tabelului.
INSERAȚI ÎN VALORILE elevilor
( „1937463”,„Harper Lee”,„CSE”),
( „1937464”,„Garcia Marquez”,„CSE”),
( „1937465”,„Forster, E.M.”,„CSE”),
( „1937466”,„Ralph Ellison”,„CSE”);
INSERAȚI ÎN VALOARE cursuri
( „CSE-401”,„Programare orientată pe obiecte”,3),
( „CSE-403”,'Structură de date',2),
( „CSE-407”,„Programare Unix”,2);
INSERAȚI ÎN VALORILE rezultatelor
( „1937463”,„CSE-401”,„Examen intern”,cincisprezece),
( „1937463”,„CSE-401”,'Examen la mijlocul perioadei',douăzeci),
( „1937463”,„CSE-401”,'Examenul final',35),
( „1937464”,„CSE-403”,„Examen intern”,17),
( „1937464”,„CSE-403”,'Examen la mijlocul perioadei',cincisprezece),
( „1937464”,„CSE-403”,'Examenul final',30),
( „1937465”,„CSE-401”,„Examen intern”,18),
( „1937465”,„CSE-401”,'Examen la mijlocul perioadei',2. 3),
( „1937465”,„CSE-401”,'Examenul final',38),
( „1937466”,„CSE-407”,„Examen intern”,douăzeci),
( „1937466”,„CSE-407”,'Examen la mijlocul perioadei',22),
( „1937466”,„CSE-407”,'Examenul final',40);
Aici, rezultat tabelul conține mai multe valori identice pentru std_id , marca_tip și curs_id coloane în fiecare rând. Modul de conversie a acestor rânduri în coloane ale acestui tabel pentru afișarea datelor într-un format mai organizat este prezentat în următoarea parte a acestui tutorial.
Rotiți rândurile în coloane utilizând instrucțiunea CASE:
Rulați următoarea instrucțiune SELECT simplă pentru a afișa toate înregistrările fișierului rezultat masa.
SELECTAȚI*FROM rezultat;
Rezultatul arată cele patru note ale elevilor pentru trei tipuri de examene de trei cursuri. Deci valorile std_id , curs_id și marca_tip sunt repetate de mai multe ori pentru diferiți studenți, cursuri și tipuri de examene.
Ieșirea va fi mai ușor de citit dacă interogarea SELECT poate fi scrisă mai eficient utilizând instrucțiunea CASE. Următorul SELECT cu instrucțiunea CASE va transforma valorile repetate ale rândurilor în numele coloanelor și va afișa conținutul tabelelor într-un format mai ușor de înțeles pentru utilizator.
SELECT result.std_id, result.course_id,MAX(CAZ CÂND rezultatul.mark_type =„Examen intern”ATUNCI rezultat.semnuri ÎNCHEI) „Examen intern”,
MAX(CAZ CÂND rezultatul.mark_type ='Examen la mijlocul perioadei'ATUNCI rezultat.semnuri ÎNCHEI) 'Examen la mijlocul perioadei',
MAX(CAZ CÂND rezultat.mark_type ='Examenul final'ATUNCI rezultat.semne END) 'Examenul final'
DE la rezultat
GROUP BY result.std_id, result.course_id
ORDIN BY result.std_id, result.course_id ASC;
Următoarea ieșire va apărea după executarea instrucțiunii de mai sus, care este mai ușor de citit decât ieșirea anterioară.
Rotiți rândurile în coloane folosind CASE și SUM ():
Dacă doriți să numărați din tabel numărul total al fiecărui curs al fiecărui elev, atunci trebuie să utilizați funcția agregată SUMĂ() a se grupa cu std_id și curs_id cu declarația CASE. Următoarea interogare este creată prin modificarea interogării anterioare cu funcția SUM () și clauza GROUP BY.
SELECT result.std_id, result.course_id,MAX(CAZ CÂND rezultat.mark_type =„Examen intern”ATUNCI rezultat.semnuri ÎNCHEI) „Examen intern”,
MAX(CAZ CÂND rezultatul.mark_type ='Examen la mijlocul perioadei'ATUNCI rezultat.semne END) 'Examen la mijlocul perioadei',
MAX(CAZ CÂND rezultat.mark_type ='Examenul final'ATUNCI rezultat.semne END) 'Examenul final',
SUMĂ(rezultat.markuri) la fel deTotal
DE la rezultat
GROUP BY result.std_id, result.course_id
ORDER BY result.std_id, result.course_id ASC;
Ieșirea arată o nouă coloană numită Total care afișează suma notelor tuturor tipurilor de examene ale fiecărui curs obținute de fiecare student.
Rotiți rândurile în coloane în mai multe tabele:
Cele două interogări anterioare sunt aplicate la rezultat masa. Acest tabel este legat de celelalte două tabele. Acestea sunt elevi și cursuri . Dacă doriți să afișați numele studentului în loc de ID-ul studentului și numele cursului în loc de id-ul cursului, atunci trebuie să scrieți interogarea SELECT folosind trei tabele corelate, elevi , cursuri și rezultat . Următoarea interogare SELECT este creată prin adăugarea a trei nume de tabele după clauza FORM și setarea condițiilor adecvate în clauza WHERE pentru a extrage datele din cele trei tabele și a genera rezultate mai adecvate decât interogările SELECT anterioare.
SELECTAȚI elevii.numela fel de „Numele studentului„, cursuri.numela fel de „Numele cursului„,MAX(CAZ CÂND rezultatul.mark_type =„Examen intern”ATUNCI rezultat.semne END) 'CT',
MAX(CAZ CÂND rezultat.mark_type ='Examen la mijlocul perioadei'ATUNCI rezultat.semnuri ÎNCHEI) „Mijloc”,
MAX(CAZ CÂND rezultatul.mark_type ='Examenul final'ATUNCI rezultat.semne END) 'Final',
SUMĂ(rezultat.markuri) la fel deTotal
DE LA studenți, cursuri, rezultat
UNDE result.std_id = students.id și result.course_id = courses.course_id
GROUP BY result.std_id, result.course_id
ORDIN BY result.std_id, result.course_id ASC;
Următoarea ieșire se va genera după executarea interogării de mai sus.
Concluzie:
Modul în care puteți implementa funcționalitatea funcției Pivot () fără suportul funcției Pivot () în MySQL este prezentat în acest articol folosind unele date inexact. Sper că cititorii vor putea transforma orice date la nivel de rând în date la nivel de coloană utilizând interogarea SELECT după ce au citit acest articol.