เชิงสัมพันธ์ จัดเก็บข้อมูลจำนวนมหาศาลในรูปแบบของตาราง ตารางเหล่านี้สามารถมีแถวและคอลัมน์จำนวนเท่าใดก็ได้ แต่ถ้าคุณต้องเปลี่ยนข้อมูลระดับแถวเป็นข้อมูลคอลัมน์ล่ะ? ในบทความเกี่ยวกับ SQL Pivot นี้ฉันจะแสดงให้คุณเห็นว่าคุณสามารถแปลงแถวเป็นคอลัมน์ใน SQL Server ได้อย่างไร
หัวข้อต่อไปนี้จะกล่าวถึงในบทความนี้:
PIVOT ใน SQL คืออะไร?
PIVOT ใช้เพื่อหมุนค่าตารางโดยการแปลงค่าเฉพาะของคอลัมน์เดียวเป็นหลายคอลัมน์ ใช้เพื่อหมุนแถวเป็นค่าคอลัมน์และเรียกใช้การรวมเมื่อจำเป็นกับค่าคอลัมน์ที่เหลือ
ในทางกลับกัน UNPIVOT ใช้เพื่อดำเนินการที่ตรงกันข้าม ดังนั้นจึงใช้เพื่อแปลงคอลัมน์ของตารางเฉพาะเป็นค่าคอลัมน์
ต่อไปในบทความนี้ให้เราเข้าใจไวยากรณ์ของ SQL Pivot
ไวยากรณ์:
SELECT NonPivoted ColumnName, [First Pivoted ColumnName] AS ColumnName, [Second Pivoted ColumnName] AS ColumnName, [Third Pivoted ColumnName] AS ColumnName, ... [Last Pivoted ColumnName] AS ColumnName FROM (SELECT query ที่สร้างข้อมูล) AS [alias สำหรับการสืบค้นเริ่มต้น] PIVOT ([AggregationFunction] (ColumName) สำหรับ [ColumnName ของคอลัมน์ที่ค่าจะกลายเป็นส่วนหัวของคอลัมน์] IN ([First Pivoted ColumnName], [Second Pivoted ColumnName], [Third Pivoted ColumnName] ... [last pivoted column])) AS [นามแฝงสำหรับ Pivot Table]
ที่นี่คุณยังสามารถใช้ไฟล์ ORDER BY clause เพื่อจัดเรียงค่าตามลำดับจากน้อยไปมากหรือมากไปหาน้อย ตอนนี้คุณรู้แล้วว่า PIVOT ใน SQL คืออะไรและไวยากรณ์พื้นฐานแล้วให้เราก้าวไปข้างหน้าและดูวิธีการใช้งาน
ตัวอย่าง
เพื่อความเข้าใจที่ดีขึ้นเราจะพิจารณาตารางต่อไปนี้เพื่ออธิบายตัวอย่างทั้งหมด
ตารางผู้จำหน่าย:
| รหัสซัพพลายเออร์ | DaysofManufacture | ค่าใช้จ่าย | รหัสลูกค้า | รหัสการซื้อ |
| หนึ่ง | 12 | 1230 | สิบเอ็ด | P1 |
| 2 | ยี่สิบเอ็ด | พ.ศ. 2186 | 22 | P2 |
| 3 | 32 | 2. 3. 4. 5 | สิบเอ็ด | P3 |
| 4 | 14 | 8765 | 22 | P1 |
| 5 | 42 | 3452 | 33 | P3 |
| 6 | 31 | 5431 | 33 | P1 |
| 7 | 41 | 2342 | สิบเอ็ด | P2 |
| 8 | 54 | 3654 | 22 | P2 |
| 9 | 33 | 1234 | สิบเอ็ด | P3 |
| 10 | 56 | 6832 | 33 | P2 |
ให้เราเขียนแบบสอบถามง่ายๆเพื่อดึงต้นทุนเฉลี่ยที่ลูกค้าแต่ละรายใช้ไป
เลือก CustomerID, AVG (ต้นทุน) เป็น AverageCostofCustomer จากซัพพลายเออร์ GROUP BY CustomerID
เอาท์พุต:
| รหัสลูกค้า | AverageCostofCustomer |
| สิบเอ็ด | 1787.75 |
| 22 | 4654 |
| 33 | 5238.33 |
ตอนนี้ให้เราบอกว่าเราต้องการหมุนตารางด้านบน ที่นี่ค่าคอลัมน์ CustomerID จะกลายเป็นส่วนหัวของคอลัมน์
- สร้างตาราง Pivot ด้วยหนึ่งแถวและสามคอลัมน์ SELECT 'AverageCostofCustomer' AS Cost_According_To_Customers, [11], [22], [33] FROM (SELECT CustomerID, Cost FROM Suppliers) AS SourceTable PIVOT (AVG (Cost) for CustomerID IN ( [11], [22], [33])) เป็น PivotTable
เอาท์พุต:
| Cost_According_To_Customers | สิบเอ็ด | 22 | 33 |
| AverageCostofCustomer | 1787.75 | 4654 | 5238.33 |
บันทึก: เมื่อคุณใช้ ฟังก์ชันรวม ด้วย PIVOT ค่า null จะไม่ได้รับการพิจารณาในขณะที่คำนวณการรวม
นั่นเป็นตัวอย่างพื้นฐาน แต่ตอนนี้ให้เราเข้าใจว่าประโยค PIVOT ทำงานอย่างไร
การทำงานของข้อ PIVOT
ดังที่คุณสามารถอ้างถึงข้างต้นในการสร้าง PIVOT TABLE คุณต้องทำตามขั้นตอนด้านล่าง:
- เลือกคอลัมน์สำหรับการหมุน
- จากนั้นเลือกตารางต้นทาง
- ใช้ตัวดำเนินการ PIVOT จากนั้นใช้ฟังก์ชันการรวม
- พูดถึงค่าเดือย
เลือกคอลัมน์สำหรับการหมุน
เริ่มแรกเราต้องระบุฟิลด์ที่จะรวมไว้ในผลลัพธ์ของเรา ในตัวอย่างของเราฉันพิจารณาคอลัมน์ AverageCostofCustomer ในตาราง Pivot จากนั้นเราสร้างคอลัมน์อีกสามคอลัมน์โดยมีส่วนหัวของคอลัมน์ 11, 22 และ 33 ตัวอย่าง -
วิธีการแยกวิเคราะห์ xml ใน java
เลือก 'AverageCostofCustomer' เป็น Cost_According_To_Customers, [11], [22], [33]
เลือกตารางต้นทาง
ถัดไปคุณต้องระบุคำสั่ง SELECT ซึ่งจะส่งคืนข้อมูลต้นทางสำหรับตาราง Pivot ในตัวอย่างของเราเรากำลังส่งคืนรหัสลูกค้าและต้นทุนจากตารางซัพพลายเออร์
(เลือกรหัสลูกค้าต้นทุนจากซัพพลายเออร์) เป็น SourceTable
ใช้ตัวดำเนินการ PIVOT จากนั้นใช้ฟังก์ชันการรวม
ถัดไปคุณต้องระบุฟังก์ชันการรวมที่จะใช้ในขณะสร้างตารางสาระสำคัญ ในตัวอย่างของเราฉันใช้ฟังก์ชัน AVG เพื่อคำนวณต้นทุนเฉลี่ย
PIVOT (AVG (ต้นทุน)
พูดถึงค่าเดือย
สุดท้ายคุณต้องพูดถึงค่าที่ต้องรวมไว้ในตาราง Pivot ที่เป็นผลลัพธ์ ค่าเหล่านี้จะใช้เป็นส่วนหัวของคอลัมน์ในตาราง Pivot
สำหรับ CustomerID IN ([11], [22], [33])) เป็น PivotTable
นั่นคือวิธีการทำงานของตัวดำเนินการ PIVOT ในบทความนี้เกี่ยวกับ SQL PIVOT ให้เราเข้าใจว่ามันแตกต่างจาก SQL UNPIVOT อย่างไร
SQL UNPIVOT
ตัวดำเนินการ SQL UNPIVOT ใช้เพื่อดำเนินการที่ตรงกันข้ามกับของ PIVOT ใช้เพื่อหมุนข้อมูลคอลัมน์เป็นข้อมูลระดับแถว ไวยากรณ์ของ UNPIVOT นั้นคล้ายกับของ PIVOT ข้อแตกต่างเพียงอย่างเดียวคือคุณต้องใช้ไฟล์ ' UNPIVOT” .
ตัวอย่าง:
ให้เราสร้างตารางด้วยคอลัมน์ SupplierID, AAA, BBB และ CCC ใส่ค่าน้อย ๆ ด้วย
สร้างตารางตัวอย่าง (SupplierID int, AAA int, BBB int, CCC int) GO INSERT INTO sampletable VALUES (1,3,5,6) INSERT INTO sampletable VALUES (2,9,2,8) INSERT INTO sampletable VALUES (3, 8,1,7) ไป
เอาท์พุต:
| รหัสซัพพลายเออร์ | AAA | BBB | CCC |
| หนึ่ง | 3 | 5 | 6 |
| 2 | 9 | 2 | 8 |
| 3 | 8 | หนึ่ง | 7 |
ตอนนี้สมมติว่าเราต้องการยกเลิกการใช้งานตาราง ในการทำเช่นนั้นคุณสามารถอ้างถึงรหัสต่อไปนี้:
เลือก SupplierID ลูกค้าผลิตภัณฑ์จาก (เลือกซัพพลายเออร์ D, AAA, BBB, CCC จากตัวอย่างตาราง) p UNPIVOT (ผลิตภัณฑ์สำหรับลูกค้าใน (AAA, BBB, CCC)) ดังตัวอย่าง GO
| รหัสซัพพลายเออร์ | ลูกค้า | ผลิตภัณฑ์ |
หนึ่ง | AAA | 3 |
หนึ่ง | BBB | 5 |
หนึ่ง | CCC | 6 |
2 | AAA | 9 |
2 | BBB | 2 |
2 | CCC | 8 |
3 | AAA | 8 |
3 | BBB | หนึ่ง ความแตกต่างของ java ระหว่างการขยายและการใช้งาน |
3 | CCC | 7 |
นั่นคือวิธีที่คุณสามารถใช้ SQL PIVOT และ UNPIVOT ด้วยเหตุนี้เราจึงมาจบบทความนี้ ฉันหวังว่าคุณจะเข้าใจวิธีใช้ SQL หากคุณต้องการเรียนรู้เพิ่มเติมเกี่ยวกับ MySQL และทำความรู้จักกับฐานข้อมูลเชิงสัมพันธ์แบบโอเพนซอร์สนี้จากนั้นตรวจสอบไฟล์ ซึ่งมาพร้อมกับการฝึกอบรมสดที่นำโดยผู้สอนและประสบการณ์โครงการในชีวิตจริง การฝึกอบรมนี้จะช่วยให้คุณเข้าใจ MySQL ในเชิงลึกและช่วยให้คุณบรรลุความเชี่ยวชาญในเรื่องนั้น ๆ
มีคำถามสำหรับเรา? โปรดระบุไว้ในส่วนความคิดเห็นของบทความนี้เกี่ยวกับ SQL Pivot แล้วเราจะติดต่อกลับไป