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

Bảng tổng hợp hữu ích cho việc phân tích dữ liệu, cho phép bạn hiển thị giá trị hàng dưới dạng cột để dễ dàng nhận thông tin chi tiết. Tuy nhiên, không có chức năng tạo bảng tổng hợp trong MySQL. Vì vậy, bạn cần viết truy vấn SQL để tạo bảng tổng hợp trong MySQL. May mắn thay, có nhiều cách để tạo Pivot Table trong MySQL. Hãy xem xét từng người trong số họ một cách nhanh chóng

Cách tạo Bảng Pivot trong MySQL

Dưới đây là các bước để tạo bảng tổng hợp trong MySQL. Giả sử bạn có bảng sau

CREATE TABLE exams (
id int(11) NOT NULL auto_increment,
name varchar(15),
exam int,
score int,
PRIMARY KEY (id)
);

insert into exams (name,exam,score) values ('Bob',1,70);
insert into exams (name,exam,score) values ('Bob',2,77);
insert into exams (name,exam,score) values ('Bob',3,71);
insert into exams (name,exam,score) values ('Bob',4,70);

insert into exams (name,exam,score) values ('Sue',1,89);
insert into exams (name,exam,score) values ('Sue',2,87);
insert into exams (name,exam,score) values ('Sue',3,88);
insert into exams (name,exam,score) values ('Sue',4,89);

mysql> select * from exams;
+------+------+------+-------+
| id   | name | exam | score |
+------+------+------+-------+
|   1  |  Bob |   1  |   70  |
|   2  |  Bob |   2  |   77  |
|   3  |  Bob |   3  |   71  |
|   4  |  Bob |   4  |   70  |
|   5  |  Sue |   1  |   89  |
|   6  |  Sue |   2  |   87  |
|   7  |  Sue |   3  |   88  |
|   8  |  Sue |   4  |   89  |
+------+------+------+-------+

Giả sử bạn muốn xoay bảng theo cột bài kiểm tra để tạo 1 hàng cho mỗi học sinh và 1 cột cho mỗi bài kiểm tra, như minh họa bên dưới

+--------+-----------+-----------+-----------+------------+
|   name |    exam1  |    exam2  |    exam3  |    exam4   |
+--------+-----------+-----------+-----------+------------+
|   Bob  |     70    |     77    |     71    |     70     |
|   Sue  |     89    |     87    |     88    |     89     |
+--------+-----------+-----------+-----------+------------+

Bạn có thể tạo bảng tổng hợp trong MySQL bằng câu lệnh IF hoặc CASE

tiền thưởng đọc. Cách tính trung bình trong MySQL

Tạo Pivot Table trong MySQL bằng câu lệnh IF

Đây là truy vấn SQL chuyển hàng thành cột bằng cách sử dụng câu lệnh IF

SELECT name,
sum(IF(exam=1, score, NULL)) AS exam1,
sum(IF(exam=2, score, NULL)) AS exam2,
sum(IF(exam=3, score, NULL)) AS exam3,
sum(IF(exam=4, score, NULL)) AS exam4
FROM exams
GROUP BY name;
+--------+-----------+-----------+-----------+------------+
|   name |    exam1  |    exam2  |    exam3  |    exam4   |
+--------+-----------+-----------+-----------+------------+
|   Bob  |     70    |     77    |     71    |     70     |
|   Sue  |     89    |     87    |     88    |     89     |
+--------+-----------+-----------+-----------+------------+

Trong truy vấn trên, bạn cần nhóm theo cột tên vì bạn muốn 1 hàng cho mỗi sinh viên. Ngoài ra bạn cần cung cấp 1 điều kiện cho mỗi cột cần tạo, tức là mỗi kỳ thi 1 điều kiện

Tạo Pivot Table trong MySQL bằng câu lệnh CASE

Đây là truy vấn SQL để chuyển đổi hàng thành cột bằng câu lệnh CASE

SELECT name,
sum(CASE WHEN exam=1 THEN score ELSE NULL END) AS exam1,
sum(CASE WHEN exam=2 THEN score ELSE NULL END) AS exam2,
sum(CASE WHEN exam=3 THEN score ELSE NULL END) AS exam3,
sum(CASE WHEN exam=4 THEN score ELSE NULL END) AS exam4
FROM exams
GROUP BY name;
+--------+-----------+-----------+-----------+------------+
|   name |    exam1  |    exam2  |    exam3  |    exam4   |
+--------+-----------+-----------+-----------+------------+
|   Bob  |     70    |     77    |     71    |     70     |
|   Sue  |     89    |     87    |     88    |     89     |
+--------+-----------+-----------+-----------+------------+

Truy vấn trên hoạt động tương tự như truy vấn sử dụng điều kiện IF ở trên. Bạn cần nhóm theo cột mà bạn muốn xoay vòng dữ liệu của mình, tức là tên. Ngoài ra, bạn cần xác định 1 câu lệnh CASE cho mỗi số bài kiểm tra vì bạn muốn tạo các cột riêng cho từng bài kiểm tra

Bạn cũng có thể kết hợp điểm bài kiểm tra trong bảng tổng hợp của mình. Ví dụ: nếu bạn muốn cộng điểm của kỳ thi 1 và kỳ thi 2 và hiển thị chúng trong cùng một cột, bạn có thể sử dụng truy vấn sau

SELECT name,
sum(CASE WHEN exam=1 or exam=2 THEN score ELSE NULL END) AS exam12,
sum(CASE WHEN exam=3 THEN score ELSE NULL END) AS exam3,
sum(CASE WHEN exam=4 THEN score ELSE NULL END) AS exam4
FROM exams
GROUP BY name;
+--------+------------+-----------+-----------+------------+
|   name |    exam12  |    exam2  |    exam3  |    exam4   |
+--------+------------+-----------+-----------+------------+
|   Bob  |     147    |     77    |     71    |     70     |
|   Sue  |     176    |     87    |     88    |     89     |
+--------+------------+-----------+-----------+------------+

Trong bảng trên, điểm của bài kiểm tra 1 và bài kiểm tra 2 đã được thêm vào và hiển thị trong một cột duy nhất bài kiểm tra12, chỉ bằng cách sửa đổi điều kiện của câu lệnh CASE thứ nhất và xóa câu lệnh CASE thứ 2

Bạn có thể tùy chỉnh các truy vấn trên theo yêu cầu của mình để tạo bảng tổng hợp trong MySQL. Bạn cũng có thể sử dụng một công cụ báo cáo để vẽ kết quả trong một bảng. Đây là một ví dụ về bảng được tạo bằng Ubiq

Bạn có biết rằng bạn có thể tạo bảng tổng hợp trong Ubiq chỉ bằng cách kéo và thả không?

Nếu bạn muốn tạo bảng tổng hợp, biểu đồ và bảng điều khiển từ cơ sở dữ liệu MySQL, bạn có thể thử Ubiq. Chúng tôi cung cấp bản dùng thử miễn phí 14 ngày

Làm cách nào để xoay vòng trên MySQL?

Có thể sử dụng trục trong MySQL không?

Làm cách nào để xoay hàng thành cột trong MySQL?

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