Предпоставка:
Трябва да създадете база данни и някои свързани таблици, където редовете от една таблица ще бъдат преобразувани в колони като функцията PIVOT (). Изпълнете следните SQL изрази, за да създадете база данни с име „ unidb “И създайте три таблици с име„ студенти “,„ курсове ' и ' резултат '. студенти и резултат таблиците ще бъдат свързани чрез връзка един към много и курсове и резултати тук таблиците ще бъдат свързани чрез връзка един към много. CREATE изявление на резултат таблицата съдържа две ограничения на външния ключ за полетата, std_id , и course_id .
CREATE DATABASE unidb;
ИЗПОЛЗВАЙТЕ unidb;
СЪЗДАВАЙТЕ ТАБЛИЦА студенти(
документ за самоличностINT ОСНОВЕН КЛЮЧ,
име varchar(петдесет)НЕ НУЛ,
отдел ВЪРЧАР(петнадесет)НЕ НУЛ);
CREATE TABLE курсове(
course_id VARCHAR(двайсет)ОСНОВЕН КЛЮЧ,
име varchar(петдесет)НЕ НУЛ,
кредит SMALLINT NOT NULL);
CREATE TABLE резултат(
std_id INT NOT NULL,
course_id VARCHAR(двайсет)НЕ НУЛ,
mark_type VARCHAR(двайсет)НЕ НУЛ,
маркира SMALLINT NOT NULL,
ЧУЖДЕН КЛЮЧ(std_id)ЛИТЕРАТУРА студенти(документ за самоличност),
ЧУЖДЕН КЛЮЧ(course_id)ЛИТЕРАТУРА курсове(course_id),
ОСНОВЕН КЛЮЧ(std_id, course_id, mark_type));
Вмъкнете някои записи в студенти, курсове и резултат таблици. Стойностите трябва да бъдат вмъкнати в таблиците въз основа на ограниченията, зададени по време на създаването на таблицата.
ВСТАВЕТЕ В ЦЕННОСТИТЕ на учениците
( '1937463','Харпър Лий',„CSE“),
( '1937464','Гарсия Маркес',„CSE“),
( '1937465',„Форстър, Е.М.“,„CSE“),
( '1937466','Ралф Елисън',„CSE“);
ВМЕСТВАНЕ В ЦЕННОСТИТЕ на курсовете
( „CSE-401“,„Обектно ориентирано програмиране“,3),
( „CSE-403“,'Структура на данни',2),
( „CSE-407“,'Unix програмиране',2);
ВМЕСТВАНЕ В ЦЕННОСТИТЕ на резултата
( '1937463',„CSE-401“,„Вътрешен изпит“,петнадесет),
( '1937463',„CSE-401“,'Междинен изпит',двайсет),
( '1937463',„CSE-401“,'Последен изпит',35),
( '1937464',„CSE-403“,„Вътрешен изпит“,17),
( '1937464',„CSE-403“,'Междинен изпит',петнадесет),
( '1937464',„CSE-403“,'Последен изпит',30),
( '1937465',„CSE-401“,„Вътрешен изпит“,18),
( '1937465',„CSE-401“,'Междинен изпит',2. 3),
( '1937465',„CSE-401“,'Последен изпит',38),
( '1937466',„CSE-407“,„Вътрешен изпит“,двайсет),
( '1937466',„CSE-407“,'Междинен изпит',22),
( '1937466',„CSE-407“,'Последен изпит',40);
Тук, резултат таблицата съдържа множество еднакви стойности за std_id , mark_type и course_id колони във всеки ред. Как да конвертирате тези редове в колони на тази таблица за показване на данните в по -организиран формат е показано в следващата част на този урок.
Завъртете редовете до колони, използвайки израза CASE:
Изпълнете следния прост оператор SELECT, за да покажете всички записи на резултат маса.
SELECT*ОТ резултат;
Резултатът показва четирите оценки на студентите за три вида изпити от три курса. Така че стойностите на std_id , course_id и mark_type се повтарят многократно за различните студенти, курсове и типове изпити.
Изходът ще бъде по -четим, ако заявката SELECT може да бъде написана по -ефективно с помощта на израза CASE. Следният SELECT с израза CASE ще трансформира повтарящите се стойности на редовете в имената на колоните и ще покаже съдържанието на таблиците в по -разбираем за потребителя формат.
SELECT result.std_id, result.course_id,МАКС(СЛУЧАЙ КОГА result.mark_type =„Вътрешен изпит“ТОГАВА резултатите.бележки КРАЙ) „Вътрешен изпит“,
МАКС(СЛУЧАЙ КОГА result.mark_type ='Междинен изпит'ТОГАВА резултатите.бележки КРАЙ) 'Междинен изпит',
МАКС(СЛУЧАЙ КОГА result.mark_type ='Последен изпит'ТОГАВА резултатите.бележки КРАЙ) 'Последен изпит'
ОТ резултата
GROUP BY result.std_id, result.course_id
ORDER BY result.std_id, result.course_id ASC;
Следният изход ще се появи след изпълнение на горния израз, който е по -четим от предишния изход.
Завъртете редовете до колони, използвайки CASE и SUM ():
Ако искате да преброите общия брой на всеки курс на всеки студент от таблицата, тогава трябва да използвате агрегираната функция SUM () Групирай по std_id и course_id с изявлението CASE. Следващата заявка е създадена чрез промяна на предишната заявка с функция SUM () и клауза GROUP BY.
SELECT result.std_id, result.course_id,МАКС(СЛУЧАЙ КОГА result.mark_type =„Вътрешен изпит“ТОГАВА резултатите.бележки КРАЙ) „Вътрешен изпит“,
МАКС(СЛУЧАЙ КОГА result.mark_type ='Междинен изпит'ТОГАВА резултатите.бележки КРАЙ) 'Междинен изпит',
МАКС(СЛУЧАЙ КОГА result.mark_type ='Последен изпит'ТОГАВА резултатите.бележки КРАЙ) 'Последен изпит',
SUM(резултат.марки) катоОбща сума
ОТ резултата
GROUP BY result.std_id, result.course_id
ORDER BY result.std_id, result.course_id ASC;
Изходът показва нова колона с име Обща сума което показва сумата от оценките за всички видове изпити за всеки курс, получени от всеки конкретен студент.
Завъртете редовете до колони в множество таблици:
Предишните две заявки се прилагат към резултат маса. Тази таблица е свързана с другите две таблици. Това са студенти и курсове . Ако искате да покажете името на студента вместо идентификатора на студента и името на курса вместо идентификатора на курса, тогава трябва да напишете заявката SELECT, като използвате три свързани таблици, студенти , курсове и резултат . Следващата заявка SELECT се създава чрез добавяне на три имена на таблици след клаузата FORM и задаване на подходящи условия в клаузата WHERE за извличане на данните от трите таблици и генериране на по -подходящ изход от предишните заявки SELECT.
SELECT students.nameкато „Име на студент„, courses.nameкато „Име на курса„,МАКС(СЛУЧАЙ КОГА result.mark_type =„Вътрешен изпит“ТОГАВА резултатите.бележки КРАЙ) 'CT',
МАКС(СЛУЧАЙ КОГА result.mark_type ='Междинен изпит'ТОГАВА резултатите.бележки КРАЙ) 'Средно',
МАКС(СЛУЧАЙ КОГА result.mark_type ='Последен изпит'ТОГАВА резултатите.бележки КРАЙ) 'Финал',
SUM(резултат.марки) катоОбща сума
ОТ ученици, курсове, резултат
КЪДЕ result.std_id = students.id и result.course_id = courses.course_id
GROUP BY result.std_id, result.course_id
ORDER BY result.std_id, result.course_id ASC;
Следният изход ще се генерира след изпълнение на горната заявка.
Заключение:
Как можете да внедрите функционалността на функцията Pivot () без поддръжката на функцията Pivot () в MySQL е показано в тази статия, като използвате някои фиктивни данни. Надявам се, че читателите ще могат да трансформират всички данни на ниво ред в данни на ниво колона, като използват заявката SELECT, след като прочетат тази статия.