Таблица базы данных может хранить различные типы данных, и иногда нам нужно преобразовать данные на уровне строк в данные на уровне столбцов. Эту проблему можно решить с помощью функции PIVOT(). Эта функция используется для поворота строк таблицы в значения столбцов. Но эта функция поддерживается очень немногими серверами баз данных, такими как Oracle или SQL Server. Если вы хотите выполнить ту же задачу в таблице базы данных MySQL, вам необходимо написать запрос SELECT, используя оператор CASE для поворота строк в столбцы. В статье показано, как выполнять задачу функции PIVOT() в связанных таблицах базы данных MySQL.
Предпосылка:
Вам необходимо создать базу данных и несколько связанных таблиц, в которых строки одной таблицы будут преобразованы в столбцы, такие как функция PIVOT(). Выполните следующие операторы SQL, чтобы создать базу данных с именем unidb и создать три таблицы с именами ‘students’, ‘courses’ и ‘result’. студенты и таблицы результатов будут связаны отношением «один ко многим», а таблицы курсов и результатов будут связаны здесь отношением «один ко многим». Запрос CREATE таблицы результатов содержит два ограничения внешнего ключа для полей, std_id и course_id.
CREATE DATABASE unidb; USE unidb; CREATE TABLE students ( id INT PRIMARY KEY, name varchar(50) NOT NULL, department VARCHAR(15) NOT NULL); CREATE TABLE courses ( course_id VARCHAR(20) PRIMARY KEY, name varchar(50) NOT NULL, credit SMALLINT NOT NULL); CREATE TABLE result( std_id INT NOT NULL, course_id VARCHAR(20) NOT NULL, mark_type VARCHAR(20) NOT NULL, marks SMALLINT NOT NULL, FOREIGN KEY (std_id) REFERENCES students(id), FOREIGN KEY (course_id) REFERENCES courses(course_id), PRIMARY KEY (std_id, course_id, mark_type));
Вставьте некоторые записи в студентов, курсы и таблицы результатов. Значения должны быть вставлены в таблицы в соответствии с ограничениями, установленными во время создания таблицы.
INSERT INTO students VALUES ( '1937463', 'AndreyEx', 'CSE'), ( '1937464', 'Destroyer', 'CSE'), ( '1937465', 'Alex', 'CSE'), INSERT INTO courses VALUES ( 'CSE-401', 'Объектно-ориентированное программирование', 3), ( 'CSE-403', 'Структура данных', 2), ( 'CSE-407', 'Программирование Unix', 2); INSERT INTO result VALUES ( '1937463', 'CSE-401','Внутренний экзамен' ,15), ( '1937463', 'CSE-401','Промежуточный экзамен' ,20), ( '1937463', 'CSE-401','Итоговый экзамен', 35),
Здесь таблица результатов содержит несколько одинаковых значений для столбцов std_id , mark_type и course_id в каждой строке. Как преобразовать эти строки в столбцы этой таблицы для отображения данных в более организованном формате, показано в следующей части этой статьи.
Поверните строки в столбцы, используя оператор CASE:
Выполните следующую простую инструкцию с оператором SELECT, чтобы отобразить все записи таблицы результатов.
SELECT * FROM result;
На выходе показаны оценки четырех студентов за три типа экзаменов по трем курсам. Таким образом, значения std_id , course_id и mark_type повторяются несколько раз для разных студентов, курсов и типов экзаменов.
Вывод будет более читабельным, если запрос SELECT можно будет написать более эффективно с помощью оператора CASE. Следующий оператор SELECT с оператором CASE преобразует повторяющиеся значения строк в имена столбцов и отобразит содержимое таблиц в более понятном для пользователя формате.
SELECT result.std_id, result.course_id, MAX(CASE WHEN result.mark_type = "Внутренний экзамен" THEN result.marks END) "Внутренний экзамен", MAX(CASE WHEN result.mark_type = "Промежуточный экзамен" THEN result.marks END) "Промежуточный экзамен", MAX(CASE WHEN result.mark_type = "Итоговый экзамен" THEN result.marks END) "Итоговый экзамен" FROM result GROUP BY result.std_id, result.course_id ORDER BY result.std_id, result.course_id ASC;
Поверните строки в столбцы, используя CASE и SUM():
Если вы хотите подсчитать общее количество каждого курса каждого студента из таблицы, вы должны использовать агрегатную функцию SUM() group by std_id и course_id с оператором CASE. Следующий запрос создается путем изменения предыдущего запроса с помощью функции SUM() и предложения GROUP BY.
SELECT result.std_id,result.course_id, MAX(CASE WHEN result.mark_type = "Внутренний экзамен" THEN result.marks END) "Внутренний экзамен", MAX(CASE WHEN result.mark_type = "Промежуточный экзамен" THEN result.marks END) "Промежуточный экзамен", MAX(CASE WHEN result.mark_type = "Итоговый экзамен" THEN result.marks END) "Итоговый экзамен", SUM( result.marks) as Total FROM result GROUP BY result.std_id, result.course_id ORDER BY result.std_id, result.course_id ASC;
В выходных данных отображается новый столбец «Total», в котором отображается сумма оценок по всем типам экзаменов каждого курса, полученных каждым конкретным студентом.
Поверните строки в столбцы в нескольких таблицах:
К таблице результатов применяются два предыдущих запроса.. Эта таблица связана с двумя другими таблицами. Это студенты и курсы. Если вы хотите отображать имя студента вместо идентификатора студента и имени курса вместо идентификатора курса, вам нужно написать запрос SELECT, используя три связанных таблицы, студентов , курсы и результат. Следующий запрос SELECT создается путем добавления трех имен таблиц после предложения FORM и задания соответствующих условий в предложении WHERE для извлечения данных из трех таблиц и создания более подходящего вывода, чем предыдущие запросы SELECT.
SELECT students.name as `Student Name` , courses.name as `Course Name`, MAX(CASE WHEN result.mark_type = "Внутренний экзамен" THEN result.marks END) "Вн", MAX(CASE WHEN result.mark_type = "Промежуточный экзамен" THEN result.marks END) "Пр", MAX(CASE WHEN result.mark_type = "Итоговый экзамен" THEN result.marks END) "Финал", SUM( result.marks) as Total FROM students, courses, result WHERE result.std_id = students.id and 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 после прочтения этой статьи.
