Trong bài viết này, chúng tôi sẽ xem xét cách chuyển đổi hàng thành cột. Thường có một yêu cầu mà chúng tôi muốn lấy dữ liệu hàng được biểu thị dưới dạng cột và nhận thông tin cần thiết được liên kết với chúng
Mục lục
Giới thiệu
MySQL không cung cấp bất kỳ chức năng PIVOT nào để thiết lập chế độ xem này cho chúng tôi. Chúng ta có thể sử dụng các câu lệnh CASE cùng với các hàm tổng hợp để xem cách chuyển đổi hàng thành cột
Hãy để chúng tôi bắt đầu bằng cách thiết lập dữ liệu mẫu của chúng tôi sẽ được sử dụng trên. Chúng tôi sẽ tạo một bảng có tên là students_marks và chèn các hàng vào đó
CREATE TABLE students_marks [ id INT AUTO_INCREMENT, student_id INT, student_name VARCHAR[255], term VARCHAR[50], marks INT, student_subject VARCHAR[50], PRIMARY KEY [id] ]; INSERT INTO students_marks [student_id,student_name,term,marks,student_subject] VALUES[1,"Gustav","term1",50,"Physics"], [1,"Gustav","term2",50,"Physics"], [1,"Gustav","term3",54,"Physics"], [1,"Gustav","term1",76,"Chemistry"], [1,"Gustav","term2",50,"Chemistry"], [1,"Gustav","term3",34,"Chemistry"], [1,"Gustav","term1",96,"Botany"], [1,"Gustav","term2",90,"Botany"], [1,"Gustav","term3",74,"Botany"], [2,"Henric","term1",34,"Physics"], [2,"Henric","term2",45,"Physics"], [2,"Henric","term3",90,"Physics"], [2,"Henric","term1",94,"Chemistry"], [2,"Henric","term2",45,"Chemistry"], [2,"Henric","term3",94,"Chemistry"], [2,"Henric","term1",23,"Botany"], [2,"Henric","term2",45,"Botany"], [2,"Henric","term3",56,"Botany"], [3,"Rasmus","term1",40,"Physics"], [3,"Rasmus","term2",56,"Physics"], [3,"Rasmus","term3",23,"Physics"], [3,"Rasmus","term1",76,"Chemistry"], [3,"Rasmus","term2",56,"Chemistry"], [3,"Rasmus","term3",33,"Chemistry"], [3,"Rasmus","term1",89,"Botany"], [3,"Rasmus","term2",67,"Botany"], [3,"Rasmus","term3",90,"Botany"];
Hãy cho chúng tôi xem những gì đã được thêm vào bảng students_marks bằng cách thực hiện
quảng cáo
SELECT * FROM students_marks;
đầu ra. -
mysql. chuyển đổi hàng thành cột – sử dụng CASE
Yêu cầu là xem tổng điểm của mỗi học sinh cho tất cả các điều khoản cho mỗi môn học
Sẽ rõ ràng nếu chúng ta có thể thấy các cột trên mỗi tên_sinh viên. Quan sát truy vấn dưới đây
SELECT student_subject, SUM[CASE WHEN student_name = "Gustav" THEN marks ELSE 0 END] AS Gustav, SUM[CASE WHEN student_name = "Henric" THEN marks ELSE 0 END] AS Henric, SUM[CASE WHEN student_name = "Rasmus" THEN marks ELSE 0 END] AS Rasmus FROM students_marks GROUP BY student_subject;
đầu ra. -
Kết quả trong hình 2 cho thấy các cột đã được tạo tương ứng với mỗi tên_sinh viên. Ở đây, một câu lệnh CHỌN được sử dụng để hiển thị dữ liệu ở dạng mong muốn. Chúng tôi cũng đang sử dụng các câu lệnh CASE để kiểm tra các điều kiện dựa trên student_name và nhận tổng điểm trong một môn học cụ thể
Nếu chúng ta muốn đầu ra này thường xuyên, thì chúng ta có thể dễ dàng tạo chế độ xem cho cùng một. Xem các truy vấn và đầu ra bên dưới
CREATE OR REPLACE VIEW student_total_marks_per_subject AS [ SELECT student_subject, SUM[CASE WHEN student_name = "Gustav" THEN marks ELSE 0 END] AS Gustav, SUM[CASE WHEN student_name = "Henric" THEN marks ELSE 0 END] AS Henric, SUM[CASE WHEN student_name = "Rasmus" THEN marks ELSE 0 END] AS Rasmus FROM students_marks GROUP BY student_subject ]; SELECT * FROM student_total_marks_per_subject;
đầu ra. -
Đầu ra giống như trong hình 2
mysql. chuyển đổi hàng thành cột – sử dụng IF
Chúng ta cũng có thể sử dụng hàm IF[] cho cùng một yêu cầu
Lấy tổng điểm của mỗi học sinh cho tất cả các điều khoản cho mỗi môn học. Quan sát truy vấn.
SELECT student_subject, SUM[IF[student_name = 'Gustav', marks, 0]] AS Gustav, SUM[IF[student_name = 'Henric', marks, 0]] AS Henric, SUM[IF[student_name = 'Rasmus', marks, 0]] AS Rasmus FROM students_marks GROUP BY student_subject;
đầu ra. -
mysql. chuyển đổi hàng thành cột để có dữ liệu mở rộng – sử dụng câu lệnh đã chuẩn bị
Nhận tổng điểm cho mỗi học sinh cho tất cả các điều khoản cho mỗi môn học
Sẽ không thực tế khi sử dụng cùng một truy vấn nếu có rất nhiều hàng trên bảng của bạn. Trong trường hợp này, chúng ta nên để MySQL xây dựng truy vấn cho chúng ta bằng cách sử dụng các câu lệnh đã chuẩn bị sẵn. Quan sát truy vấn dưới đây
SET @sql = NULL; SELECT GROUP_CONCAT[DISTINCT CONCAT[ 'ifnull[SUM[case when student_name = ''', student_name, ''' then marks end],0] AS `', student_name, '`' ] ] INTO @sql FROM students_marks; SET @sql = CONCAT['SELECT student_subject, ', @sql, ' FROM students_marks GROUP BY student_subject']; PREPARE stmt1 FROM @sql; EXECUTE stmt1;
đầu ra. -
Ở đây chúng tôi đang sử dụng hàm GROUP_CONCAT[] để tạo truy vấn bằng máy chủ MySQL một cách linh hoạt. Truy vấn giống như đã thảo luận trước đó. Chỉ cần tên_sinh_viên đó được kéo tự động một cách nhanh chóng