Предпосылка:
Вам необходимо создать базу данных и несколько связанных таблиц, в которых строки одной таблицы будут преобразованы в столбцы, такие как функция PIVOT (). Выполните следующие операторы SQL, чтобы создать базу данных с именем ‘ unidb ’И создайте три таблицы с именем‘ студенты ’,‘ курсы ' а также ' результат '. студенты а также результат таблицы будут связаны отношениями 'один ко многим' и курсы а также полученные результаты здесь таблицы будут связаны отношениями 'один ко многим'. CREATE оператор результат таблица содержит два ограничения внешнего ключа для полей, std_id , а также course_id .
СОЗДАТЬ БАЗУ ДАННЫХ unidb;
ИСПОЛЬЗУЙТЕ unidb;
СОЗДАТЬ ТАБЛИЦУ студентов(
я быПЕРВИЧНЫЙ КЛЮЧ INT,
имя varchar(пятьдесят)НЕНУЛЕВОЙ,
отдел ВАРЧАР(пятнадцать)НЕНУЛЕВОЙ);
СОЗДАТЬ ТАБЛИЦУ курсы(
course_id VARCHAR(двадцать)ОСНОВНОЙ КЛЮЧ,
имя varchar(пятьдесят)НЕНУЛЕВОЙ,
кредит SMALLINT NOT NULL);
СОЗДАТЬ ТАБЛИЦУ результат(
std_id INT НЕ 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);
INSERT INTO результат VALUES
( '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, чтобы отобразить все записи результат стол.
ВЫБРАТЬ*ОТ результата;
В выходных данных показаны оценки четырех студентов за три типа экзаменов по трем курсам. Итак, значения std_id , course_id а также mark_type повторяются несколько раз для разных студентов, курсов и типов экзаменов.
Вывод будет более читабельным, если запрос SELECT можно будет написать более эффективно с помощью оператора CASE. Следующий оператор SELECT с оператором CASE преобразует повторяющиеся значения строк в имена столбцов и отобразит содержимое таблиц в более понятном для пользователя формате.
ВЫБЕРИТЕ result.std_id, result.course_id,МАКСИМУМ(СЛУЧАЙ, КОГДА result.mark_type =«Внутренний экзамен»ТО результат. Метки КОНЕЦ) «Внутренний экзамен»,
МАКСИМУМ(СЛУЧАЙ, КОГДА result.mark_type ='Промежуточный экзамен'ТО результат. Метки КОНЕЦ) 'Промежуточный экзамен',
МАКСИМУМ(СЛУЧАЙ, КОГДА result.mark_type =«Заключительный экзамен»ТО результат. Метки КОНЕЦ) «Заключительный экзамен»
ОТ результата
ГРУППА ПО result.std_id, result.course_id
ЗАКАЗАТЬ ПО result.std_id, result.course_id ASC;
Следующий вывод появится после выполнения вышеуказанного оператора, который более читабелен, чем предыдущий вывод.
Поверните строки в столбцы, используя CASE и SUM ():
Если вы хотите подсчитать общее количество каждого курса каждого студента из таблицы, вам необходимо использовать агрегатную функцию СУММ () группа по std_id а также course_id с помощью оператора CASE. Следующий запрос создается путем изменения предыдущего запроса с помощью функции SUM () и предложения GROUP BY.
ВЫБЕРИТЕ result.std_id, result.course_id,МАКСИМУМ(СЛУЧАЙ, КОГДА result.mark_type =«Внутренний экзамен»ТО результат. Метки КОНЕЦ) «Внутренний экзамен»,
МАКСИМУМ(СЛУЧАЙ, КОГДА result.mark_type ='Промежуточный экзамен'ТО результат. Метки КОНЕЦ) 'Промежуточный экзамен',
МАКСИМУМ(СЛУЧАЙ, КОГДА result.mark_type =«Заключительный экзамен»ТО результат. Метки КОНЕЦ) «Заключительный экзамен»,
СУММ(result.marks) в качествеОбщий
ОТ результата
ГРУППА ПО result.std_id, result.course_id
ЗАКАЗАТЬ ПО result.std_id, result.course_id ASC;
В выходных данных отображается новый столбец с именем Общий который отображает сумму оценок по всем типам экзаменов по каждому курсу, полученных каждым конкретным студентом.
Поверните строки в столбцы в нескольких таблицах:
Предыдущие два запроса применяются к результат стол. Эта таблица связана с двумя другими таблицами. Эти студенты а также курсы . Если вы хотите отображать имя студента вместо идентификатора студента и имени курса вместо идентификатора курса, вам нужно написать запрос SELECT, используя три связанные таблицы, студенты , курсы а также результат . Следующий запрос SELECT создается путем добавления трех имен таблиц после предложения FORM и установки соответствующих условий в предложении WHERE для извлечения данных из трех таблиц и генерации более подходящего вывода, чем предыдущие запросы SELECT.
ВЫБЕРИТЕ student.nameв качестве ``Имя ученика``, course.nameв качестве ``Название курса``,МАКСИМУМ(СЛУЧАЙ, КОГДА result.mark_type =«Внутренний экзамен»ТО результат. Метки КОНЕЦ) 'CT',
МАКСИМУМ(СЛУЧАЙ, КОГДА result.mark_type ='Промежуточный экзамен'ТО результат. Метки КОНЕЦ) 'Середина',
МАКСИМУМ(СЛУЧАЙ, КОГДА result.mark_type =«Заключительный экзамен»ТО результат. Метки КОНЕЦ) 'Финал',
СУММ(result.marks) в качествеОбщий
ОТ студентов, курсы, результат
ГДЕ result.std_id = student.id и result.course_id = курсы.course_id
ГРУППА ПО result.std_id, result.course_id
ЗАКАЗАТЬ ПО result.std_id, result.course_id ASC;
Следующий вывод будет сгенерирован после выполнения вышеуказанного запроса.
Заключение:
Как можно реализовать функциональность функции Pivot () без поддержки функции Pivot () в MySQL, показано в этой статье с использованием некоторых фиктивных данных. Я надеюсь, что читатели смогут преобразовать любые данные на уровне строк в данные на уровне столбцов с помощью запроса SELECT после прочтения этой статьи.