Hàng Mysql đến cột động

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

Làm cách nào để sử dụng chức năng PIVOT trong MySQL?

Phép biến đổi nào chuyển hàng thành cột?

Làm cách nào để sử dụng liên kết tất cả trong MySQL?

Làm cách nào để sử dụng Row_number trong MySQL?