MySQL Pivot: rotiranje redaka u stupce

Mysql Pivot Rotating Rows Columns



Tablica baze podataka može pohraniti različite vrste podataka, a ponekad moramo pretvoriti podatke na razini retka u podatke na razini stupca. Ovaj se problem može riješiti upotrebom funkcije PIVOT (). Ova se funkcija koristi za rotiranje redaka tablice u vrijednosti stupaca. No ovu funkciju podržava vrlo malo poslužitelja baza podataka, poput Oraclea ili SQL Servera. Ako želite napraviti isti zadatak u tablici baze podataka MySQL, tada morate napisati upit SELECT pomoću izraza CASE da biste rotirali retke u stupce. Članak prikazuje način izvršavanja zadatka funkcije PIVOT () unutar povezanih tablica baze podataka MySQL.

Preduvjet:

Morate stvoriti bazu podataka i neke povezane tablice u kojima će se redovi jedne tablice pretvoriti u stupce poput funkcije PIVOT (). Pokrenite sljedeće SQL izraze za stvaranje baze podataka pod nazivom ' unidb 'I izraditi tri tablice pod nazivom' studenti ',' tečajevi 'I' proizlaziti '. studenti i proizlaziti tablice će biti povezane odnosom jedan prema više i tečajevi i rezultate tablice će ovdje biti povezane odnosom jedan prema više. CREATE izjava datoteke proizlaziti tablica sadrži dva ograničenja stranog ključa za polja, std_id , i tečaj_id .







CREATE DATABASE unidb;
KORISTITE unidb;

IZRADI UČENIKE STOLA(
iskaznicaINT PRIMARNI KLJUČ,
ime varchar(pedeset)NIJE NULA,
odjel VARCHAR(petnaest)NIJE NULA);

CREATE TABLE tečajevi(
tečaj_id VARCHAR(dvadeset)GLAVNI KLJUČ,
ime varchar(pedeset)NIJE NULA,
kredit SMALLINT NOT NULL);

CREATE TABLE rezultat(
std_id INT NOT NULL,
tečaj_id VARCHAR(dvadeset)NIJE NULA,
oznaka_tip VARCHAR(dvadeset)NIJE NULA,
oznake SMALLINT NOT NULL,
STRANI KLJUC(std_id)LITERATURA studenti(iskaznica),
STRANI KLJUC(tečaj_id)LITERATURA tečajevi(tečaj_id),
GLAVNI KLJUČ(std_id, course_id, mark_type));

Umetnite neke zapise u studenti, tečajevi i rezultat tablice. Vrijednosti se moraju umetnuti u tablice na temelju ograničenja postavljenih u vrijeme izrade tablice.



UMETNITE VRIJEDNOSTI učenika
( '1937463','Harper Lee','CSE'),
( '1937464','Garcia Marquez','CSE'),
( '1937465','Forster, E.M.','CSE'),
( '1937466','Ralph Ellison','CSE');

UMETAJTE VRIJEDNOSTI tečajeva
( 'CSE-401','Objektno orijentirano programiranje',3),
( 'CSE-403','Struktura podataka',2),
( 'CSE-407','Unix programiranje',2);

UMETNI VRIJEDNOSTI rezultata
( '1937463','CSE-401','Interni ispit',petnaest),
( '1937463','CSE-401','Srednjoročni ispit',dvadeset),
( '1937463','CSE-401','Završni ispit',35),
( '1937464','CSE-403','Interni ispit',17),
( '1937464','CSE-403','Srednjoročni ispit',petnaest),
( '1937464','CSE-403','Završni ispit',30),
( '1937465','CSE-401','Interni ispit',18),
( '1937465','CSE-401','Srednjoročni ispit',2. 3),
( '1937465','CSE-401','Završni ispit',38),
( '1937466','CSE-407','Interni ispit',dvadeset),
( '1937466','CSE-407','Srednjoročni ispit',22),
( '1937466','CSE-407','Završni ispit',40);

Ovdje, proizlaziti tablica sadrži više istih vrijednosti za std_id , oznaka_vrsta i tečaj_id stupce u svakom retku. Kako pretvoriti ove retke u stupce ove tablice za prikaz podataka u uređenijem formatu prikazano je u sljedećem dijelu ovog vodiča.



Rotirajte retke u stupce pomoću izraza CASE:

Pokrenite sljedeću jednostavnu naredbu SELECT za prikaz svih zapisa datoteke proizlaziti stol.





ODABERI*IZ rezultata;

Rezultat prikazuje četiri studentske ocjene za tri vrste ispita iz tri predmeta. Dakle vrijednosti std_id , tečaj_id i oznaka_vrsta ponavljaju se više puta za različite studente, tečajeve i vrste ispita.



Izlaz će biti čitljiviji ako se upit SELECT može učinkovitije napisati upotrebom izraza CASE. Sljedeći SELECT s izrazom CASE transformirat će ponavljajuće se retke u nazive stupaca i prikazati sadržaj tablica u razumljivijem formatu za korisnika.

SELECT result.std_id, result.course_id,
MAKS(SLUČAJ KADA rezultat.mark_type ='Interni ispit'ONDA rezultat.znake KRAJ) 'Interni ispit',
MAKS(SLUČAJ KADA rezultat.mark_type ='Srednjoročni ispit'ONDA rezultat.znake KRAJ) 'Srednjoročni ispit',
MAKS(SLUČAJ KADA rezultat.mark_type ='Završni ispit'ONDA rezultat.znake KRAJ) 'Završni ispit'
OD rezultata
GROUP BY result.std_id, result.course_id
ORDER BY result.std_id, result.course_id ASC;

Sljedeći izlaz pojavit će se nakon izvođenja gornje naredbe koja je čitljivija od prethodnog.

Rotirajte retke u stupce pomoću CASE i SUM ():

Ako želite iz tablice izbrojati ukupan broj svakog predmeta svakog studenta, tada morate upotrijebiti zbirnu funkciju IZNOS() grupiraj po std_id i tečaj_id s izjavom CASE. Sljedeći upit nastaje mijenjanjem prethodnog upita funkcijom SUM () i klauzulom GROUP BY.

SELECT result.std_id, result.course_id,
MAKS(SLUČAJ KADA rezultat.mark_type ='Interni ispit'ONDA rezultat.znake KRAJ) 'Interni ispit',
MAKS(SLUČAJ KADA rezultat.mark_type ='Srednjoročni ispit'ONDA rezultat.znake KRAJ) 'Srednjoročni ispit',
MAKS(SLUČAJ KADA rezultat.mark_type ='Završni ispit'ONDA rezultat.znake KRAJ) 'Završni ispit',
IZNOS(rezultat.znake) kaoUkupno
OD rezultata
GROUP BY result.std_id, result.course_id
ORDER BY result.std_id, result.course_id ASC;

Izlaz prikazuje novi stupac pod nazivom Ukupno koji prikazuje zbroj ocjena svih vrsta ispita svakog predmeta koje je dobio svaki pojedini student.

Rotirajte retke u stupce u više tablica:

Prethodna dva upita primjenjuju se na proizlaziti stol. Ova tablica je povezana s druge dvije tablice. Ovi su studenti i tečajevi . Ako želite prikazati ime studenta umjesto ID -a studenta i naziv predmeta umjesto ID -a tečaja, tada morate upisati SELECT upit koristeći tri povezane tablice, studenti , tečajevi i proizlaziti . Sljedeći SELECT upit nastaje dodavanjem tri naziva tablice nakon klauzule FORM i postavljanjem odgovarajućih uvjeta u klauzuli WHERE za dohvaćanje podataka iz tri tablice i generiranje prikladnijeg izlaza od prethodnih upita SELECT.

ODABERI studente.imekao ``Ime studenta``, tečajevi.imekao ``Naziv predmeta``,
MAKS(SLUČAJ KADA rezultat.mark_type ='Interni ispit'ONDA rezultat.znake KRAJ) 'CT',
MAKS(SLUČAJ KADA rezultat.mark_type ='Srednjoročni ispit'ONDA rezultat.znake KRAJ) 'Sredina',
MAKS(SLUČAJ KADA rezultat.mark_type ='Završni ispit'ONDA rezultat.znake KRAJ) 'Konačno',
IZNOS(rezultat.znake) kaoUkupno
OD studenata, tečajevi, rezultat
GDJE je result.std_id = students.id i result.course_id = courses.course_id
GROUP BY result.std_id, result.course_id
ORDER BY result.std_id, result.course_id ASC;

Sljedeći izlaz generirat će se nakon izvršavanja gornjeg upita.

Zaključak:

Kako možete implementirati funkcionalnost funkcije Pivot () bez podrške funkcije Pivot () u MySQL -u prikazano je u ovom članku pomoću nekih lažnih podataka. Nadam se da će čitatelji moći pročitati sve podatke na razini retka u podatke na razini stupca pomoću upita SELECT nakon čitanja ovog članka.