Előfeltétel:
Létre kell hoznia egy adatbázist és néhány kapcsolódó táblázatot, ahol az egyik táblázat sorait a PIVOT () függvényhez hasonló oszlopokká alakítják át. Futtassa a következő SQL utasításokat a 'nevű adatbázis létrehozásához' unidb ', És hozzon létre három táblázatot' diákok ',' tanfolyamok 'És' eredmény '. diákok és eredmény táblázatok egy-sok kapcsolathoz kapcsolódnak majd és tanfolyamok és eredmények táblázatok itt egy-sok kapcsolathoz kapcsolódnak. CREATE nyilatkozata a eredmény a táblázat két idegen kulcsra vonatkozó korlátozást tartalmaz a mezőkhöz, std_id , és tanfolyam_azonosítója .
ADATBÁZIS LÉTREHOZÁSA unidb;
USE unidb;
TÁBLÁZAT LÉTREHOZÁSA(
idBELSŐ GOMB,
név varchar(ötven)NEM NULLA,
osztály VARCHAR(tizenöt)NEM NULLA);
TABLE tanfolyamok létrehozása(
course_id VARCHAR(húsz)ELSŐDLEGES KULCS,
név varchar(ötven)NEM NULLA,
hitel SMALLINT NEM NULL);
TÁBLÁZAT LÉTREHOZÁSA(
std_id INT NOT NULL,
course_id VARCHAR(húsz)NEM NULLA,
mark_type VARCHAR(húsz)NEM NULLA,
jelzi a SMALLINT NOT NULL értéket,
IDEGEN KULCS(std_id)IRODALOM diákok(id),
IDEGEN KULCS(tanfolyam_azonosítója)REFERENCES tanfolyamok(tanfolyam_azonosítója),
ELSŐDLEGES KULCS(std_id, course_id, mark_type));
Helyezzen be néhány rekordot hallgatók, tanfolyamok és az eredmény táblázatok. Az értékeket a táblázatok létrehozásakor meghatározott korlátozások alapján kell beilleszteni a táblázatokba.
INSERT INTO INTES a diákok értékeit
( '1937463','Harper Lee','CSE'),
( '1937464','Garcia Marquez','CSE'),
( '1937465','Forster, E.M.','CSE'),
( '1937466','Ralph Ellison','CSE');
INSERT INTO kurzusokba ÉRTÉKEK
( 'CSE-401','Objektumorientált programozás',3),
( 'CSE-403','Adatszerkezet',2),
( 'CSE-407','Unix programozás',2);
INSERT INTO result VALUES
( '1937463','CSE-401','Belső vizsga',tizenöt),
( '1937463','CSE-401','Félidős vizsga',húsz),
( '1937463','CSE-401','Záróvizsga',35),
( '1937464','CSE-403','Belső vizsga',17),
( '1937464','CSE-403','Félidős vizsga',tizenöt),
( '1937464','CSE-403','Záróvizsga',30),
( '1937465','CSE-401','Belső vizsga',18),
( '1937465','CSE-401','Félidős vizsga',2. 3),
( '1937465','CSE-401','Záróvizsga',38),
( '1937466','CSE-407','Belső vizsga',húsz),
( '1937466','CSE-407','Félidős vizsga',22),
( '1937466','CSE-407','Záróvizsga',40);
Itt, eredmény táblázat több azonos értéket tartalmaz a std_id , mark_type és tanfolyam_azonosítója oszlopokat minden sorban. Az oktatóanyag következő részében bemutatjuk, hogyan lehet ezeket a sorokat átalakítani ennek a táblázatnak az oszlopaiba, hogy az adatok rendszerezettebben jelenjenek meg.
Sorok forgatása oszlopokba a CASE utasítás használatával:
Futtassa a következő egyszerű SELECT utasítást a. Összes rekordjának megjelenítéséhez eredmény asztal.
SELECT*FROM eredményből;
A kimenet három tanfolyam három vizsgatípusának négy hallgatói osztályzatát mutatja. Tehát az értékei std_id , tanfolyam_azonosítója és mark_type többször megismétlődnek a különböző hallgatók, tanfolyamok és vizsgatípusok esetében.
A kimenet olvashatóbb lesz, ha a SELECT lekérdezés hatékonyabban írható a CASE utasítás használatával. A következő SELECT a CASE utasítással átalakítja a sorok ismétlődő értékeit oszlopnévvé, és a táblázatok tartalmát a felhasználó számára érthetőbb formában jeleníti meg.
SELECT result.std_id, result.course_id,MAX(CASE WHEN result.mark_type ='Belső vizsga'AKKOR eredmény.jelek VÉGE) 'Belső vizsga',
MAX(CASE WHEN result.mark_type ='Félidős vizsga'AKKOR eredmény.jelek VÉGE) 'Félidős vizsga',
MAX(CASE WHEN result.mark_type ='Záróvizsga'AKKOR eredmény.jelek VÉGE) 'Záróvizsga'
Eredményből
GROUP BY result.std_id, result.course_id
ORDER BY result.std_id, result.course_id ASC;
A következő kimenet jelenik meg a fenti utasítás futtatása után, amely jobban olvasható, mint az előző kimenet.
Sorok forgatása oszlopokba CASE és SUM () használatával:
Ha a táblázatból meg szeretné számolni minden tanuló összes kurzusának számát, akkor az összesítő függvényt kell használnia ÖSSZEG() csoportosít std_id és tanfolyam_azonosítója a CASE utasítással. A következő lekérdezés az előző lekérdezés SUM () függvénnyel és GROUP BY záradékkal történő módosításával jön létre.
SELECT result.std_id, result.course_id,MAX(CASE WHEN result.mark_type ='Belső vizsga'AKKOR eredmény.jelek VÉGE) 'Belső vizsga',
MAX(CASE WHEN result.mark_type ='Félidős vizsga'AKKOR eredmény.jelek VÉGE) 'Félidős vizsga',
MAX(CASE WHEN result.mark_type ='Záróvizsga'AKKOR eredmény.jelek VÉGE) 'Záróvizsga',
ÖSSZEG(eredmény.jelek) mintTeljes
Eredményből
GROUP BY result.std_id, result.course_id
ORDER BY result.std_id, result.course_id ASC;
A kimenet egy új oszlopot mutat Teljes azaz az egyes hallgatók által az egyes tanfolyamok összes vizsgatípusának érdemjegyeinek összegét jeleníti meg.
Sorok forgatása oszlopokba több táblázatban:
Az előző két lekérdezés vonatkozik a eredmény asztal. Ez a táblázat a másik két táblához kapcsolódik. Ezek diákok és tanfolyamok . Ha a hallgató azonosítója helyett a tanuló nevét és a tanfolyam azonosítója helyett a tanfolyam nevét szeretné megjeleníteni, akkor a SELECT lekérdezést három kapcsolódó táblázat segítségével kell megírnia, diákok , tanfolyamok és eredmény . A következő SELECT lekérdezés úgy jön létre, hogy a FORM záradék után három táblanevet ad hozzá, és a WHERE záradékban megfelelő feltételeket állít be, hogy lekérje az adatokat a három táblából, és megfelelőbb kimenetet generáljon, mint az előző SELECT lekérdezések.
KIVÁLASZTANI a diákokat.névmint ``Tanuló név``, tanfolyamok.nevemint ``A tantárgy neve``,MAX(CASE WHEN result.mark_type ='Belső vizsga'AKKOR eredmény.jelek VÉGE) 'CT',
MAX(CASE WHEN result.mark_type ='Félidős vizsga'AKKOR eredmény.jelek VÉGE) 'Középső',
MAX(CASE WHEN result.mark_type ='Záróvizsga'AKKOR eredmény.jelek VÉGE) 'Végső',
ÖSSZEG(eredmény.jelek) mintTeljes
Hallgatóktól, tanfolyamok, eredmény
WHERE result.std_id = hallgatói azonosító és eredmény.tanfolyam_azonosító = tanfolyamok.tanfolyam_azonosító
GROUP BY result.std_id, result.course_id
ORDER BY result.std_id, result.course_id ASC;
A fenti kimenet a fenti lekérdezés végrehajtása után jön létre.
Következtetés:
A Pivot () függvény funkcionalitásának megvalósítását a MySQL -ben a Pivot () függvény támogatása nélkül, ebben a cikkben néhány hamis adat felhasználásával mutatjuk be. Remélem, a cikk elolvasása után az olvasók képesek lesznek a sor szintű adatokat oszlopszintű adatmá alakítani a SELECT lekérdezés használatával.