มีเครื่องมืออันทรงพลังมากมายในคลังแสงสำหรับจัดการข้อมูลที่จัดเก็บในรูปแบบของตาราง

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

มีพารามิเตอร์ SQL: คำอธิบาย

ประการแรก เป็นที่น่าสังเกตว่าพารามิเตอร์นี้เป็นทางเลือกและใช้ร่วมกับพารามิเตอร์ GROUP BY เท่านั้น ดังที่คุณจำได้ GROUP BY จะใช้เมื่อมีการใช้ฟังก์ชันรวมใน SELECT และจำเป็นต้องได้รับผลลัพธ์ของการคำนวณสำหรับกลุ่มเฉพาะ หาก WHERE อนุญาตให้คุณตั้งค่าเงื่อนไขการเลือกก่อนที่จะจัดกลุ่มข้อมูล HAVING จะมีเงื่อนไขที่เกี่ยวข้องกับข้อมูลในกลุ่มโดยตรง เพื่อความเข้าใจที่ดีขึ้นเรามาดูตัวอย่างพร้อมวงจรที่แสดงในรูปด้านล่าง

นี่เป็นตัวอย่างที่ดีเยี่ยมที่ให้คำอธิบาย HAVING SQL ตารางจะได้รับรายชื่อผลิตภัณฑ์ บริษัทที่ผลิต และฟิลด์อื่นๆ ในแบบสอบถามที่มุมขวาบน เรากำลังพยายามรับข้อมูลเกี่ยวกับจำนวนสินค้าที่แต่ละบริษัทผลิต และในผลลัพธ์นี้ เราต้องการแสดงเฉพาะบริษัทที่ผลิตสินค้ามากกว่า 2 รายการ พารามิเตอร์ GROUP BY จะสร้างกลุ่มสามกลุ่มที่สอดคล้องกับชื่อบริษัท โดยแต่ละกลุ่มจะมีการคำนวณจำนวนผลิตภัณฑ์ (แถว) แต่พารามิเตอร์ HAVING พร้อมเงื่อนไข จะตัดกลุ่มหนึ่งกลุ่มออกจากตัวอย่างผลลัพธ์ เนื่องจากไม่ตรงตามเงื่อนไข เป็นผลให้เราได้สองกลุ่มที่สอดคล้องกับบริษัทที่มีปริมาณการผลิต 5 และ 3

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

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

พารามิเตอร์ SQL HAVING: ตัวอย่าง ไวยากรณ์

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

บทสรุป

อย่างที่คุณเห็นไม่มีอะไรซับซ้อน ตัวดำเนินการที่กำหนดเลขที่ ความหมายจะใช้ในลักษณะเดียวกับ WHERE สิ่งสำคัญคือต้องเข้าใจว่า WHERE ใช้กับข้อมูลที่เลือกทั้งหมด และ HAVING ใช้กับกลุ่มที่กำหนดในพารามิเตอร์ GROUP BY เท่านั้น เราได้นำเสนอคำอธิบายที่ครอบคลุมของ HAVING SQL ซึ่งเพียงพอสำหรับคุณในการทำงานด้วยความมั่นใจ

ฉันจะทราบจำนวนรุ่นพีซีที่ผลิตโดยซัพพลายเออร์รายใดรายหนึ่งได้อย่างไร วิธีกำหนดราคาเฉลี่ยของคอมพิวเตอร์ที่มีราคาเท่ากัน ข้อกำหนด? คำถามเหล่านี้และคำถามอื่นๆ ที่เกี่ยวข้องกับข้อมูลทางสถิติสามารถตอบได้โดยใช้ ฟังก์ชันสุดท้าย (รวม). มาตรฐานมีฟังก์ชันรวมดังต่อไปนี้:

ฟังก์ชันทั้งหมดนี้ส่งคืนค่าเดียว ขณะเดียวกันก็มีฟังก์ชั่นต่างๆ นับ, นาทีและ สูงสุดใช้ได้กับข้อมูลทุกประเภทในขณะนั้น ผลรวมและ เฉลี่ยใช้สำหรับช่องตัวเลขเท่านั้น ความแตกต่างระหว่างฟังก์ชั่น นับ(*)และ นับ(<имя поля>) คืออันที่สองไม่ได้คำนึงถึงค่า NULL เมื่อคำนวณ

ตัวอย่าง. ค้นหาราคาต่ำสุดและสูงสุดสำหรับคอมพิวเตอร์ส่วนบุคคล:

ตัวอย่าง. ค้นหาจำนวนคอมพิวเตอร์ที่ผลิตโดยผู้ผลิต A:

ตัวอย่าง. หากเราสนใจปริมาณ รุ่นต่างๆผลิตโดยผู้ผลิต A จากนั้นสามารถกำหนดแบบสอบถามได้ดังนี้ (โดยใช้ข้อเท็จจริงที่ว่าในตารางผลิตภัณฑ์แต่ละรุ่นจะถูกบันทึกหนึ่งครั้ง):

ตัวอย่าง. ค้นหาจำนวนรุ่นต่างๆ ที่ผลิตโดยผู้ผลิต A คำค้นหานี้คล้ายกับรุ่นก่อนหน้า ซึ่งจำเป็นต้องระบุจำนวนรุ่นทั้งหมดที่ผลิตโดยผู้ผลิต A ที่นี่คุณจะต้องค้นหาจำนวนรุ่นที่แตกต่างกันด้วย โต๊ะพีซี (เช่น โต๊ะวางขาย)

เพื่อให้แน่ใจว่ามีการใช้เฉพาะค่าที่ไม่ซ้ำเมื่อได้รับตัวบ่งชี้ทางสถิติเมื่อใด การโต้แย้ง ฟังก์ชันรวม สามารถใช้ได้ พารามิเตอร์ที่แตกต่าง. อื่น พารามิเตอร์ทั้งหมดเป็นค่าเริ่มต้นและถือว่าค่าที่ส่งคืนทั้งหมดในคอลัมน์ถูกนับ ผู้ดำเนินการ

หากเราต้องการได้รับจำนวนพีซีรุ่นที่ผลิต ทุกคนผู้ผลิตคุณจะต้องใช้ จัดกลุ่มตามข้อตามด้วยวากยสัมพันธ์ ข้อไหน.

จัดกลุ่มตามข้อ

จัดกลุ่มตามข้อใช้เพื่อกำหนดกลุ่มของบรรทัดเอาต์พุตที่สามารถนำไปใช้ได้ ฟังก์ชันรวม (COUNT, MIN, MAX, AVG และ SUM). หากไม่มีส่วนย่อยนี้และใช้ฟังก์ชันการรวม คอลัมน์ทั้งหมดที่มีชื่อกล่าวถึง เลือกจะต้องรวมอยู่ใน ฟังก์ชันรวมและฟังก์ชันเหล่านี้จะนำไปใช้กับชุดแถวทั้งชุดที่ตรงกับภาคแสดงแบบสอบถาม มิฉะนั้น คอลัมน์ทั้งหมดของรายการ SELECT ไม่รวมต้องระบุในฟังก์ชันรวม ในส่วนคำสั่ง GROUP BY. เป็นผลให้แถวแบบสอบถามเอาต์พุตทั้งหมดถูกแบ่งออกเป็นกลุ่มที่มีลักษณะเฉพาะด้วยชุดค่าผสมที่เหมือนกันในคอลัมน์เหล่านี้ หลังจากนี้ ฟังก์ชันรวมจะถูกนำไปใช้กับแต่ละกลุ่ม โปรดทราบว่าสำหรับ GROUP BY ค่า NULL ทั้งหมดจะถือว่าเท่ากันนั่นคือ เมื่อจัดกลุ่มตามฟิลด์ที่มีค่า NULL แถวดังกล่าวทั้งหมดจะรวมอยู่ในกลุ่มเดียว
ถ้า หากมีประโยค GROUP BYในส่วนคำสั่ง SELECT ไม่มีฟังก์ชันรวมจากนั้นแบบสอบถามจะส่งกลับหนึ่งแถวจากแต่ละกลุ่ม คุณลักษณะนี้พร้อมกับคีย์เวิร์ด DISTINCT สามารถใช้เพื่อกำจัดแถวที่ซ้ำกันในชุดผลลัพธ์ได้
ลองดูตัวอย่างง่ายๆ:

เลือกรุ่น COUNT(รุ่น) AS Qty_model, AVG(ราคา) AS Avg_price จากพีซี จัดกลุ่มตามรุ่น;

ในคำขอนี้ สำหรับพีซีแต่ละรุ่น จะมีการกำหนดหมายเลขและราคาเฉลี่ย แถวทั้งหมดที่มีค่าโมเดลเดียวกันจะรวมกลุ่มกัน และผลลัพธ์ของ SELECT จะคำนวณจำนวนค่าและมูลค่าราคาเฉลี่ยสำหรับแต่ละกลุ่ม ผลลัพธ์ของแบบสอบถามจะเป็นตารางต่อไปนี้:

แบบอย่าง	จำนวน_รุ่น	ราคาเฉลี่ย_ราคา
1121	3	850.0
1232	4	425.0
1233	3	843.33333333333337
1260	1	350.0

หาก SELECT มีคอลัมน์วันที่ ก็จะสามารถคำนวณตัวบ่งชี้เหล่านี้สำหรับวันที่ที่ระบุแต่ละวันได้ ในการดำเนินการนี้ คุณต้องเพิ่มวันที่เป็นคอลัมน์การจัดกลุ่ม จากนั้นฟังก์ชันรวมจะถูกคำนวณสำหรับการรวมกันของค่าแต่ละค่า (วันที่รุ่น)

มีเฉพาะหลายประการ กฎสำหรับการทำหน้าที่รวม:

• หากเป็นผลมาจากการร้องขอ ไม่ได้รับแถว(หรือมากกว่าหนึ่งแถวสำหรับกลุ่มที่กำหนด) ดังนั้นจึงไม่มีแหล่งข้อมูลสำหรับการคำนวณฟังก์ชันรวมใดๆ ในกรณีนี้ ผลลัพธ์ของฟังก์ชัน COUNT จะเป็นศูนย์ และผลลัพธ์ของฟังก์ชันอื่นๆ ทั้งหมดจะเป็น NULL
• การโต้แย้งฟังก์ชันรวม ไม่สามารถมีฟังก์ชันรวมได้(ฟังก์ชันจากฟังก์ชัน) เหล่านั้น. ในแบบสอบถามเดียว เป็นไปไม่ได้ เช่น จะได้รับค่าเฉลี่ยสูงสุด
• ผลลัพธ์ของการรันฟังก์ชัน COUNT คือ จำนวนเต็ม(จำนวนเต็ม). ฟังก์ชันการรวมอื่นๆ สืบทอดประเภทข้อมูลของค่าที่ประมวลผล
• ถ้าฟังก์ชัน SUM ให้ผลลัพธ์ที่มากกว่าค่าสูงสุดของชนิดข้อมูลที่ใช้ ข้อผิดพลาด.

ดังนั้นหากคำขอไม่มี จัดกลุ่มตามข้อ, ที่ ฟังก์ชันรวมรวมอยู่ใน ส่วนคำสั่ง SELECTจะถูกดำเนินการกับแถวแบบสอบถามผลลัพธ์ทั้งหมด หากคำขอประกอบด้วย จัดกลุ่มตามข้อแต่ละชุดแถวที่มีค่าเท่ากันของคอลัมน์หรือกลุ่มคอลัมน์ที่ระบุ จัดกลุ่มตามข้อ, รวมตัวกันเป็นกลุ่ม และ ฟังก์ชันรวมจะดำเนินการสำหรับแต่ละกลุ่มแยกกัน

มีข้อเสนอ

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

ส่วนคำสั่ง HAVING จะใช้ร่วมกับส่วนคำสั่ง GROUP BY สามารถใช้ในคำสั่ง SELECT เพื่อกรองบันทึกที่ส่งคืนโดย GROUP BY clause

มีไวยากรณ์ประโยค

รวม_ฟังก์ชันอาจเป็นฟังก์ชันเช่น SUM, COUNT, MIN หรือ MAX

ตัวอย่างการใช้ฟังก์ชัน SUM
ตัวอย่างเช่น คุณสามารถใช้ฟังก์ชัน SUM เพื่อค้นหาชื่อแผนกและยอดขาย (สำหรับแผนกที่เกี่ยวข้อง) ข้อเสนอ HAVING สามารถเลือกได้เฉพาะแผนกที่มียอดขายมากกว่า $1,000 เท่านั้น

SELECT department, SUM(sales) AS "Total sales" FROM order_details GROUP BY department HAVING SUM(sales) > 1000 ;

ตัวอย่างการใช้ฟังก์ชัน COUNT
ตัวอย่างเช่น คุณสามารถใช้ฟังก์ชัน COUNT เพื่อดึงข้อมูลชื่อแผนกและจำนวนพนักงาน (ในแผนกที่เกี่ยวข้อง) ที่มีรายได้มากกว่า 25,000 ดอลลาร์ต่อปี ข้อเสนอ HAVING จะเลือกเฉพาะแผนกที่มีพนักงานดังกล่าวมากกว่า 10 คน

ตัวอย่างการใช้ฟังก์ชัน MIN
ตัวอย่างเช่น คุณสามารถใช้ฟังก์ชัน MIN เพื่อส่งคืนชื่อแผนกและรายได้ขั้นต่ำสำหรับแผนกนั้น ข้อเสนอ HAVING จะส่งคืนเฉพาะแผนกที่มีรายได้เริ่มต้นที่ 35,000 ดอลลาร์

แผนก SELECT, MIN(เงินเดือน) AS "เงินเดือนต่ำสุด" จากพนักงาน GROUP BY department มี MIN(เงินเดือน) = 35000 ;

ตัวอย่างการใช้ฟังก์ชัน MAX
ตัวอย่างเช่น คุณยังสามารถใช้ฟังก์ชันนี้เพื่อดึงข้อมูลชื่อแผนกและรายได้สูงสุดของแผนกได้ ข้อเสนอ HAVING จะส่งคืนเฉพาะแผนกที่มีรายได้สูงสุดน้อยกว่า 50,000 ดอลลาร์เท่านั้น

แผนก SELECT, MAX(เงินเดือน) AS "เงินเดือนสูงสุด" จากพนักงาน GROUP BY department HAVING MAX(เงินเดือน)< 50000 ;

อัปเดตครั้งล่าสุด: 07/19/2017

T-SQL ใช้คำสั่ง GROUP BY และ HAVING เพื่อจัดกลุ่มข้อมูล โดยใช้ไวยากรณ์ที่เป็นทางการต่อไปนี้:

เลือกคอลัมน์จากตาราง

จัดกลุ่มตาม

GROUP BY clause กำหนดวิธีการจัดกลุ่มแถว

ตัวอย่างเช่น ลองจัดกลุ่มผลิตภัณฑ์ตามผู้ผลิต

เลือกผู้ผลิต COUNT(*) AS Modelsนับจากกลุ่มผลิตภัณฑ์ตามผู้ผลิต

คอลัมน์แรกในคำสั่ง SELECT - ผู้ผลิต แสดงถึงชื่อของกลุ่ม และคอลัมน์ที่สอง - ModelsCount แสดงถึงผลลัพธ์ของฟังก์ชัน Count ซึ่งจะคำนวณจำนวนแถวในกลุ่ม

ควรพิจารณาว่าคอลัมน์ใดๆ ที่ใช้ในคำสั่ง SELECT (ไม่นับคอลัมน์ที่เก็บผลลัพธ์ของฟังก์ชันการรวม) จะต้องระบุหลังส่วนคำสั่ง GROUP BY ตัวอย่างเช่น ในกรณีข้างต้น คอลัมน์ผู้ผลิตจะถูกระบุไว้ในทั้งส่วนคำสั่ง SELECT และ GROUP BY

และถ้าคำสั่ง SELECT เลือกหนึ่งคอลัมน์ขึ้นไปและใช้ฟังก์ชันการรวมด้วย คุณต้องใช้ GROUP BY clause ดังนั้น ตัวอย่างต่อไปนี้จะไม่ทำงานเนื่องจากไม่มีนิพจน์การจัดกลุ่ม:

เลือกผู้ผลิต COUNT(*) AS Modelsนับจากผลิตภัณฑ์

อีกตัวอย่างหนึ่ง ลองเพิ่มการจัดกลุ่มตามจำนวนผลิตภัณฑ์:

เลือกผู้ผลิต, ProductCount, COUNT(*) AS ModelsCount จากกลุ่มผลิตภัณฑ์ตามผู้ผลิต, ProductCount

GROUP BY clause สามารถจัดกลุ่มได้หลายคอลัมน์

หากคอลัมน์ที่คุณกำลังจัดกลุ่มมีค่า NULL แถวที่มีค่า NULL จะแยกกลุ่มกัน

โปรดทราบว่า GROUP BY clause ต้องอยู่หลัง WHERE clause แต่อยู่ก่อน ORDER BY clause:

เลือกผู้ผลิต COUNT(*) AS ModelsCount จากผลิตภัณฑ์ WHERE ราคา > 30000 กลุ่มตามผู้ผลิต ORDER BY ModelsCount DESC

การกรองกลุ่ม มี

ผู้ดำเนินการ มี กำหนดกลุ่มที่จะรวมอยู่ในผลลัพธ์ผลลัพธ์ นั่นคือกรองกลุ่ม

การใช้ HAVING มีความคล้ายคลึงกับการใช้ WHERE ในหลายๆ ด้าน เฉพาะ WHERE เท่านั้นที่ใช้กรองแถว HAVING ใช้เพื่อกรองกลุ่ม

ตัวอย่างเช่น เรามาค้นหากลุ่มผลิตภัณฑ์ทั้งหมดตามผู้ผลิตซึ่งมีการกำหนดรุ่นไว้มากกว่า 1 รุ่น:

เลือกผู้ผลิต COUNT(*) AS Modelsนับจากกลุ่มผลิตภัณฑ์ตามผู้ผลิต มี COUNT(*) > 1

ในกรณีนี้ ในคำสั่งเดียวเราสามารถใช้นิพจน์ WHERE และ HAVING ได้:

เลือกผู้ผลิต COUNT(*) AS ModelsCount จากผลิตภัณฑ์ โดยที่ราคา * จำนวนผลิตภัณฑ์ > 80000 กลุ่มตามผู้ผลิต มี COUNT(*) > 1

นั่นคือในกรณีนี้ แถวต่างๆ จะถูกกรองก่อน: ผลิตภัณฑ์เหล่านั้นถูกเลือกซึ่งมีต้นทุนรวมมากกว่า 80,000 จากนั้น ผลิตภัณฑ์ที่เลือกจะถูกจัดกลุ่มตามผู้ผลิต จากนั้นกลุ่มจะถูกกรอง - เลือกกลุ่มที่มีมากกว่า 1 โมเดล

หากจำเป็นต้องเรียงลำดับ นิพจน์ ORDER BY จะอยู่หลังนิพจน์ HAVING:

เลือกผู้ผลิต, COUNT(*) AS Models, SUM(ProductCount) AS หน่วยจากผลิตภัณฑ์ WHERE ราคา * ProductCount > 80000 GROUP BY Manufacturing HAVING SUM(ProductCount) > 2 ORDER BY Units DESC

ในกรณีนี้ การจัดกลุ่มจะเป็นไปตามผู้ผลิต และเลือกจำนวนรุ่นสำหรับผู้ผลิตแต่ละราย (รุ่น) และจำนวนผลิตภัณฑ์ทั้งหมดสำหรับรุ่นเหล่านี้ทั้งหมด (หน่วย) ด้วยเช่นกัน ในตอนท้าย กลุ่มจะจัดเรียงตามจำนวนผลิตภัณฑ์จากมากไปน้อย

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

เรามาจำปัญหาก่อนหน้าของเรากัน โดยที่เราคำนวณราคาเฉลี่ยของนมสำหรับเครือข่ายซูเปอร์มาร์เก็ตแห่งใดแห่งหนึ่ง อย่าเพิ่งดูที่ราคาเฉลี่ย แต่ให้ระบุเฉพาะเครือข่ายซูเปอร์มาร์เก็ตเหล่านั้นด้วย ราคาเฉลี่ยต่ำกว่า 38.

เราใช้สำหรับการกรองนี้ตามผลลัพธ์ของการดำเนินการฟังก์ชันรวม ในคำสั่ง SQL HAVING:

เลือก `shop_id`, AVG(`price`) จาก `table` จัดกลุ่มตาม `shop_id` มี AVG(`price`)< 38

เป็นผลให้แทนที่จะเป็น 4เราก็จะมีแต่เส้น 3 :

ร้านค้า_id	AVG(`ราคา`)
1	37.5
2	36.0
3	37.0

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