Requisit previ:
Heu de crear una base de dades i algunes taules relacionades on les files d'una taula es convertiran en columnes com la funció PIVOT (). Executeu les sentències SQL següents per crear una base de dades anomenada ' unidb 'I creeu tres taules anomenades' estudiants ',' cursos 'I' resultat '. estudiants i resultat les taules estaran relacionades per una relació d'un a molts i cursos i resultats les taules estaran relacionades per una relació d'un a molts aquí. Sentència CREATE de la resultat la taula conté dues restriccions de clau externa per als camps, std_id , i id_curs .
CREA BASE DE DADES unidb;
USAR unidb;
CREA TAULA estudiants(
identificadorCLAU PRIMÀRIA INT,
nom varchar(50)NO NUL,
departament VARCHAR(15)NO NUL);
CREAR TAULA cursos(
curs_id VARCHAR(20)CLAU PRIMÀRIA,
nom varchar(50)NO NUL,
crèdit SMALLINT NO NUL);
Crea el resultat de la TAULA(
std_id INT NO NUL,
curs_id VARCHAR(20)NO NUL,
marca_tip VARCHAR(20)NO NUL,
marca SMALLINT NOT NULL,
CLAU ESTRANGERA(std_id)REFERÈNCIES estudiants(identificador),
CLAU ESTRANGERA(id_curs)Cursos de REFERÈNCIES(id_curs),
CLAU PRIMÀRIA(std_id, course_id, mark_type));
Inseriu alguns registres a estudiants, cursos i resultat taules. Els valors s'han d'inserir a les taules segons les restriccions establertes en el moment de la creació de la taula.
INSERIR EN ELS VALORS dels estudiants
( '1937463','Harper Lee','CSE'),
( '1937464','Garcia Marquez','CSE'),
( '1937465','Forster, E.M.','CSE'),
( '1937466','Ralph Ellison','CSE');
INSERTAR A VALORS DE CURSOS
( 'CSE-401','Programació orientada a objectes',3),
( 'CSE-403','Estructura de dades',2),
( 'CSE-407','Programació Unix',2);
INSERIR EN ELS VALORS DELS RESULTATS
( '1937463','CSE-401','Examen intern',15),
( '1937463','CSE-401','Examen parcial',20),
( '1937463','CSE-401','Final Exam',35),
( '1937464','CSE-403','Examen intern',17),
( '1937464','CSE-403','Examen parcial',15),
( '1937464','CSE-403','Final Exam',30),
( '1937465','CSE-401','Examen intern',18),
( '1937465','CSE-401','Examen parcial',23),
( '1937465','CSE-401','Final Exam',38),
( '1937466','CSE-407','Examen intern',20),
( '1937466','CSE-407','Examen parcial',22),
( '1937466','CSE-407','Final Exam',40);
Aquí, resultat La taula conté diversos mateixos valors per a std_id , tipus_marca i id_curs columnes de cada fila. A la següent part d’aquest tutorial es mostra com convertir aquestes files en columnes d’aquesta taula per mostrar les dades en un format més organitzat.
Gireu files a columnes mitjançant la sentència CASE:
Executeu la següent instrucció SELECT senzilla per mostrar tots els registres del fitxer resultat taula.
SELECCIONA*DE resultat;
El resultat mostra les quatre notes dels estudiants per a tres tipus d’exàmens de tres cursos. Així doncs, els valors de std_id , id_curs i tipus_marca es repeteixen diverses vegades per als diferents estudiants, cursos i tipus d'exàmens.
La sortida serà més llegible si la consulta SELECT es pot escriure de manera més eficient mitjançant la instrucció CASE. El següent SELECT amb la sentència CASE transformarà els valors repetitius de les files en els noms de columna i mostrarà el contingut de les taules en un format més comprensible per a l'usuari.
SELECT result.std_id, result.course_id,MÀX(CAS QUAN result.mark_type ='Examen intern'DESPRÉS result.marks END) 'Examen intern',
MÀX(CAS QUAN result.mark_type ='Examen parcial'DESPRÉS result.marks END) 'Examen parcial',
MÀX(CAS QUAN result.mark_type ='Final Exam'DESPRÉS result.marks END) 'Final Exam'
RESULTAT DE
GRUP PER resultat.std_id, result.course_id
ORDRE BY result.std_id, result.course_id ASC;
La següent sortida apareixerà després d'executar la sentència anterior, que és més llegible que la sortida anterior.
Gireu files a columnes amb CASE i SUM ():
Si voleu comptar el nombre total de cada curs de cada estudiant de la taula, heu d'utilitzar la funció agregada SUMA() agrupar per std_id i id_curs amb la declaració CASE. La següent consulta es crea modificant la consulta anterior amb la funció SUM () i la clàusula GROUP BY.
SELECT result.std_id, result.course_id,MÀX(CAS QUAN result.mark_type ='Examen intern'DESPRÉS result.marks END) 'Examen intern',
MÀX(CAS QUAN result.mark_type ='Examen parcial'DESPRÉS result.marks END) 'Examen parcial',
MÀX(CAS QUAN result.mark_type ='Final Exam'DESPRÉS result.marks END) 'Final Exam',
SUMA(result.marks) comtotal
RESULTAT DE
GRUP PER resultat.std_id, result.course_id
ORDRE BY result.std_id, result.course_id ASC;
La sortida mostra una nova columna anomenada total que mostra la suma de les notes de tots els tipus d’exàmens de cada curs obtinguts per cada estudiant en particular.
Gireu files a columnes de diverses taules:
Les dues consultes anteriors s'apliquen al fitxer resultat taula. Aquesta taula està relacionada amb les altres dues taules. Aquests són estudiants i cursos . Si voleu mostrar el nom de l'estudiant en lloc de l'identificador de l'estudiant i el nom del curs en lloc de l'identificador del curs, heu d'escriure la consulta SELECT mitjançant tres taules relacionades, estudiants , cursos i resultat . La següent consulta SELECT es crea afegint tres noms de taula després de la clàusula FORM i establint les condicions adequades a la clàusula WHERE per recuperar les dades de les tres taules i generar una sortida més adequada que les consultes SELECT anteriors.
SELECCIONAR estudiants.nomcom `Nom de l'estudiant`, courses.namecom `Nom del curs`,MÀX(CAS QUAN result.mark_type ='Examen intern'DESPRÉS result.marks END) 'CT',
MÀX(CAS QUAN result.mark_type ='Examen parcial'DESPRÉS result.marks END) 'Mitjà',
MÀX(CAS QUAN result.mark_type ='Final Exam'DESPRÉS result.marks END) 'Final',
SUMA(result.marks) comtotal
D’ALUMNES, cursos, resultat
WHERE result.std_id = students.id i result.course_id = cursos.course_id
GRUP PER resultat.std_id, result.course_id
ORDRE BY result.std_id, result.course_id ASC;
La sortida següent es generarà després d'executar la consulta anterior.
Conclusió:
En aquest article es mostra com es pot implementar la funcionalitat de la funció Pivot () sense el suport de la funció Pivot () a MySQL mitjançant algunes dades fictícies. Espero que els lectors puguin transformar qualsevol dada de fila en dades de columna mitjançant la consulta SELECT després de llegir aquest article.