วิชาบังคับก่อน:
คุณต้องสร้างฐานข้อมูลและตารางที่เกี่ยวข้อง โดยที่แถวของตารางหนึ่งจะถูกแปลงเป็นคอลัมน์ เช่น ฟังก์ชัน PIVOT() เรียกใช้คำสั่ง SQL ต่อไปนี้เพื่อสร้างฐานข้อมูลชื่อ ' unidb ' และสร้างสามตารางชื่อ ' นักเรียน ',' หลักสูตร ' และ ' ผลลัพธ์ '. นักเรียน และ ผลลัพธ์ ตารางจะสัมพันธ์กันด้วยความสัมพันธ์แบบหนึ่งต่อกลุ่มและ หลักสูตร และ ผลลัพธ์ ตารางจะสัมพันธ์กันด้วยความสัมพันธ์แบบหนึ่งต่อกลุ่มที่นี่ สร้างคำสั่งของ ผลลัพธ์ ตารางมีข้อจำกัดของคีย์ต่างประเทศสองข้อสำหรับฟิลด์ std_id , และ course_id .
สร้างฐานข้อมูล unidb;
ใช้ unidb;
สร้างนักเรียนตาราง(
NSคีย์หลัก INT,
ชื่อ varchar(ห้าสิบ)ไม่เป็นโมฆะ,
แผนก VARCHAR(สิบห้า)ไม่เป็นโมฆะ);
หลักสูตรสร้างตาราง(
course_id VARCHAR(ยี่สิบ)คีย์หลัก
ชื่อ varchar(ห้าสิบ)ไม่เป็นโมฆะ,
เครดิต SMALLINT NOT NULL);
สร้างผลลัพธ์ตาราง(
std_id INT ไม่เป็นโมฆะ
course_id VARCHAR(ยี่สิบ)ไม่เป็นโมฆะ,
mark_type VARCHAR(ยี่สิบ)ไม่เป็นโมฆะ,
ทำเครื่องหมาย SMALLINT NOT NULL,
กุญแจต่างประเทศ(std_id)อ้างอิง นักศึกษา(NS),
กุญแจต่างประเทศ(course_id)หลักสูตรอ้างอิง(course_id),
คีย์หลัก(std_id, course_id, mark_type));
แทรกบันทึกบางส่วนลงใน นักศึกษารายวิชาและผลการเรียน ตาราง ต้องแทรกค่าลงในตารางตามข้อจำกัดที่ตั้งไว้ในขณะที่สร้างตาราง
INSERT INTO ค่านิยมของนักเรียน
( '1937463','ฮาร์เปอร์ ลี','คสช.'),
( '1937464','การ์เซีย มาร์เกซ','คสช.'),
( '1937465','ฟอร์สเตอร์ อีเอ็ม','คสช.'),
( '1937466','ราล์ฟ เอลลิสัน','คสช.');
INSERT INTO หลักสูตร ค่า
( 'CSE-401','การเขียนโปรแกรมเชิงวัตถุ',3),
( 'CSE-403','โครงสร้างข้อมูล',2),
( 'CSE-407','การเขียนโปรแกรม Unix',2);
INSERT INTO ผลลัพธ์ VALUES
( '1937463','CSE-401','การสอบภายใน',สิบห้า),
( '1937463','CSE-401','สอบกลางภาค',ยี่สิบ),
( '1937463','CSE-401','สอบปลายภาค',35),
( '1937464','CSE-403','การสอบภายใน',17),
( '1937464','CSE-403','สอบกลางภาค',สิบห้า),
( '1937464','CSE-403','สอบปลายภาค',30),
( '1937465','CSE-401','การสอบภายใน',18),
( '1937465','CSE-401','สอบกลางภาค',2. 3),
( '1937465','CSE-401','สอบปลายภาค',38),
( '1937466','CSE-407','การสอบภายใน',ยี่สิบ),
( '1937466','CSE-407','สอบกลางภาค',22),
( '1937466','CSE-407','สอบปลายภาค',40);
ที่นี่, ผลลัพธ์ ตารางมีค่าเดียวกันหลายค่าสำหรับ std_id , mark_type และ course_id คอลัมน์ในแต่ละแถว วิธีแปลงแถวเหล่านี้เป็นคอลัมน์ของตารางนี้เพื่อแสดงข้อมูลในรูปแบบที่เป็นระเบียบมากขึ้นจะแสดงในส่วนถัดไปของบทช่วยสอนนี้
หมุนแถวเป็นคอลัมน์โดยใช้คำสั่ง CASE:
เรียกใช้คำสั่ง SELECT อย่างง่ายต่อไปนี้เพื่อแสดงบันทึกทั้งหมดของ ผลลัพธ์ ตาราง.
เลือก*จาก ผลลัพธ์;
ผลลัพธ์จะแสดงคะแนนของนักเรียนสี่คนสำหรับการสอบสามประเภทในสามหลักสูตร ดังนั้นค่าของ std_id , course_id และ mark_type มีการทำซ้ำหลายครั้งสำหรับนักเรียน หลักสูตร และประเภทการสอบที่แตกต่างกัน
ผลลัพธ์จะอ่านง่ายขึ้นหากสามารถเขียนแบบสอบถาม SELECT ได้อย่างมีประสิทธิภาพมากขึ้นโดยใช้คำสั่ง CASE คำสั่ง SELECT ที่มีคำสั่ง CASE ต่อไปนี้จะเปลี่ยนค่าที่ซ้ำกันของแถวให้เป็นชื่อคอลัมน์ และแสดงเนื้อหาของตารางในรูปแบบที่เข้าใจง่ายขึ้นสำหรับผู้ใช้
เลือก result.std_id, result.course_id,MAX(กรณีเมื่อ result.mark_type ='การสอบภายใน'แล้วผลลัพธ์เครื่องหมาย END) 'การสอบภายใน',
MAX(กรณีเมื่อ result.mark_type ='สอบกลางภาค'แล้วผลลัพธ์เครื่องหมาย END) 'สอบกลางภาค',
MAX(กรณีเมื่อ result.mark_type ='สอบปลายภาค'แล้วผลลัพธ์เครื่องหมาย END) 'สอบปลายภาค'
จากผลลัพธ์
จัดกลุ่มตามผล.std_id,ผลลัพธ์.คอร์ส_id
เรียงลำดับตาม result.std_id, result.course_id ASC;
ผลลัพธ์ต่อไปนี้จะปรากฏขึ้นหลังจากรันคำสั่งด้านบนซึ่งสามารถอ่านได้ง่ายกว่าเอาต์พุตก่อนหน้า
หมุนแถวเป็นคอลัมน์โดยใช้ CASE และ SUM():
ถ้าคุณต้องการนับจำนวนรวมของแต่ละหลักสูตรของนักเรียนทุกคนจากตาราง คุณต้องใช้ฟังก์ชันรวม ผลรวม() จัดกลุ่มโดย std_id และ course_id ด้วยคำสั่ง CASE แบบสอบถามต่อไปนี้ถูกสร้างขึ้นโดยการแก้ไขการสืบค้นก่อนหน้าด้วยฟังก์ชัน SUM() และ GROUP BY clause
เลือก result.std_id,result.course_id,MAX(กรณีเมื่อ result.mark_type ='การสอบภายใน'แล้วผลลัพธ์เครื่องหมาย END) 'การสอบภายใน',
MAX(กรณีเมื่อ result.mark_type ='สอบกลางภาค'แล้วผลลัพธ์เครื่องหมาย END) 'สอบกลางภาค',
MAX(กรณีเมื่อ result.mark_type ='สอบปลายภาค'แล้วผลลัพธ์เครื่องหมาย END) 'สอบปลายภาค',
SUM(result.marks) เช่นรวม
จากผลลัพธ์
จัดกลุ่มตามผล.std_id,ผลลัพธ์.คอร์ส_id
เรียงลำดับตาม result.std_id, result.course_id ASC;
ผลลัพธ์แสดงคอลัมน์ใหม่ชื่อ รวม ที่แสดงผลคะแนนรวมของข้อสอบทุกประเภทของแต่ละหลักสูตรที่นักเรียนแต่ละคนได้รับ
หมุนแถวเป็นคอลัมน์ในหลายตาราง:
แบบสอบถามสองข้อก่อนหน้านี้ใช้กับ ผลลัพธ์ ตาราง. ตารางนี้เกี่ยวข้องกับอีกสองตาราง เหล่านี้คือ นักเรียน และ หลักสูตร . หากคุณต้องการแสดงชื่อนักเรียนแทนรหัสนักเรียนและชื่อหลักสูตรแทนรหัสหลักสูตร คุณต้องเขียนแบบสอบถาม SELECT โดยใช้ตารางที่เกี่ยวข้องสามตาราง นักเรียน , หลักสูตร และ ผลลัพธ์ . แบบสอบถาม SELECT ต่อไปนี้ถูกสร้างขึ้นโดยการเพิ่มชื่อตารางสามชื่อหลังส่วนคำสั่ง FORM และการตั้งค่าเงื่อนไขที่เหมาะสมในส่วนคำสั่ง WHERE เพื่อดึงข้อมูลจากสามตารางและสร้างผลลัพธ์ที่เหมาะสมกว่าแบบสอบถาม SELECT ก่อนหน้า
SELECT นักเรียนชื่อเช่น ``ชื่อนักเรียน``,courses.nameเช่น ``ชื่อหลักสูตร``,MAX(กรณีเมื่อ result.mark_type ='การสอบภายใน'แล้วผลลัพธ์เครื่องหมาย END) 'ซีที',
MAX(กรณีเมื่อ result.mark_type ='สอบกลางภาค'แล้วผลลัพธ์เครื่องหมาย END) 'กลาง',
MAX(กรณีเมื่อ result.mark_type ='สอบปลายภาค'แล้วผลลัพธ์เครื่องหมาย END) 'สุดท้าย',
SUM(result.marks) เช่นรวม
จากนักศึกษา รายวิชา ผลลัพธ์
โดยที่ result.std_id = students.id และ result.course_id= courses.course_id
จัดกลุ่มตามผล.std_id,ผลลัพธ์.คอร์ส_id
เรียงลำดับตาม result.std_id, result.course_id ASC;
ผลลัพธ์ต่อไปนี้จะสร้างหลังจากดำเนินการแบบสอบถามข้างต้น
บทสรุป:
วิธีการใช้ฟังก์ชัน Pivot() โดยไม่รองรับฟังก์ชัน Pivot() ใน MySQL จะแสดงในบทความนี้โดยใช้ข้อมูลจำลอง ฉันหวังว่าผู้อ่านจะสามารถแปลงข้อมูลระดับแถวเป็นข้อมูลระดับคอลัมน์ได้โดยใช้แบบสอบถาม SELECT หลังจากอ่านบทความนี้