ใน SQL ส่วนคำสั่ง WITH เรียกอีกอย่างว่า CTE เป็นคุณลักษณะที่มีประสิทธิภาพที่ช่วยให้เราสามารถสร้างชุดผลลัพธ์ชั่วคราวภายในแบบสอบถามได้ บทบาทสำคัญประการหนึ่งของ CTE คือการลดความซับซ้อนของการสืบค้นที่ซับซ้อนให้กลายเป็นการสืบค้นย่อยที่เล็กลงและนำกลับมาใช้ใหม่ได้ สิ่งนี้ช่วยให้โค้ดสามารถอ่านและบำรุงรักษาได้มากขึ้นในระยะยาว
เข้าร่วมกับเราในบทช่วยสอนนี้ในขณะที่เราสำรวจการทำงานของ Common Table Expressions โดยใช้คำสั่งย่อย WITH และฟังก์ชันการทำงานที่รองรับ
ความต้องการ:
เพื่อวัตถุประสงค์ในการสาธิต เราจะใช้สิ่งต่อไปนี้:
- MySQL เวอร์ชัน 8.0 ขึ้นไป
- ฐานข้อมูลตัวอย่างสากีลา
เมื่อมีคุณสมบัติตรงตามข้อกำหนดแล้ว เราจะสามารถดำเนินการเรียนรู้เพิ่มเติมเกี่ยวกับ CTE และคำสั่ง WITH ได้
SQL พร้อมข้อ
ส่วนคำสั่ง WITH ช่วยให้เราสามารถกำหนดชุดผลลัพธ์ชั่วคราวตั้งแต่หนึ่งชุดขึ้นไป ซึ่งเรียกว่า Common Table Expressions
เราสามารถอ้างอิง CTE ที่เป็นผลลัพธ์ในการสืบค้นหลักได้เช่นเดียวกับตารางหรือชุดผลลัพธ์อื่นๆ สิ่งนี้มีบทบาทสำคัญในการสร้างแบบสอบถาม SQL แบบโมดูลาร์
แม้ว่าไวยากรณ์ของ CTE อาจแตกต่างกันเล็กน้อยขึ้นอยู่กับความต้องการของคุณ แต่ต่อไปนี้จะแสดงไวยากรณ์พื้นฐานของ CTE ใน SQL:
ด้วย cte_name (column1, column2, ...) AS (
--แบบสอบถาม CTE
เลือก ...
จาก ...
ที่ไหน ...
)
-- คำถามหลัก
เลือก ...
จาก ...
เข้าร่วม cte_name บน ...
ที่ไหน ...
เราเริ่มต้นด้วยคีย์เวิร์ด WITH ซึ่งบอกฐานข้อมูล SQL ว่าเราต้องการสร้างและใช้ CTE
ต่อไป เราจะระบุชื่อของ CTE ซึ่งช่วยให้เราอ้างอิงในแบบสอบถามอื่นๆ ได้
นอกจากนี้เรายังระบุรายการชื่อคอลัมน์เพิ่มเติมหาก CTE มีนามแฝงของคอลัมน์ด้วย
ต่อไป เราจะกำหนดการสืบค้น CTE ต่อไป ประกอบด้วยงานทั้งหมดหรือข้อมูลที่ CTE ดำเนินการอยู่ในวงเล็บคู่หนึ่ง
สุดท้ายนี้ เราระบุข้อความค้นหาหลักที่อ้างอิงถึง CTE
ตัวอย่างการใช้งาน:
หนึ่งในวิธีที่ดีที่สุดในการทำความเข้าใจวิธีใช้และทำงานกับ CTE คือการดูตัวอย่างที่ใช้งานได้จริง
ยกตัวอย่างฐานข้อมูลตัวอย่าง Sakila สมมติว่าเราต้องการหาลูกค้า 10 อันดับแรกที่มีจำนวนการเช่าสูงสุด
ลองดูที่ CTE ที่แสดงต่อไปนี้
การใช้ SQL WITH Clause เพื่อค้นหาลูกค้า 10 อันดับแรกที่มีจำนวนการเช่าสูงสุด:
ด้วย CustomerRentals AS (เลือก c.customer_id, c.first_name, c.last_name, COUNT(r.rental_id) AS rent_count
จากลูกค้าค
เข้าร่วมการเช่า r บน c.customer_id = r.customer_id
จัดกลุ่มตาม c.customer_id, c.first_name, c.last_name
)
เลือก *
จากลูกค้าเช่า
เรียงลำดับตาม DESC เช่า_จำนวน
จำกัด 10;
ในตัวอย่างที่กำหนด เราเริ่มต้นด้วยการกำหนด CTE ใหม่โดยใช้คีย์เวิร์ด WITH ตามด้วยชื่อที่เราต้องการกำหนดให้กับ CTE ในกรณีนี้ เราเรียกมันว่า 'CustomerRentals'
ภายในเนื้อหา CTE เราคำนวณจำนวนการเช่าสำหรับลูกค้าแต่ละรายโดยการเข้าร่วมตารางลูกค้าและตารางการเช่า
สุดท้ายนี้ ในแบบสอบถามหลัก เราเลือกคอลัมน์ทั้งหมดจาก CTE เรียงลำดับผลลัพธ์ตามจำนวนการเช่า (ลำดับจากมากไปน้อย) และจำกัดเอาต์พุตให้เหลือเพียง 10 แถวบนสุด
สิ่งนี้ช่วยให้เราสามารถดึงข้อมูลลูกค้าที่มีจำนวนการเช่าสูงสุดดังที่แสดงในผลลัพธ์ต่อไปนี้:
CTE แบบเรียกซ้ำ
ในบางกรณี คุณอาจต้องจัดการกับโครงสร้างข้อมูลแบบลำดับชั้น นี่คือจุดที่ CTE แบบเรียกซ้ำเข้ามามีบทบาท
ให้เรายกตัวอย่างกรณีที่เราต้องการนำทางองค์กรที่มีลำดับชั้นหรือเพื่อแสดงโครงสร้างที่เหมือนต้นไม้ เราสามารถใช้คีย์เวิร์ด WITH RECURSIVE เพื่อสร้าง CTE แบบเรียกซ้ำ
เนื่องจากไม่มีข้อมูลแบบลำดับชั้นที่เราสามารถใช้ในฐานข้อมูล Sakila เพื่อแสดง CTE แบบเรียกซ้ำ ให้เรายกตัวอย่างพื้นฐานขึ้นมา
สร้างแผนกตาราง (department_id INT คีย์หลัก AUTO_INCREMENT,
department_name VARCHAR (255) ไม่เป็นโมฆะ
parent_department_id INT,
คีย์ต่างประเทศ (parent_department_id) แผนกอ้างอิง (department_id)
);
INSERT INTO แผนก (department_name, parent_department_id)
ค่านิยม
('องค์กร', NULL),
('การเงิน', 1),
('ทรัพยากรบุคคล', 1),
('การบัญชี', 2),
'การสรรหา', 3),
('บัญชีเงินเดือน', 4);
ในกรณีนี้ เรามีตาราง 'แผนก' ตัวอย่างพร้อมข้อมูลที่สุ่มบางส่วน ในการค้นหาโครงสร้างลำดับชั้นของแผนกต่างๆ เราสามารถใช้ CTE แบบเรียกซ้ำได้ดังนี้
ด้วยแผนก RECURSIVE ลำดับชั้น AS (เลือก department_id, department_name, parent_department_id
จากแผนก
โดยที่ parent_department_id เป็นโมฆะ
ยูเนี่ยนทั้งหมด
เลือก d.department_id, d.department_name, d.parent_department_id
จากแผนกง
เข้าร่วม DepartmentHierarchy dh ON d.parent_department_id = dh.department_id
)
เลือก *
จาก แผนก ลำดับชั้น;
ในกรณีนี้ CTE แบบเรียกซ้ำจะเริ่มต้นด้วยแผนกที่มีค่า NULL “parent_department_id” (แผนกราก) และดึงข้อมูลแผนกย่อยแบบเรียกซ้ำ
บทสรุป
ในบทช่วยสอนนี้ เราได้เรียนรู้เกี่ยวกับคุณสมบัติพื้นฐานและมีประโยชน์ที่สุดในฐานข้อมูล SQL เช่น Common Table Expressions โดยการทำความเข้าใจวิธีทำงานกับคำสำคัญ WITH