Điều kiện tiên quyết:
Bạn phải tạo một cơ sở dữ liệu và một số bảng liên quan trong đó các hàng của một bảng sẽ được chuyển đổi thành các cột như hàm PIVOT (). Chạy các câu lệnh SQL sau để tạo cơ sở dữ liệu có tên ‘ unidb 'Và tạo ba bảng có tên là' sinh viên ',' các khóa học ' và ' kết quả '. sinh viên và kết quả các bảng sẽ được liên kết theo mối quan hệ một-nhiều và các khóa học và kết quả các bảng sẽ được liên kết theo quan hệ một-nhiều ở đây. Tuyên bố CREATE của kết quả bảng chứa hai ràng buộc khóa ngoại cho các trường, std_id , và Mã khóa học .
TẠO CƠ SỞ DỮ LIỆU unidb;
SỬ DỤNG unidb;
TẠO BẢNG sinh viên(
TôiINT PRIMARY KEY,
tên varchar(năm mươi)CÓ GIÁ TRỊ,
bộ phận VARCHAR(mười lăm)CÓ GIÁ TRỊ);
TẠO BẢNG khóa học(
course_id VARCHAR(hai mươi)KHÓA CHÍNH,
tên varchar(năm mươi)CÓ GIÁ TRỊ,
tín dụng SMALLINT NOT NULL);
KẾT QUẢ TẠO BẢNG(
std_id INT NOT NULL,
course_id VARCHAR(hai mươi)CÓ GIÁ TRỊ,
mark_type VARCHAR(hai mươi)CÓ GIÁ TRỊ,
đánh dấu SMALLINT NOT NULL,
TỪ KHÓA NGOẠI TỆ(std_id)TÀI LIỆU THAM KHẢO sinh viên(Tôi),
TỪ KHÓA NGOẠI TỆ(Mã khóa học)TÀI LIỆU THAM KHẢO các khóa học(Mã khóa học),
KHÓA CHÍNH(std_id, course_id, mark_type));
Chèn một số bản ghi vào sinh viên, khóa học và kết quả những cái bàn. Các giá trị phải được chèn vào các bảng dựa trên các hạn chế được đặt tại thời điểm tạo bảng.
CHÈN VÀO GIÁ TRỊ của sinh viên
( '1937463','Harper Lee','CSE'),
( '1937464','Garcia Marquez','CSE'),
( '1937465','Forster, E.M.','CSE'),
( '1937466','Ralph Ellison','CSE');
CHÈN VÀO GIÁ TRỊ của các khóa học
( 'CSE-401','Lập trình hướng đối tượng',3),
( 'CSE-403','Cấu trúc dữ liệu',2),
( 'CSE-407','Lập trình Unix',2);
CHÈN VÀO GIÁ TRỊ kết quả
( '1937463','CSE-401','Kiểm tra nội bộ',mười lăm),
( '1937463','CSE-401','Kỳ thi giữa kỳ',hai mươi),
( '1937463','CSE-401','Bài kiểm tra cuối cùng',35),
( '1937464','CSE-403','Kiểm tra nội bộ',17),
( '1937464','CSE-403','Kỳ thi giữa kỳ',mười lăm),
( '1937464','CSE-403','Bài kiểm tra cuối cùng',30),
( '1937465','CSE-401','Kiểm tra nội bộ',18),
( '1937465','CSE-401','Kỳ thi giữa kỳ',2. 3),
( '1937465','CSE-401','Bài kiểm tra cuối cùng',38),
( '1937466','CSE-407','Kiểm tra nội bộ',hai mươi),
( '1937466','CSE-407','Kỳ thi giữa kỳ',22),
( '1937466','CSE-407','Bài kiểm tra cuối cùng',40);
Ở đây, kết quả bảng chứa nhiều giá trị giống nhau cho std_id , mark_type và Mã khóa học cột trong mỗi hàng. Cách chuyển đổi các hàng này thành các cột của bảng này để hiển thị dữ liệu ở định dạng có tổ chức hơn được trình bày trong phần tiếp theo của hướng dẫn này.
Xoay hàng thành cột bằng câu lệnh CASE:
Chạy câu lệnh SELECT đơn giản sau để hiển thị tất cả các bản ghi của kết quả bàn.
LỰA CHỌN*Kết quả từ;
Kết quả đầu ra hiển thị bốn điểm của học sinh cho ba loại bài kiểm tra của ba khóa học. Vì vậy, các giá trị của std_id , Mã khóa học và mark_type được lặp lại nhiều lần cho các sinh viên, khóa học và loại bài thi khác nhau.
Đầu ra sẽ dễ đọc hơn nếu truy vấn SELECT có thể được viết hiệu quả hơn bằng cách sử dụng câu lệnh CASE. Câu lệnh SELECT sau đây với câu lệnh CASE sẽ biến đổi các giá trị lặp lại của các hàng thành tên cột và hiển thị nội dung của các bảng ở định dạng dễ hiểu hơn cho người dùng.
CHỌN result.std_id, result.course_id,TỐI ĐA(TRƯỜNG HỢP KHI result.mark_type ='Kiểm tra nội bộ'SAU ĐÓ result.marks END) 'Kiểm tra nội bộ',
TỐI ĐA(TRƯỜNG HỢP KHI result.mark_type ='Kỳ thi giữa kỳ'SAU ĐÓ result.marks END) 'Kỳ thi giữa kỳ',
TỐI ĐA(TRƯỜNG HỢP KHI result.mark_type ='Bài kiểm tra cuối cùng'SAU ĐÓ result.marks END) 'Bài kiểm tra cuối cùng'
TỪ kết quả
GROUP BY result.std_id, result.course_id
ĐẶT HÀNG THEO result.std_id, result.course_id ASC;
Đầu ra sau sẽ xuất hiện sau khi chạy câu lệnh trên, dễ đọc hơn đầu ra trước đó.
Xoay hàng thành cột bằng cách sử dụng CASE và SUM ():
Nếu bạn muốn đếm tổng số mỗi khóa học của mọi sinh viên từ bảng thì bạn phải sử dụng hàm tổng hợp TỔNG() nhóm bởi std_id và Mã khóa học với câu lệnh CASE. Truy vấn sau được tạo bằng cách sửa đổi truy vấn trước với hàm SUM () và mệnh đề GROUP BY.
CHỌN result.std_id, result.course_id,TỐI ĐA(TRƯỜNG HỢP KHI result.mark_type ='Kiểm tra nội bộ'SAU ĐÓ result.marks END) 'Kiểm tra nội bộ',
TỐI ĐA(TRƯỜNG HỢP KHI result.mark_type ='Kỳ thi giữa kỳ'SAU ĐÓ result.marks END) 'Kỳ thi giữa kỳ',
TỐI ĐA(TRƯỜNG HỢP KHI result.mark_type ='Bài kiểm tra cuối cùng'SAU ĐÓ result.marks END) 'Bài kiểm tra cuối cùng',
TỔNG(result.marks) nhưToàn bộ
TỪ kết quả
GROUP BY result.std_id, result.course_id
ĐẶT HÀNG THEO result.std_id, result.course_id ASC;
Kết quả hiển thị một cột mới có tên Toàn bộ đó là hiển thị tổng điểm của tất cả các loại bài kiểm tra của mỗi khóa học mà mỗi học sinh cụ thể đạt được.
Xoay hàng thành cột trong nhiều bảng:
Hai truy vấn trước được áp dụng cho kết quả bàn. Bảng này có liên quan đến hai bảng kia. đó là sinh viên và các khóa học . Nếu bạn muốn hiển thị tên sinh viên thay vì id sinh viên và tên khóa học thay vì id khóa học thì bạn phải viết truy vấn CHỌN bằng cách sử dụng ba bảng có liên quan, sinh viên , các khóa học và kết quả . Truy vấn SELECT sau đây được tạo bằng cách thêm ba tên bảng sau mệnh đề FORM và đặt điều kiện thích hợp trong mệnh đề WHERE để truy xuất dữ liệu từ ba bảng và tạo đầu ra thích hợp hơn các truy vấn SELECT trước đó.
CHỌN tên học sinhnhư ``Tên học sinh``, các khóa học.namenhư ``Tên khóa học``,TỐI ĐA(TRƯỜNG HỢP KHI result.mark_type ='Kiểm tra nội bộ'SAU ĐÓ result.marks END) 'CT',
TỐI ĐA(TRƯỜNG HỢP KHI result.mark_type ='Kỳ thi giữa kỳ'SAU ĐÓ result.marks END) 'Giữa',
TỐI ĐA(TRƯỜNG HỢP KHI result.mark_type ='Bài kiểm tra cuối cùng'SAU ĐÓ result.marks END) 'Cuối cùng',
TỔNG(result.marks) nhưToàn bộ
TỪ sinh viên, khóa học, kết quả
WHERE result.std_id = students.id và result.course_id = Course.course_id
GROUP BY result.std_id, result.course_id
ĐẶT HÀNG THEO result.std_id, result.course_id ASC;
Kết quả sau sẽ tạo ra sau khi thực hiện truy vấn trên.
Phần kết luận:
Cách bạn có thể triển khai chức năng của hàm Pivot () mà không cần sự hỗ trợ của hàm Pivot () trong MySQL được trình bày trong bài viết này bằng cách sử dụng một số dữ liệu giả. Tôi hy vọng, độc giả sẽ có thể chuyển đổi bất kỳ dữ liệu cấp hàng nào thành dữ liệu cấp cột bằng cách sử dụng truy vấn SELECT sau khi đọc bài viết này.