MySQL Pivot: rivien kääntäminen sarakkeisiin

Mysql Pivot Rotating Rows Columns

Tietokantataulukko voi tallentaa erityyppisiä tietoja, ja joskus meidän on muutettava rivitason tiedot sarakkeen tason tiedoiksi. Tämä ongelma voidaan ratkaista käyttämällä PIVOT () -toimintoa. Tätä toimintoa käytetään kääntämään taulukon rivit sarakearvoiksi. Tätä toimintoa tukevat kuitenkin vain harvat tietokantapalvelimet, kuten Oracle tai SQL Server. Jos haluat tehdä saman tehtävän MySQL -tietokantataulukossa, sinun on kirjoitettava SELECT -kysely CASE -käskyä käyttämällä, jotta rivit voidaan kääntää sarakkeiksi. Artikkeli näyttää tavan suorittaa PIVOT () -toiminnon tehtävät liittyvissä MySQL -tietokantataulukoissa.

Edellytys:

Sinun on luotava tietokanta ja joitakin siihen liittyviä taulukoita, joissa yhden taulukon rivit muunnetaan sarakkeiksi, kuten PIVOT () -funktio. Suorita seuraavat SQL -käskyt ja luo tietokanta nimeltä ' unidb 'Ja luo kolme taulukkoa nimeltä' opiskelijat ',' kurssit 'Ja' tulos ''. opiskelijat ja tulos taulukot liittyvät yhdestä moniin -suhteella ja kurssit ja tuloksia taulukot liittyvät tässä yksi-moniin -suhteeseen. CREATE -lausunto tulos taulukko sisältää kaksi kenttien vieraan avaimen rajoitusta, std_id ja kurssin_tunnus .

CREATE DATABASE unidb;
KÄYTÄ unidb;

LUO TAULUKKO -opiskelijat(
idALKUPERÄINEN AVAIN,
nimi varchar(viisikymmentä)EI TYHJÄ,
osasto VARCHAR(viisitoista)EI TYHJÄ);

LUO TAULUKON kursseja(
course_id VARCHAR(kaksikymmentä)PÄÄAVAIN,
nimi varchar(viisikymmentä)EI TYHJÄ,
luotto SMALLINT EI NULL);

LUO TAULUKON tulos(
std_id INT NOT NULL,
course_id VARCHAR(kaksikymmentä)EI TYHJÄ,
mark_type VARCHAR(kaksikymmentä)EI TYHJÄ,
merkitsee SMALLINT NOT NULL,
ULKOMAINEN AVAIN(std_id)VIITTEET opiskelijat(id),
ULKOMAINEN AVAIN(kurssin_tunnus)REFERENSES kurssit(kurssin_tunnus),
PÄÄAVAIN(std_id, course_id, mark_type));

Lisää joitakin tietueita opiskelijat, kurssit ja tulos taulukot. Arvot on lisättävä taulukoihin taulukon luontihetkellä asetettujen rajoitusten perusteella.



INSERT INTO opiskelijoille ARVOT
( '1937463','Harper Lee',CSE),
( '1937464','Garcia Marquez',CSE),
( '1937465','Forster, E.M.',CSE),
( '1937466',Ralph Ellison,CSE);

INSERT INTO kurssit ARVOT
( 'CSE-401','Olio-ohjelmointi',3),
( 'CSE-403','Tietorakenne',2),
( 'CSE-407','Unix -ohjelmointi',2);

INSERT INTO tulos ARVOT
( '1937463','CSE-401','Sisäinen tentti',viisitoista),
( '1937463','CSE-401','Väliaika tentti',kaksikymmentä),
( '1937463','CSE-401','Loppukoe',35),
( '1937464','CSE-403','Sisäinen tentti',17),
( '1937464','CSE-403','Väliaika tentti',viisitoista),
( '1937464','CSE-403','Loppukoe',30),
( '1937465','CSE-401','Sisäinen tentti',18),
( '1937465','CSE-401','Väliaika tentti',2. 3),
( '1937465','CSE-401','Loppukoe',38),
( '1937466','CSE-407','Sisäinen tentti',kaksikymmentä),
( '1937466','CSE-407','Väliaika tentti',22),
( '1937466','CSE-407','Loppukoe',40);

Tässä, tulos taulukko sisältää useita samoja arvoja kohteelle std_id , mark_type ja kurssin_tunnus sarakkeita jokaisella rivillä. Näiden rivien muuntaminen tämän taulukon sarakkeiksi tietojen näyttämiseksi järjestäytyneemmässä muodossa esitetään tämän opetusohjelman seuraavassa osassa.



Kierrä rivejä sarakkeisiin CASE -lausekkeen avulla:

Suorita seuraava yksinkertainen SELECT -lause näyttääksesi kaikki tietueet tulos pöytä.

VALITSE*FROM tuloksesta;

Tulos näyttää neljän opiskelijan arvosanat kolmen kurssin kolmesta tenttityypistä. Joten arvot std_id , kurssin_tunnus ja mark_type toistetaan useita kertoja eri opiskelijoille, kursseille ja tenttityypeille.



Tulos on luettavampi, jos SELECT -kysely voidaan kirjoittaa tehokkaammin käyttämällä CASE -käskyä. Seuraava SELECT, jossa on CASE -käsky, muuttaa rivien toistuvat arvot sarakkeiden nimiksi ja näyttää taulukoiden sisällön käyttäjälle ymmärrettävämmässä muodossa.

SELECT result.std_id, result.course_id,
MAX(CASE WHEN result.mark_type ='Sisäinen tentti'THEN result. Merkit END) 'Sisäinen tentti',
MAX(CASE WHEN result.mark_type ='Väliaika tentti'THEN result. Merkit END) 'Väliaika tentti',
MAX(CASE WHEN result.mark_type ='Loppukoe'THEN result. Merkit END) 'Loppukoe'
FROM tuloksesta
GROUP BY result.std_id, result.course_id
TILAA TULOS.td_id, result.course_id ASC;

Seuraava tuloste tulee näkyviin, kun yllä oleva lause on suoritettu, mikä on luettavampaa kuin edellinen lähtö.

Kierrä rivejä sarakkeisiin CASE- ja SUM () -painikkeilla:

Jos haluat laskea taulukosta jokaisen oppilaan jokaisen kurssin kokonaismäärän, sinun on käytettävä aggregaattitoimintoa SUMMA() ryhmitellä std_id ja kurssin_tunnus CASE -lausunnon kanssa. Seuraava kysely luodaan muokkaamalla edellistä kyselyä SUM () -funktiolla ja GROUP BY -lausekkeella.

SELECT result.std_id, result.course_id,
MAX(CASE WHEN result.mark_type ='Sisäinen tentti'THEN result. Merkit END) 'Sisäinen tentti',
MAX(CASE WHEN result.mark_type ='Väliaika tentti'THEN result. Merkit END) 'Väliaika tentti',
MAX(CASE WHEN result.mark_type ='Loppukoe'THEN result. Merkit END) 'Loppukoe',
SUMMA(tulos. merkit) kutenKaikki yhteensä
FROM tuloksesta
GROUP BY result.std_id, result.course_id
TILAA TULOS.td_id, result.course_id ASC;

Tulos näyttää uuden sarakkeen nimeltä Kaikki yhteensä joka näyttää kunkin oppilaan kunkin kurssin kaikkien tenttityyppien arvosanojen summan.

Kierrä rivit sarakkeiksi useissa taulukoissa:

Kahta edellistä kyselyä sovelletaan tulos pöytä. Tämä taulukko liittyy kahteen muuhun taulukkoon. Nämä ovat opiskelijat ja kurssit . Jos haluat näyttää opiskelijan nimen opiskelijan tunnuksen sijaan ja kurssin nimen kurssin tunnuksen sijaan, sinun on kirjoitettava SELECT -kysely käyttämällä kolmea asiaan liittyvää taulukkoa, opiskelijat , kurssit ja tulos . Seuraava SELECT -kysely luodaan lisäämällä kolme taulukon nimeä FORM -lausekkeen jälkeen ja asettamalla WHERE -lausekkeeseen asianmukaiset ehdot, jotta tiedot voidaan noutaa kolmesta taulukosta ja luoda aiempaa SELECT -kyselyä sopivampi tulos.

VALITSE oppilaat. Nimikuten ``Opiskelijan nimi``, kurssit.nimikuten ``Kurssin nimi``,
MAX(CASE WHEN result.mark_type ='Sisäinen tentti'THEN result. Merkit END) 'CT',
MAX(CASE WHEN result.mark_type ='Väliaika tentti'THEN result. Merkit END) 'Mid',
MAX(CASE WHEN result.mark_type ='Loppukoe'THEN result. Merkit END) 'Lopullinen',
SUMMA(tulos. merkit) kutenKaikki yhteensä
Opiskelijoilta, kursseilta, tulos
WHERE result.std_id = opiskelijatunnus ja tulos.course_id = course.course_id
GROUP BY result.std_id, result.course_id
TILAA TULOS.td_id, result.course_id ASC;

Seuraava tulos luodaan yllä olevan kyselyn suorittamisen jälkeen.

Johtopäätös:

Tässä artikkelissa kerrotaan, kuinka voit toteuttaa Pivot () -toiminnon toiminnot ilman MySQL: n Pivot () -toiminnon tukea. Toivon, että lukijat voivat muuttaa minkä tahansa rivitason datan sarakkeen tason dataksi käyttämällä SELECT-kyselyä tämän artikkelin lukemisen jälkeen.