MySQL จัดกลุ่มตามและมีประโยคพร้อมตัวอย่าง

คำสั่ง GROUP BY ใน SQL คืออะไร?

GROUP BY clause เป็นคำสั่ง SQL ที่ใช้ แถวกลุ่มที่มีค่าเหมือนกันคำสั่งนี้เขียนอยู่ภายในคำสั่ง SELECT และโดยปกติจะใช้ร่วมกับฟังก์ชันรวมเพื่อสร้างรายงานสรุปจากฐานข้อมูล

มันทำหน้าที่แบบนั้น: มัน สรุปข้อมูล ข้อมูลถูกจัดเก็บไว้ในฐานข้อมูล คำสั่งค้นหาที่มีคำสั่ง GROUP BY เรียกว่าคำสั่งค้นหาแบบจัดกลุ่ม และจะส่งคืนข้อมูลเพียงแถวเดียวสำหรับแต่ละรายการที่ถูกจัดกลุ่ม

กลุ่ม SQL ตามไวยากรณ์

เมื่อเข้าใจวัตถุประสงค์ของข้อความดังกล่าวแล้ว ลองมาดูไวยากรณ์ของคำสั่งค้นหาแบบจัดกลุ่มพื้นฐานกัน

SELECT statements... GROUP BY column_name1[, column_name2, ...] [HAVING condition];

ที่นี่

• "คำสั่ง SELECT…“คือมาตรฐาน” SQL เลือก การสอบถามคำสั่ง
• "จัดกลุ่มตาม column_name1“คือประโยคย่อยที่ทำหน้าที่ของกลุ่ม”ping อ้างอิงจาก column_name1
• "[, column_name2, …]" เป็นตัวเลือกเสริม และใช้แทนชื่อคอลัมน์อื่นๆ เมื่อกลุ่มping ทำบนคอลัมน์มากกว่าหนึ่งคอลัมน์
• "[มีอาการ]" เป็นตัวเลือกเสริม และใช้เพื่อจำกัดแถวที่ได้รับผลกระทบจากคำสั่ง GROUP BY มีลักษณะคล้ายกับ WHERE ข้อยกเว้นว่าจะนำไปใช้หลังจากกลุ่มping.

กลุ่มping การใช้คอลัมน์เดียว

วิธีที่เร็วที่สุดในการดูผลกระทบของคำสั่ง GROUP BY ใน SQL คือการเปรียบเทียบคำสั่ง SQL ที่ไม่ได้จัดกลุ่มกับคำสั่ง SQL ที่จัดกลุ่มแล้ว เริ่มต้นด้วยคำสั่ง SQL ง่ายๆ ที่ส่งคืนข้อมูลเพศทั้งหมดในตารางสมาชิก

SELECT `gender` FROM `members`;

ผลลัพธ์ที่ได้คือเก้าแถว และค่าทุกค่าซ้ำกัน สมมติว่าเราต้องการค่าที่ไม่ซ้ำกันสำหรับเพศแทน คำสั่ง SQL ด้านล่างจะเพิ่มส่วน GROUP BY เข้าไป

SELECT `gender` FROM `members` GROUP BY `gender`;

ดำเนินการสคริปต์ข้างต้นใน MySQL ม้านั่งทำงานของช่างเครื่อง จาก myflixdb เราจะได้ผลลัพธ์ดังนี้

โปรดสังเกตว่ามีการแสดงผลเพียงสองแถว เนื่องจากตารางมีข้อมูลเพศเพียงสองประเภทเท่านั้น คำสั่ง GROUP BY ได้จัดกลุ่มสมาชิกที่เป็น "ชาย" ทั้งหมดเข้าด้วยกันและแสดงผลเพียงแถวเดียว และได้ทำเช่นเดียวกันกับสมาชิกที่เป็น "หญิง"

กลุ่มping การใช้คอลัมน์หลายคอลัมน์

กลุ่มping การใช้คอลัมน์เดียวมักจะหยาบเกินไปสำหรับรายงานจริง คำสั่ง GROUP BY ยอมรับรายการคอลัมน์ที่คั่นด้วยเครื่องหมายจุลภาค และการรวมกันของค่าในคอลัมน์เหล่านั้นจะกำหนดแต่ละกลุ่ม

สมมติว่าเราต้องการรายการค่า category_id ของภาพยนตร์และปีที่ภาพยนตร์เหล่านั้นออกฉาย ลองดูผลลัพธ์ของคำสั่งค้นหาแบบง่ายๆ นี้ก่อน

SELECT `category_id`, `year_released` FROM `movies`;

แถวที่ไฮไลต์แสดงว่าผลลัพธ์มีข้อมูลซ้ำกัน การเรียกใช้คำสั่ง SQL เดียวกันโดยใช้ GROUP BY จะลบข้อมูลซ้ำเหล่านั้นออก

SELECT `category_id`, `year_released` FROM `movies` GROUP BY `category_id`, `year_released`;

ดำเนินการสคริปต์ข้างต้นใน MySQL การทดสอบด้วย Workbench กับ myflixdb ให้ผลลัพธ์ดังที่แสดงด้านล่าง

คำสั่ง GROUP BY จะทำงานกับทั้ง category_id และ year_released เพื่อระบุ ที่ไม่เหมือนใคร แถว แถวที่ซ้ำกันสองแถวสำหรับหมวดหมู่ที่ 6 ในปี 2007 ถูกรวมเข้าเป็นแถวเดียว

หลักการง่ายๆ: หากรหัสหมวดหมู่เหมือนกัน แต่ปีที่วางจำหน่ายแตกต่างกัน แถวนั้นจะถือว่าเป็นข้อมูลที่ไม่ซ้ำกัน หากรหัสหมวดหมู่และปีที่วางจำหน่ายเหมือนกันมากกว่าหนึ่งแถว แถวเหล่านั้นจะถือว่าเป็นข้อมูลซ้ำ และจะแสดงเพียงแถวเดียวเท่านั้น

กลุ่มping และฟังก์ชันรวม

การลบรายการซ้ำเป็นสิ่งที่มีประโยชน์ แต่พลังที่แท้จริงของการจัดกลุ่มอยู่ที่...ping จะปรากฏขึ้นเมื่อจับคู่กับ ฟังก์ชั่นรวมฟังก์ชันรวมจะคำนวณค่าหนึ่งค่าสำหรับแต่ละกลุ่ม: COUNT นับจำนวนแถว, SUM บวกค่าต่างๆ และ AVG, MIN และ MAX อธิบายถึงช่วงการกระจายตัว

สมมติว่าเราต้องการทราบจำนวนสมาชิกชายและหญิงทั้งหมดในฐานข้อมูล สคริปต์ด้านล่างนี้จะทำเช่นนั้น

SELECT `gender`, COUNT(`membership_number`) FROM `members` GROUP BY `gender`;

ดำเนินการสคริปต์ข้างต้นใน MySQL การทดสอบด้วย Workbench กับ myflixdb ให้ผลลัพธ์ดังต่อไปนี้

แถวต่างๆ จะถูกจัดกลุ่มตามค่าเพศที่ไม่ซ้ำกัน และจำนวนแถวภายในแต่ละกลุ่มจะถูกนับโดยฟังก์ชันการรวม COUNT บันทึกสมาชิกทั้งเก้ารายการจะถูกยุบรวมเป็นสองแถวสรุป

การจำกัดผลลัพธ์การค้นหาโดยใช้คำสั่ง HAVING

กลุ่มpingไม่จำเป็นต้องใช้เงื่อนไขสำหรับทุกแถวในตารางเสมอไป บางครั้งรายงานต้องจำกัดอยู่เฉพาะเกณฑ์ที่กำหนด และนั่นคือหน้าที่ของคำสั่ง HAVING

สมมติว่าเราต้องการทราบปีที่ออกฉายทั้งหมดของภาพยนตร์ในหมวดหมู่รหัส 8 สคริปต์ด้านล่างนี้จะช่วยให้ได้ผลลัพธ์นั้น

SELECT * FROM `movies` GROUP BY `category_id`, `year_released` HAVING `category_id` = 8;

ดำเนินการสคริปต์ข้างต้นใน MySQL การทดสอบด้วย Workbench กับ myflixdb ให้ผลลัพธ์ดังที่แสดงด้านล่าง

เฉพาะภาพยนตร์ที่มีรหัสหมวดหมู่ 8 เท่านั้นที่ถูกเก็บไว้โดยเงื่อนไข HAVING

คำเตือน: MySQL เวอร์ชัน 5.7 และเวอร์ชันที่ใหม่กว่าจะเปิดใช้งานโหมด ONLY_FULL_GROUP_BY โดยค่าเริ่มต้น และในโหมดนั้น คำสั่ง SELECT * ที่มีเงื่อนไข GROUP BY จะถูกปฏิเสธ เนื่องจาก movie_id, title และ director ไม่ได้ถูกจัดกลุ่มหรือรวมเข้าด้วยกัน ในการใช้งานจริง ควรระบุชื่อคอลัมน์ที่จัดกลุ่มอย่างชัดเจน ตัวอย่างเช่น SELECT category_id, year_released FROM movies GROUP BY category_id, year_released HAVING category_id = 8;

WHERE เทียบกับ HAVING เทียบกับ GROUP BY เทียบกับ ORDER BY

ผู้เริ่มต้นมักจะสับสนระหว่างข้อความทั้งสี่นี้ เพราะทั้งหมดล้วนมีส่วนในการกำหนดชุดผลลัพธ์ ความแตกต่างอยู่ที่... โดยหมายถึง MySQL คำสั่ง WHERE จะทำงานก่อนการจัดกลุ่มแถว คำสั่ง HAVING จะทำงานหลังจากนั้น และคำสั่ง ORDER BY จะทำงานเป็นลำดับสุดท้าย

ผลที่ตามมาในทางปฏิบัติคือเรื่องประสิทธิภาพ การกรองด้วย WHERE จะลบแถวก่อนกลุ่มออกping งานเริ่มต้นแล้ว ดังนั้นเงื่อนไขที่ไม่ขึ้นอยู่กับผลลัพธ์โดยรวมจึงควรอยู่ใน WHERE มากกว่า HAVING

คำถามที่พบบ่อย