Predpogoj:
Ustvariti morate bazo podatkov in nekaj sorodnih tabel, kjer bodo vrstice ene tabele pretvorjene v stolpce, kot je funkcija PIVOT (). Zaženite naslednje stavke SQL, da ustvarite bazo podatkov z imenom ' unidb 'In ustvarite tri tabele z imenom' študentov ',' tečaji 'In' rezultat '. študentov in rezultat tabele bodo povezane z razmerjem ena proti več in tečaji in rezultatov tabele bodo tukaj povezane z razmerjem ena proti več. CREATE stavek rezultat tabela vsebuje dve omejitvi tujih ključev za polja, std_id , in course_id .
Ustvari bazo podatkov unidb;
UPORABI unidb;
Ustvarite študente(
idINT PRIMARNI KLJUČ,
ime varchar(petdeset)NI NULL,
oddelek VARCHAR(petnajst)NI NULL);
Ustvari tečaje MIZE(
course_id VARCHAR(dvajset)PRIMARNI KLJUČ,
ime varchar(petdeset)NI NULL,
kredit SMALLINT NOT NULL);
Ustvari rezultat tabele(
std_id INT NOT NULL,
course_id VARCHAR(dvajset)NI NULL,
mark_type VARCHAR(dvajset)NI NULL,
oznake SMALLINT NOT NULL,
TUJI KLJUČ(std_id)LITERATURA študentje(id),
TUJI KLJUČ(course_id)REFERENCE tečaji(course_id),
PRIMARNI KLJUČ(std_id, course_id, mark_type));
Vstavite nekaj zapisov v študenti, tečaji in rezultat mize. Vrednosti je treba vnesti v tabele na podlagi omejitev, določenih v času ustvarjanja tabele.
VSTAVITE Vrednosti študentov
( '1937463','Harper Lee','CSE'),
( '1937464','Garcia Marquez','CSE'),
( '1937465','Forster, E.M.','CSE'),
( '1937466','Ralph Ellison','CSE');
VSTAVITE VREDNOSTI tečajev
( 'CSE-401','Objektno usmerjeno programiranje',3),
( 'CSE-403','Struktura podatkov',2),
( 'CSE-407','Unix programiranje',2);
VSTAVI VREDNOSTI rezultata
( '1937463','CSE-401','Notranji izpit',petnajst),
( '1937463','CSE-401','Vmesni izpit',dvajset),
( '1937463','CSE-401','Končni izpit',35),
( '1937464','CSE-403','Notranji izpit',17),
( '1937464','CSE-403','Vmesni izpit',petnajst),
( '1937464','CSE-403','Končni izpit',30),
( '1937465','CSE-401','Notranji izpit',18),
( '1937465','CSE-401','Vmesni izpit',2. 3),
( '1937465','CSE-401','Končni izpit',38),
( '1937466','CSE-407','Notranji izpit',dvajset),
( '1937466','CSE-407','Vmesni izpit',22),
( '1937466','CSE-407','Končni izpit',40);
Tukaj, rezultat tabela vsebuje več istih vrednosti za std_id , oznaka_vrsta in course_id stolpcev v vsaki vrstici. Kako pretvoriti te vrstice v stolpce te tabele za prikaz podatkov v bolj urejeni obliki, je prikazano v naslednjem delu te vadnice.
Zavrtite vrstice v stolpce z uporabo stavka CASE:
Če želite prikazati vse zapise datoteke rezultat miza.
IZBERI*IZ rezultatov;
Rezultat prikazuje štiri ocene študentov za tri vrste izpitov treh predmetov. Torej vrednote std_id , course_id in oznaka_vrsta se večkrat ponovijo za različne študente, tečaje in vrste izpitov.
Izhod bo bolj berljiv, če bo lahko poizvedbo SELECT učinkoviteje zapisali z uporabo stavka CASE. Naslednji SELECT z stavkom CASE bo ponavljajoče se vrednosti vrstic spremenil v imena stolpcev in prikazal vsebino tabel v razumljivejši obliki za uporabnika.
SELECT result.std_id, result.course_id,MAX(CASE WHEN result.mark_type ='Notranji izpit'TUDI rezultat.znaki se končajo) 'Notranji izpit',
MAX(CASE WHEN result.mark_type ='Vmesni izpit'TUDI rezultat.znaki se končajo) 'Vmesni izpit',
MAX(CASE WHEN result.mark_type ='Končni izpit'TUDI rezultat.znaki se končajo) 'Končni izpit'
IZ rezultata
GROUP BY result.std_id, result.course_id
ORDER BY result.std_id, result.course_id ASC;
Naslednji izhod se bo prikazal po zagonu zgornjega stavka, ki je bolj berljiv kot prejšnji izhod.
Zavrtite vrstice v stolpce z uporabo CASE in SUM ():
Če želite iz tabele prešteti skupno število vsakega predmeta vsakega študenta, morate uporabiti skupno funkcijo SUM () skupina po std_id in course_id z izjavo CASE. Naslednja poizvedba je ustvarjena s spreminjanjem prejšnje poizvedbe s funkcijo SUM () in stavkom GROUP BY.
SELECT result.std_id, result.course_id,MAX(CASE WHEN result.mark_type ='Notranji izpit'TUDI rezultat.znaki se končajo) 'Notranji izpit',
MAX(CASE WHEN result.mark_type ='Vmesni izpit'TUDI rezultat.znaki se končajo) 'Vmesni izpit',
MAX(CASE WHEN result.mark_type ='Končni izpit'TUDI rezultat.znaki se končajo) 'Končni izpit',
SUM(result.marks) kotSkupaj
IZ rezultata
GROUP BY result.std_id, result.course_id
ORDER BY result.std_id, result.course_id ASC;
Izhod prikazuje nov stolpec z imenom Skupaj ki prikazuje vsoto ocen vseh vrst izpitov vsakega predmeta, ki jih je pridobil vsak posamezen študent.
Zavrtite vrstice na stolpce v več tabelah:
Prejšnji dve poizvedbi sta uporabljeni za rezultat miza. Ta tabela je povezana z drugimi dvema. To so študentov in tečaji . Če želite prikazati ime študenta namesto ID študenta in ime predmeta namesto ID predmeta, morate napisati poizvedbo SELECT s pomočjo treh povezanih tabel, študentov , tečaji in rezultat . Naslednja poizvedba SELECT je ustvarjena z dodajanjem treh imen tabel za klavzulo FORM in nastavitvijo ustreznih pogojev v členu WHERE za pridobivanje podatkov iz treh tabel in ustvarjanje ustreznejšega rezultata kot prejšnje poizvedbe SELECT.
IZBERI študente.imekot ''Študentsko ime'', tečaji.imekot ''Ime tečaja'',MAX(CASE WHEN result.mark_type ='Notranji izpit'TUDI rezultat.znaki se končajo) 'CT',
MAX(CASE WHEN result.mark_type ='Vmesni izpit'TUDI rezultat.znaki se končajo) 'Sredina',
MAX(CASE WHEN result.mark_type ='Končni izpit'TUDI rezultat.znaki se končajo) 'Končno',
SUM(result.marks) kotSkupaj
OD študentov, tečaji, rezultat
KJE je result.std_id = students.id in result.course_id = courses.course_id
GROUP BY result.std_id, result.course_id
ORDER BY result.std_id, result.course_id ASC;
Naslednji izhod bo ustvarjen po izvedbi zgornje poizvedbe.
Zaključek:
Kako lahko uporabite funkcijo Pivot () brez podpore funkcije Pivot () v MySQL, je prikazano v tem članku z uporabo nekaj lažnih podatkov. Upam, da bodo bralci po branju tega članka z uporabo poizvedbe SELECT lahko pretvorili vse podatke na ravni vrstice v podatke na ravni stolpca.