คอมพิวเตอร์

ฟังก์ชันสุดท้าย

นิพจน์แบบสอบถาม SQL มักต้องมีการประมวลผลข้อมูลล่วงหน้า ด้วยเหตุนี้จึงใช้ฟังก์ชันและนิพจน์พิเศษ

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

• COUNT (พารามิเตอร์ ) ส่งกลับจำนวนเร็กคอร์ดที่ระบุในพารามิเตอร์ หากคุณต้องการรับจำนวนระเบียนทั้งหมด คุณต้องระบุเครื่องหมายดอกจัน (*) เป็นพารามิเตอร์ หากคุณระบุชื่อคอลัมน์เป็นพารามิเตอร์ ฟังก์ชันจะคืนค่าจำนวนระเบียนที่คอลัมน์นี้มีค่าที่ไม่ใช่ค่า NULL หากต้องการทราบจำนวนค่าต่างๆ ในคอลัมน์ ให้นำหน้าชื่อด้วยคีย์เวิร์ด DISTINCT ตัวอย่างเช่น:

เลือก COUNT(*) จากลูกค้า;

SELECT COUNT(Order_Amount) จากลูกค้า;

SELECT COUNT(DISTINCT Order_Sum) จากลูกค้า;

การพยายามดำเนินการค้นหาต่อไปนี้จะส่งผลให้เกิดข้อความแสดงข้อผิดพลาด:

เลือกภูมิภาค , COUNT(*) จากลูกค้า ;

• SUM (พารามิเตอร์ ) ส่งกลับผลรวมของค่าของคอลัมน์ที่ระบุในพารามิเตอร์ พารามิเตอร์ยังสามารถเป็นนิพจน์ที่มีชื่อของคอลัมน์ได้อีกด้วย ตัวอย่างเช่น:

เลือกผลรวม (Order_Amount) จากลูกค้า;

คำสั่ง SQL นี้ส่งคืนตารางแบบหนึ่งคอลัมน์ แบบระเบียนเดียวที่มีผลรวมของค่าที่กำหนดไว้ทั้งหมดในคอลัมน์ Order_Amount จากตารางลูกค้า

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

เลือกผลรวม (Order_Amount*27.8) จากลูกค้า;

• AVG (พารามิเตอร์ ) ส่งกลับค่าเฉลี่ยเลขคณิตของค่าทั้งหมดของคอลัมน์ที่ระบุในพารามิเตอร์ พารามิเตอร์สามารถเป็นนิพจน์ที่มีชื่อของคอลัมน์ ตัวอย่างเช่น:

เลือก AVG (Order_Amount) จากลูกค้า;

เลือก AVG (Order_Amount*27.8) จากลูกค้า

WHERE Region<>"ตะวันตกเฉียงเหนือ";

• MAX (พารามิเตอร์ ) ส่งคืนค่าสูงสุดในคอลัมน์ที่ระบุในพารามิเตอร์ พารามิเตอร์ยังสามารถเป็นนิพจน์ที่มีชื่อของคอลัมน์ได้อีกด้วย ตัวอย่างเช่น:

SELECT MAX(Order_Amount) จากลูกค้า;

SELECT MAX(Order_Sum*27.8) จากลูกค้า

ที่ไหน ภาค<>"ตะวันตกเฉียงเหนือ";

• MIN (พารามิเตอร์ ) ส่งคืนค่าต่ำสุดในคอลัมน์ที่ระบุในพารามิเตอร์ พารามิเตอร์สามารถเป็นนิพจน์ที่มีชื่อของคอลัมน์ ตัวอย่างเช่น:

SELECT MIN(Order_Amount) จากลูกค้า;

SELECT MIN (Order_Amount*27 . 8) จากลูกค้า

ที่ไหน ภาค<>"ตะวันตกเฉียงเหนือ";

ในทางปฏิบัติ มักจะจำเป็นต้องได้รับตารางสรุปซึ่งประกอบด้วยค่ารวม ค่าเฉลี่ย ค่าสูงสุด และค่าต่ำสุดของคอลัมน์ตัวเลข เมื่อต้องการทำเช่นนี้ ใช้การจัดกลุ่ม (GROUP BY) และฟังก์ชันสรุป

เลือกภูมิภาค SUM (Order_Amount) จากลูกค้า

กลุ่มตามภูมิภาค;

ตารางผลลัพธ์สำหรับแบบสอบถามนี้มีชื่อของภูมิภาคและจำนวนคำสั่งซื้อทั้งหมด (ทั้งหมด) ของลูกค้าทั้งหมดจากภูมิภาคที่เกี่ยวข้อง (รูปที่ 5)

ตอนนี้ให้พิจารณาการสืบค้นเพื่อรับข้อมูลสรุปทั้งหมดตามภูมิภาค:

เลือกภูมิภาค SUM (Order_Amount) AVG (Order_Amount), MAX(Order_Amount),ขั้นต่ำ (Order_Amount)

จากลูกค้า

กลุ่มตามภูมิภาค;

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

ข้าว. 8. ตารางสรุปยอดสั่งซื้อตามภูมิภาค

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

AS column_header

คีย์เวิร์ด AS (as) หมายความว่าในตารางผลลัพธ์ คอลัมน์ที่เกี่ยวข้องควรมีส่วนหัวที่ระบุหลัง AS ส่วนหัวที่กำหนดเรียกอีกอย่างว่านามแฝง ตัวอย่างต่อไปนี้ (รูปที่ 9) ตั้งค่านามแฝงสำหรับคอลัมน์จากการคำนวณทั้งหมด:

เลือกภูมิภาค

SUM (Order_Amount) AS [ยอดสั่งซื้อทั้งหมด],

AVG (Order_Amount) AS [ยอดสั่งซื้อเฉลี่ย],

MAX(Order_Amount) AS สูงสุด

นาที (Order_Amount) AS ขั้นต่ำ

จากลูกค้า

กลุ่มตามภูมิภาค;

ข้าว. 9. ตารางสรุปยอดสั่งซื้อตามภูมิภาคโดยใช้ชื่อแทนคอลัมน์

นามแฝงที่ประกอบด้วยคำหลายคำที่คั่นด้วยช่องว่างจะถูกใส่ไว้ในวงเล็บเหลี่ยม

ฟังก์ชันสรุปสามารถใช้ได้ในส่วนคำสั่ง SELECT และ HAVING แต่ไม่สามารถใช้ในส่วนคำสั่ง WHERE ได้ HAVING onerator คล้ายกับ WHERE clause แต่ต่างจาก WHERE จะเลือกระเบียนในกลุ่ม

สมมติว่าคุณต้องการกำหนดว่าภูมิภาคใดมีลูกค้ามากกว่าหนึ่งราย เพื่อจุดประสงค์นี้ คุณสามารถใช้แบบสอบถามต่อไปนี้:

เลือกภูมิภาค , นับ(*)

จากลูกค้า

จัดกลุ่มตามภูมิภาค มี COUNT(*) > 1;

ฟังก์ชันการจัดการมูลค่า

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

• ฟังก์ชันสตริง
• ฟังก์ชันตัวเลข
• ฟังก์ชันวันที่-เวลา

ฟังก์ชันสตริง

ฟังก์ชันสตริงรับสตริงเป็นพารามิเตอร์และส่งคืนสตริงหรือ NULL หลังจากประมวลผล

• SUBSTRING (บรรทัด FROM start )ส่งคืนสตริงย่อยที่เกิดจากสตริงที่กำหนดเป็นพารามิเตอร์ไลน์ . สตริงย่อย เริ่มต้นด้วยอักขระที่ระบุหมายเลขลำดับในพารามิเตอร์เริ่มต้นและมีความยาวที่ระบุในพารามิเตอร์ความยาว อักขระของสตริงจะมีหมายเลขจากซ้ายไปขวา เริ่มจาก 1 วงเล็บเหลี่ยมที่นี่ระบุว่านิพจน์ที่อยู่ในวงเล็บเป็นทางเลือกเท่านั้น ถ้านิพจน์สำหรับความยาว ไม่ได้ใช้ จากนั้นเป็นสตริงย่อยของเริ่ม และต่อท้ายบรรทัดเดิม ค่าพารามิเตอร์จุดเริ่มต้นและความยาว ต้องเลือกเพื่อให้สตริงย่อยที่ค้นหาอยู่ภายในสตริงเดิม มิฉะนั้น ฟังก์ชัน SUBSTRING จะคืนค่า NULL

ตัวอย่างเช่น:

SUBSTRING ("Dear Masha!" จาก 9 FOR 4) ส่งคืน "Masha";

SUBSTRING ("Dear Masha!" จาก 9) ส่งคืน "Masha!";

SUBSTRING("Dear Masha!" จาก 15) คืนค่า NULL

คุณสามารถใช้ฟังก์ชันนี้ในคำสั่ง SQL ได้ เช่น

เลือก * จากลูกค้า

WHERE SUBSTRING (ภูมิภาคจาก 1 สำหรับ 5) = "เหนือ";

• บน(สตริง ) แปลงอักขระทั้งหมดของสตริงที่ระบุในพารามิเตอร์เป็นตัวพิมพ์ใหญ่
• ล่าง(สตริง ) แปลงอักขระทั้งหมดของสตริงที่ระบุในพารามิเตอร์เป็นตัวพิมพ์เล็ก
• TRIM (นำ | ต่อท้าย | ทั้งสอง ["ตัวละคร"] จากสตริง ) ลบอักขระนำหน้า (LEADING) ต่อท้าย (TRAILING) หรือทั้งสอง (ทั้งสอง) ออกจากสตริง โดยค่าเริ่มต้น อักขระที่จะลบคือช่องว่าง ("") จึงสามารถละเว้นได้ ส่วนใหญ่แล้ว ฟังก์ชันนี้ใช้เพื่อลบช่องว่าง

ตัวอย่างเช่น:

TRIM (ผู้นำ "" จาก "เมืองเซนต์ปีเตอร์สเบิร์ก") หมุน "เมืองเซนต์ปีเตอร์สเบิร์ก";

TRIM(TRALING " " จาก "เมืองเซนต์ปีเตอร์สเบิร์ก") ส่งคืน "เมืองเซนต์ปีเตอร์สเบิร์ก";

TRIM (ทั้ง " " จาก " เมืองเซนต์ปีเตอร์สเบิร์ก ") ส่งคืน "เมืองเซนต์ปีเตอร์สเบิร์ก";

TRIM(ทั้งจาก "เมืองเซนต์ปีเตอร์สเบิร์ก") ส่งคืน "เมืองเซนต์ปีเตอร์สเบิร์ก";

TRIM(ทั้ง "g" จาก "เมืองเซนต์ปีเตอร์สเบิร์ก") ส่งกลับ "เมืองเซนต์ปีเตอร์สเบิร์ก"

ในบรรดาฟังก์ชันเหล่านี้ ฟังก์ชันที่ใช้บ่อยที่สุดคือ SUBSTRING() และ TRIM()

ฟังก์ชันตัวเลข

ฟังก์ชันตัวเลขสามารถดึงข้อมูลไม่เพียงแต่ประเภทตัวเลขเป็นพารามิเตอร์เท่านั้น แต่ยังส่งคืนตัวเลขหรือ NULL (ค่าที่ไม่ได้กำหนด) เสมอ

• ตำแหน่ง( targetString IN สตริง) ค้นหาการเกิดขึ้นของสตริงเป้าหมายในสตริงที่ระบุ หากการค้นหาสำเร็จ ระบบจะส่งคืนหมายเลขตำแหน่งของอักขระตัวแรก มิฉะนั้น 0 หากสตริงเป้าหมายมีความยาวเป็นศูนย์ (เช่น สตริง " ") ฟังก์ชันจะคืนค่า 1 หากพารามิเตอร์อย่างน้อยหนึ่งตัวคือ NULL จากนั้น NULL จะถูกส่งกลับ อักขระของสตริงมีหมายเลขจากซ้ายไปขวา เริ่มจาก 1

ตัวอย่างเช่น:

POSITION ("e" IN "สวัสดีทุกคน") คืนค่า 5;

POSITION ("ทุกคน" ใน "สวัสดีทุกคน") ส่งกลับ 8;

POSITION ("สวัสดีทุกคน") คืนค่า 1;

POSITION("Hello!" ใน "สวัสดีทุกคน") คืนค่า 0

ในตาราง Clients (ดูรูปที่ 1) คอลัมน์ Address ประกอบด้วยชื่อเมือง รหัสไปรษณีย์ ชื่อถนน และข้อมูลอื่นๆ นอกเหนือจากนั้น คุณอาจต้องเลือกบันทึกของลูกค้าตามเมืองใดเมืองหนึ่ง ดังนั้น ถ้าคุณต้องการเลือกระเบียนที่เกี่ยวข้องกับลูกค้าที่อาศัยอยู่ในเซนต์ปีเตอร์สเบิร์ก คุณสามารถใช้นิพจน์แบบสอบถาม SQL ต่อไปนี้:

เลือก * จากลูกค้า

WHERE POSITION (" เซนต์ปีเตอร์สเบิร์ก " IN ที่อยู่ ) > 0;

โปรดทราบว่าแบบสอบถามดึงข้อมูลอย่างง่ายนี้สามารถแปลใหม่เป็น:

เลือก * จากลูกค้า

ที่อยู่เช่น "%Petersburg%";

• สารสกัด (ตัวเลือก ) แยกองค์ประกอบจากค่าวันที่-เวลาหรือจากช่วงเวลา ตัวอย่างเช่น:

สารสกัด (เดือนจากวันที่ "2005-10-25")ส่งคืน 10

• CHARACTER_LENGTH (สตริง ) ส่งกลับจำนวนอักขระในสตริง

ตัวอย่างเช่น:

CHARACTER_LENGTH("สวัสดีทุกคน") คืนค่า 11

• OCTET_LENGTH (สตริง ) ส่งกลับจำนวน octets (ไบต์) ในสตริง อักขระแต่ละตัวของอักษรละตินหรือซีริลลิกจะแสดงด้วยหนึ่งไบต์ และอักขระของอักษรจีนจะแสดงด้วยสองไบต์
• คาร์ดินัลลิตี้ (พารามิเตอร์ ) นำคอลเล็กชันขององค์ประกอบมาเป็นพารามิเตอร์และส่งคืนจำนวนองค์ประกอบในคอลเล็กชัน (หมายเลขคาร์ดินัล) ตัวอย่างเช่น คอลเล็กชันอาจเป็นอาร์เรย์หรือมัลติเซ็ตที่มีอิลิเมนต์ประเภทต่างๆ
• เอบีเอส (หมายเลข ) ส่งคืนค่าสัมบูรณ์ของตัวเลข ตัวอย่างเช่น:

ABS (-123) ส่งกลับ 123;

ABS (2 - 5) ส่งคืน 3

• MO D(หมายเลข 1 หมายเลข 2 ) คืนค่าส่วนที่เหลือของการหารจำนวนเต็มของตัวเลขแรกเป็นวินาที ตัวอย่างเช่น:

MOD (5, h) ส่งกลับ 2;

MOD (2, h) คืนค่า 0

• LN (หมายเลข ) ส่งกลับค่าลอการิทึมธรรมชาติของตัวเลข
• EXP(number) ส่งกลับ e number (ฐานของลอการิทึมธรรมชาติยกกำลังจำนวน)
• อำนาจ (หมายเลข 1 หมายเลข 2 ) ส่งคืนหมายเลข1หมายเลข2 (เลข 1 ยกกำลัง 2)
• SQRT (หมายเลข ) ส่งกลับค่ารากที่สองของตัวเลข
• ชั้น (หมายเลข ) ส่งกลับจำนวนเต็มที่มากที่สุดไม่เกินพารามิเตอร์ที่กำหนด (ปัดเศษลง) ตัวอย่างเช่น:

FLOOR (5.123) คืนค่า 5.0

• CEIL (ตัวเลข) หรือ CEILING (หมายเลข ) ส่งกลับจำนวนเต็มที่น้อยที่สุดที่ไม่น้อยกว่าพารามิเตอร์การปัดเศษที่ระบุ) ตัวอย่างเช่น:

CEIL (5.123) ส่งคืน 6.0

• WIDTH_BUCKET (number1, number2, number3, number4) ส่งคืนจำนวนเต็มในช่วงระหว่าง 0 ถึง number4 + 1 พารามิเตอร์ number2 และ number3 กำหนดส่วนตัวเลขที่แบ่งออกเป็นช่วงเท่าๆ กัน ซึ่งตัวเลขระบุโดยพารามิเตอร์หมายเลข 4 ฟังก์ชัน กำหนดจำนวนของช่วงเวลาที่ค่าตก number1 หากหมายเลข 1 อยู่นอกช่วงที่ระบุ ฟังก์ชันจะคืนค่า 0 หรือตัวเลข 4 + 1 ตัวอย่างเช่น

WIDTH_BUCKET(3.14, 0, 9, 5) คืนค่า 2

ฟังก์ชันวันที่-เวลา

ที่ ภาษา SQLมีสามฟังก์ชันที่ส่งคืนวันที่และเวลาปัจจุบัน

• วันที่ปัจจุบัน ส่งคืนวันที่ปัจจุบัน (ประเภท DATE)

ตัวอย่างเช่น: 2005-06-18.

• CURRENT_TIME (ตัวเลข ) คืนค่าเวลาปัจจุบัน (ของประเภท TIME) อาร์กิวเมนต์จำนวนเต็มระบุความแม่นยำของการแทนค่าที่สอง ตัวอย่างเช่น ด้วยค่า 2 วินาทีจะแสดงด้วยความแม่นยำในร้อย (ตัวเลขสองหลักในส่วนที่เป็นเศษส่วน):

12:39:45.27.

• CURRENT_TIMESTAMP (หมายเลข ) ส่งกลับวันที่และเวลา (ประเภท TIMESTAMP) ตัวอย่างเช่น 2005-06-18 12:39:45.27 อาร์กิวเมนต์จำนวนเต็มระบุความแม่นยำของการแทนค่าที่สอง

โปรดทราบว่าวันที่และเวลาที่ส่งคืนโดยฟังก์ชันเหล่านี้เป็นประเภทที่ไม่ใช่อักขระ หากคุณต้องการแสดงเป็นสตริงอักขระ คุณควรใช้ฟังก์ชันการแปลงประเภท CAST ()

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

นิพจน์ที่คำนวณได้

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

ตัวดำเนินการลอจิก AND, OR และ NOT และฟังก์ชัน ได้รับการตรวจสอบก่อนหน้านี้

ตัวดำเนินการเลขคณิต:

• + นอกจากนี้;
• - การลบ;
• * การคูณ;
• / แผนก.

ตัวดำเนินการสตริงตัวดำเนินการต่อสายอักขระหรือตัวดำเนินการต่อสายเพียงตัวเดียว (| |) การใช้งาน SQL บางส่วน (เช่น Microsoft Access) ใช้อักขระ (+) แทน (| |) ตัวดำเนินการต่อท้ายสตริงที่สองต่อท้ายตัวอย่างแรก นิพจน์:

"ซาช่า" | | "รัก" | | "มาช่า"

จะส่งคืนสตริง "Sasha รัก Masha" เป็นผล

เมื่อเขียนนิพจน์ คุณต้องแน่ใจว่าตัวถูกดำเนินการของตัวดำเนินการมีประเภทที่ถูกต้อง ตัวอย่างเช่น นิพจน์: 123 + "Sasha" ไม่ถูกต้อง เนื่องจากมีการใช้ตัวดำเนินการเลขคณิตเพิ่มเติมกับตัวถูกดำเนินการสตริง

นิพจน์ที่คำนวณได้สามารถปรากฏหลังจาก คำสั่ง SELECTเช่นเดียวกับในนิพจน์เงื่อนไขของ WHERE และ HAVI clause NG.

มาดูตัวอย่างกัน

สมมติว่าตารางการขายประกอบด้วยคอลัมน์ Item_Type ปริมาณ และราคา และเราต้องการทราบรายได้สำหรับสินค้าแต่ละประเภท เมื่อต้องการทำเช่นนี้ การรวมนิพจน์ ปริมาณ*ราคา ในรายการของคอลัมน์หลังคำสั่ง SELECT ก็เพียงพอแล้ว:

เลือก Item_Type ปริมาณ ราคา ปริมาณ*ราคาเช่น

รวมจากยอดขาย;

ในที่นี้ คีย์เวิร์ด AS (as) จะใช้แทนคอลัมน์ข้อมูลที่คำนวณได้

ในรูป 10 แสดงตารางการขายเดิมและตารางแบบสอบถามที่เป็นผลลัพธ์

ข้าว. 10. สืบค้นผลลัพธ์พร้อมการคำนวณรายได้สำหรับสินค้าแต่ละประเภท

หากคุณต้องการทราบรายได้รวมจากการขายผลิตภัณฑ์ทั้งหมด เพียงแค่ใช้แบบสอบถามต่อไปนี้:

เลือกผลรวม (จำนวน*ราคา) จากยอดขาย;

คิวรีต่อไปนี้ประกอบด้วยนิพจน์จากการคำนวณทั้งในรายการคอลัมน์และเงื่อนไขของส่วนคำสั่ง WHERE เขาเลือกจากตารางการขายผลิตภัณฑ์ที่มีรายได้จากการขายมากกว่า 1,000:

เลือก Product_Type ปริมาณ*ราคา AS Total

จากการขาย

WHERE ปริมาณ*ราคา > 1000;

สมมติว่าคุณต้องการรับตารางที่มีสองคอลัมน์:

สินค้าที่มีประเภทสินค้าและราคา

รวมรายได้

เนื่องจากมีการสันนิษฐานว่าในตารางการขายต้นทาง คอลัมน์ Product_Type เป็นประเภทอักขระ (ประเภท CHAR) และคอลัมน์ราคาเป็นตัวเลข จากนั้นเมื่อรวมข้อมูล (ติดกาว) จากคอลัมน์เหล่านี้ คุณต้องแปลงประเภทตัวเลขเป็นอักขระโดยใช้ CAST () การทำงาน. แบบสอบถามที่ทำงานนี้มีลักษณะดังนี้ (รูปที่ 11):

เลือก Product_type | | " (ราคา: " | | CAST(ราคา AS CHAR(5)) | | ")" AS Commodity, ปริมาณ*ราคา AS Total

จากการขาย;

ข้าว. 11. ผลการสืบค้นด้วยการรวมข้อมูลที่ต่างกันในคอลัมน์เดียว

บันทึก. ใน Microsoft Access แบบสอบถามที่คล้ายกันจะมีลักษณะดังนี้:

เลือก Product_type + " (ราคา: " + C str (ราคา) + ")" AS Item,

ปริมาณ*ตามราคารวม

จากการขาย;

นิพจน์เงื่อนไขด้วยคำสั่ง CASE

ในภาษาโปรแกรมทั่วไป มีโอเปอเรเตอร์กระโดดแบบมีเงื่อนไขที่ให้คุณควบคุมกระบวนการคำนวณได้ โดยขึ้นอยู่กับว่าตรงตามเงื่อนไขหรือไม่ ใน SQL ตัวดำเนินการนี้คือ CASE (คดี, พฤติการณ์, คดี). ใน SQL:2003 โอเปอเรเตอร์นี้จะส่งกลับค่า ดังนั้นจึงใช้ในนิพจน์ได้ มีสองรูปแบบหลักที่เราจะพูดถึงในส่วนนี้

คำสั่ง CASE ที่มีค่า

คำสั่ง CASE ที่มีค่ามีไวยากรณ์ต่อไปนี้:

CASE value_tested

เมื่อ value1 แล้ว result1

เมื่อค่า2แล้วผล2

. . .

เมื่อค่า N แล้ว ผลลัพธ์ N

ผลลัพธ์อื่นX

ในกรณีที่เมื่อ value_testedเท่ากับมูลค่า1 คำสั่ง CASE ส่งคืนค่าผลลัพธ์1 ระบุภายหลัง คำสำคัญแล้ว (นั่น) มิฉะนั้น test_value จะถูกเปรียบเทียบกับค่า2 และหากเท่ากัน จะคืนค่า result2 มิฉะนั้น ค่าที่กำลังตรวจสอบจะถูกเปรียบเทียบกับค่าถัดไปที่ระบุหลังคีย์เวิร์ด WHEN เป็นต้น หากค่าที่ตรวจสอบไม่เท่ากับค่าใดค่าหนึ่งเหล่านี้ผลลัพธ์ X ที่ระบุหลังคีย์เวิร์ด ELSE (มิฉะนั้น)

คีย์เวิร์ด ELSE เป็นทางเลือก หากไม่มีและไม่มีค่าใดที่จะเปรียบเทียบเท่ากับค่าที่กำลังทดสอบ คำสั่ง CASE จะส่งกลับค่า NULL

สมมติว่า ตามตารางลูกค้า (ดูรูปที่ 1) คุณต้องการรับตารางที่แทนที่ชื่อของภูมิภาคด้วยหมายเลขรหัส หากไม่มีขอบเขตที่แตกต่างกันมากเกินไปในตารางต้นฉบับ จะสะดวกที่จะใช้แบบสอบถามกับตัวดำเนินการ CASE เพื่อแก้ปัญหานี้:

เลือกชื่อ , ที่อยู่ ,

กรณีภูมิภาค

เมื่อ "มอสโก" แล้ว "77"

เมื่อ "ตเวียร์ภูมิภาค" แล้ว "69"

. . .

ภูมิภาคอื่น

AS รหัสภูมิภาค

จากลูกค้า;

คำสั่ง CASE พร้อมข้อความค้นหา

รูปแบบที่สองของคำสั่ง CASE เกี่ยวข้องกับการใช้เพื่อค้นหาตารางสำหรับระเบียนที่ตรงตามเงื่อนไขบางประการ:

กรณี

เมื่อเงื่อนไข1แล้วผล1

เมื่อเงื่อนไข2แล้วผล2

. . .

เมื่อเงื่อนไข N แล้ว ผลลัพธ์ N

ผลลัพธ์อื่นX

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

คีย์เวิร์ด ELSE เป็นทางเลือก หากไม่มีและไม่เป็นไปตามเงื่อนไข คำสั่ง CASE จะหมุนเวียนเป็น NULL หลังจากดำเนินการคำสั่งที่มี CASE สำหรับเร็กคอร์ดแรก คำสั่งจะย้ายไปยังเร็กคอร์ดถัดไป สิ่งนี้จะดำเนินต่อไปจนกว่าจะมีการประมวลผลระเบียนทั้งชุด

สมมติว่าในตารางหนังสือ (ชื่อ, ราคา) คอลัมน์เป็น NULL ถ้าหนังสือที่เกี่ยวข้องหมดสต็อก แบบสอบถามต่อไปนี้ส่งกลับตารางที่แสดง "สินค้าหมด" แทนที่จะเป็น NULL:

เลือกชื่อ

กรณี

เมื่อราคาเป็นโมฆะ "สินค้าหมด"

นักแสดงคนอื่น(ราคา AS CHAR(8))

AS Price

จากหนังสือ;

ค่าทั้งหมดในคอลัมน์เดียวกันต้องมีประเภทเดียวกัน ดังนั้นใน ได้รับการร้องขอใช้ฟังก์ชันการแปลงประเภท CAST เพื่อแปลงค่าตัวเลขของคอลัมน์ราคาเป็นประเภทอักขระ

โปรดทราบว่าคุณสามารถใช้รูปแบบที่สองของคำสั่ง CASE แทนรูปแบบแรกได้เสมอ:

กรณี

เมื่อ value_tested = value1 แล้ว result1

เมื่อ value_tested = value2 แล้ว result2

. . .

เมื่อ value_tested = ค่า N แล้วผล

ผลลัพธ์อื่น

ฟังก์ชัน NULLIF และ COALESCE

ในบางกรณี โดยเฉพาะอย่างยิ่งในการสืบค้นข้อมูลอัพเดต (คำสั่ง UPDATE) จะสะดวกที่จะใช้ฟังก์ชัน NULLIF () (NULL if) และ COALESCE () (รวมกัน) ที่มีขนาดกะทัดรัดมากขึ้น แทนการใช้คำสั่ง CASE ที่ยุ่งยาก

ฟังก์ชัน NULLIF ( ค่า 1 ค่า 2) คืนค่า NULL หากค่าของพารามิเตอร์ตัวแรกตรงกับค่าของพารามิเตอร์ตัวที่สอง มิฉะนั้น ค่าของพารามิเตอร์ตัวแรกจะถูกส่งกลับโดยไม่มีการเปลี่ยนแปลง นั่นคือ ถ้าค่าความเท่าเทียมกัน 1 = ค่า 2 เป็นจริง ฟังก์ชันจะส่งกลับค่า NULL มิฉะนั้น ค่า value1

ฟังก์ชันนี้เทียบเท่ากับคำสั่ง CASE ในสองรูปแบบต่อไปนี้:

• กรณีมูลค่า1

เมื่อ value2 แล้ว NULL

ค่าอื่น1

• กรณี

เมื่อ value1 = value2 แล้ว NULL

ค่าอื่น1

ฟังก์ชัน COALESCE ( ค่าที่ 1 ค่าที่ 2 ... ,ค่า N) ยอมรับรายการค่าที่สามารถกำหนดได้หรือเป็นโมฆะ (NULL) ฟังก์ชันส่งคืนค่าเฉพาะจากรายการ หรือ NULL หากค่าทั้งหมดไม่ได้กำหนดไว้

ฟังก์ชันนี้เทียบเท่ากับคำสั่ง CASE ต่อไปนี้:

กรณี

เมื่อค่า 1 ไม่เป็นค่า NULL แล้วค่า 1

เมื่อค่า 2 ไม่เป็นค่า NULL แล้วค่า 2

. . .

เมื่อค่า N ไม่เป็นค่า NULL แล้วค่า N

ELSE NULL

สมมติว่าในตารางหนังสือ (ชื่อ, ราคา) คอลัมน์ราคาจะเป็น NULL ถ้าหนังสือที่เกี่ยวข้องหมดสต็อก แบบสอบถามต่อไปนี้ส่งกลับตารางโดยแทนที่โมฆะ ข้อความ "Out of stock" แสดงขึ้น:

SELECT Title, COALESCE (CAST(ราคา AS CHAR(8)),

"สินค้าหมด") AS Price

จากหนังสือ;

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

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

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

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

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

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

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

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

GROUP BY ข้อเสนอ

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

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

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

แบบอย่าง	Qty_model	Avg_price
1121	3	850.0
1232	4	425.0
1233	3	843.33333333333337
1260	1	350.0

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

มีเฉพาะหลายอย่าง กฎสำหรับการเรียกใช้ฟังก์ชันรวม:

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

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

มีข้อเสนอ

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

ในนั้น คู่มือการเรียนคุณจะได้เรียนรู้วิธีการใช้งาน ฟังก์ชัน SUMใน SQL Server(Transact-SQL) พร้อมไวยากรณ์และตัวอย่าง

คำอธิบาย

ใน SQL Server (Transact-SQL) ฟังก์ชัน SUMส่งคืนค่ารวมของนิพจน์

ไวยากรณ์

ไวยากรณ์สำหรับฟังก์ชัน SUM ใน SQL Server (Transact-SQL) คือ:

หรือไวยากรณ์ของฟังก์ชัน SUM เมื่อจัดกลุ่มผลลัพธ์ตามคอลัมน์อย่างน้อยหนึ่งคอลัมน์:

ตัวเลือกหรือข้อโต้แย้ง

expression1 , expression2 , ... expression_n เป็นนิพจน์ที่ไม่รวมอยู่ในฟังก์ชัน SUM และต้องรวมอยู่ในส่วนคำสั่ง GROUP BY ที่ส่วนท้ายของคำสั่ง SQL
aggregate_expression คือคอลัมน์หรือนิพจน์ที่จะถูกรวมเข้าด้วยกัน
ตาราง - ตารางที่คุณต้องการรับระเบียน ต้องมีอย่างน้อยหนึ่งตารางที่ระบุไว้ในส่วนคำสั่ง FROM
เงื่อนไข WHERE - ไม่บังคับ นี่คือเงื่อนไขที่ต้องปฏิบัติตามสำหรับเรกคอร์ดที่เลือก

แอปพลิเคชัน

ฟังก์ชัน SUM สามารถใช้ในเวอร์ชันต่อไปนี้ของ SQL Server (Transact-SQL):
SQL Server vNext, SQL Server 2016, SQL Server 2015, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005

ตัวอย่างช่องเดียว

มาดูตัวอย่างฟังก์ชัน SUM ของ SQL Server เพื่อทำความเข้าใจวิธีใช้ฟังก์ชัน SUM ใน SQL Server (Transact-SQL)

ตัวอย่างเช่น คุณสามารถค้นหาจำนวนผลิตภัณฑ์ทั้งหมดที่มีจำนวนมากกว่า 10 ได้อย่างไร

ในตัวอย่างฟังก์ชัน SUM นี้ เราได้ใช้นามแฝงของนิพจน์ SUM(quantity ) "Total Quantity" เมื่อส่งคืนชุดผลลัพธ์ - "จำนวนรวม" จะแสดงเป็นชื่อฟิลด์

ตัวอย่าง DISTINCT

คุณสามารถใช้คำสั่ง DISTINCT ในฟังก์ชัน SUM ตัวอย่างเช่น คำสั่ง SQL ด้านล่างจะส่งกลับเงินเดือนรวมที่มีค่าเงินเดือนที่ไม่ซ้ำกัน โดยที่เงินเดือนน้อยกว่า $29,000 ต่อปี

หากเงินเดือนสองเงินเดือนเท่ากับ $24,000 ต่อปี จะใช้ค่าใดค่าหนึ่งเท่านั้นในฟังก์ชัน SUM

ตัวอย่างสูตร

นิพจน์ที่มีอยู่ในฟังก์ชัน SUM ไม่จำเป็นต้องเป็นฟิลด์เดียว คุณยังสามารถใช้สูตร ตัวอย่างเช่น คุณสามารถคำนวณค่าคอมมิชชั่นทั้งหมดได้

ทำธุรกรรมSQL

SELECT SUM(ยอดขาย * 0.03) เป็น "ค่าคอมมิชชั่นทั้งหมด" จากคำสั่งซื้อ;

SELECT SUM (ยอดขาย * 0.03 ) AS "ค่าคอมมิชชั่นทั้งหมด" จากคำสั่งซื้อ ;

จัดกลุ่มตามตัวอย่าง

ในบางกรณี คุณจะต้องใช้ส่วนคำสั่ง GROUP BY กับฟังก์ชัน SUM

SQL - บทที่ 11 ฟังก์ชันสรุป คอลัมน์จากการคำนวณและมุมมอง

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

• AVG() ฟังก์ชันส่งกลับค่าเฉลี่ยของคอลัมน์


• COUNT() ฟังก์ชันส่งคืนจำนวนแถวในคอลัมน์


• MAX() ฟังก์ชันส่งกลับค่าที่มากที่สุดในคอลัมน์


• MIN() ฟังก์ชันส่งกลับค่าที่น้อยที่สุดในคอลัมน์


• SUM() ฟังก์ชันส่งคืนผลรวมของค่าคอลัมน์

หนึ่งในนั้น - COUNT() - เราพบกันแล้วในบทที่ 8 ตอนนี้มาพบกับส่วนที่เหลือ สมมติว่าเราต้องการทราบราคาต่ำสุด สูงสุด และราคาเฉลี่ยของหนังสือในร้านของเรา จากนั้นจากตาราง ราคา (ราคา) คุณต้องใช้ค่าต่ำสุด สูงสุด และค่าเฉลี่ยสำหรับคอลัมน์ราคา คำขอนั้นง่าย:

SELECT MIN(ราคา), MAX(ราคา), AVG(ราคา) จากราคา;

ตอนนี้ เราต้องการทราบราคาที่ซัพพลายเออร์ "โรงพิมพ์" (id=2) นำสินค้ามาให้เรา การร้องขอดังกล่าวไม่ใช่เรื่องง่าย ลองคิดดูว่าจะทำอย่างไร:

1. ก่อนอื่น คุณต้องเลือกตัวระบุ (id_incoming) ของการส่งมอบที่ดำเนินการโดยซัพพลายเออร์ "โรงพิมพ์" (id=2) จากตาราง การส่งมอบ (ขาเข้า):

2. ตอนนี้ จากตาราง Supply Journal (magazine_incoming) คุณต้องเลือกสินค้า (id_product) และปริมาณ (จำนวน) ซึ่งดำเนินการในการส่งมอบที่พบในวรรค 1 นั่นคือคำขอจากจุดที่ 1 ซ้อนกัน:

3. ตอนนี้ เราต้องเพิ่มราคาสำหรับสินค้าที่พบลงในตารางผลลัพธ์ ซึ่งจัดเก็บไว้ในตารางราคา นั่นคือเราต้องเข้าร่วมตาราง Supply Journal (magazine_incoming) และราคา (prices) โดยคอลัมน์ id_product:

4. ในตารางผลลัพธ์ คอลัมน์ Sum หายไปอย่างชัดเจน นั่นคือ คอลัมน์จากการคำนวณ. ความสามารถในการสร้างคอลัมน์ดังกล่าวมีอยู่ใน MySQL ในการดำเนินการนี้ คุณเพียงแค่ระบุชื่อคอลัมน์จากการคำนวณในคิวรีและสิ่งที่ควรคำนวณในคิวรี ในตัวอย่างของเรา คอลัมน์ดังกล่าวจะเรียกว่า summa และจะคำนวณผลคูณของคอลัมน์ปริมาณและราคา ชื่อของคอลัมน์ใหม่คั่นด้วยคำว่า AS:

SELECT Magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity*prices.price AS summa FROM magazine_incoming, prices WHERE magazine_incoming.id_product= prices.id_product AND id_incoming= (เลือก id_incoming FROM ขาเข้า WHERE id_vendor=2);

5. เยี่ยมมาก สิ่งที่เหลืออยู่สำหรับเราคือการสรุปคอลัมน์รวมและในที่สุดก็พบว่าซัพพลายเออร์ "โรงพิมพ์" นำสินค้ามาให้เรามากน้อยเพียงใด ไวยากรณ์สำหรับการใช้ฟังก์ชัน SUM() มีดังนี้:

เลือก SUM(column_name) จาก table_name;

เราทราบชื่อคอลัมน์ - summa แต่เราไม่มีชื่อของตาราง เนื่องจากเป็นผลจากการสืบค้น จะทำอย่างไร? สำหรับกรณีดังกล่าว MySQL มีมุมมอง มุมมองคือคิวรีแบบใช้เลือกข้อมูลที่มีชื่อเฉพาะและสามารถเก็บไว้ในฐานข้อมูลเพื่อใช้ในภายหลัง

ไวยากรณ์สำหรับการสร้างมุมมองมีดังนี้:

สร้าง VIEW view_name AS แบบสอบถาม;

มาบันทึกคำขอของเราเป็นมุมมองที่ชื่อ report_vendor:

สร้างมุมมองรายงาน_vendor AS SELECT Magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity*prices.price AS summa FROM magazine_incoming, prices WHERE magazine_incoming.id_product= prices.id_product AND id_incoming= (เลือก id_incoming FROM ขาเข้า =2 );

6. ตอนนี้คุณสามารถใช้ฟังก์ชัน SUM() สุดท้ายได้:

เลือก SUM(ผลรวม) จาก report_vendor;

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

เขตข้อมูลจากการคำนวณ (คอลัมน์)

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

เลือก col_name_1, col_name_2, col_name_1*col_name_2 AS คำนวณ_column_name จาก table_name;

ความแตกต่างที่สองคือคีย์เวิร์ด AS เราใช้เพื่อตั้งชื่อคอลัมน์จากการคำนวณ อันที่จริง คีย์เวิร์ดนี้ตั้งค่านามแฝงสำหรับคอลัมน์ใดๆ ทำไมจึงจำเป็น? สำหรับการลดโค้ดและความสามารถในการอ่าน ตัวอย่างเช่น มุมมองของเราอาจมีลักษณะดังนี้:

สร้าง VIEW report_vendor AS SELECT A.id_product, A.quantity, B.price, A.quantity*B.price AS summa FROM magazine_incoming AS A, prices AS B WHERE A.id_product= B.id_product AND id_incoming= (SELECT id_incoming FROM incoming) โดยที่ id_vendor=2);

ยอมรับว่าสั้นและชัดเจนกว่านี้มาก

การเป็นตัวแทน

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

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

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

ฟังก์ชัน SQL SUM

ฟังก์ชัน SQL SUM จะคืนค่าผลรวมของค่าของคอลัมน์ในตารางฐานข้อมูล ใช้ได้กับคอลัมน์ที่มีค่าเป็นตัวเลขเท่านั้น แบบสอบถาม SQLเพื่อให้ได้ผลรวมเริ่มต้นดังนี้:

เลือกผลรวม (COLUMNAME) ...

นิพจน์นี้ตามด้วย FROM (TABLE_NAME) และจากนั้นสามารถระบุเงื่อนไขได้โดยใช้ส่วนคำสั่ง WHERE นอกจากนี้ DISTINCT สามารถนำหน้าชื่อคอลัมน์เพื่อระบุว่าจะพิจารณาเฉพาะค่าที่ไม่ซ้ำเท่านั้น โดยค่าเริ่มต้นค่าทั้งหมดจะถูกนำมาพิจารณา (สำหรับสิ่งนี้คุณสามารถระบุได้โดยเฉพาะไม่ใช่ DISTINCT แต่ทั้งหมด แต่คำว่า ALL เป็นตัวเลือก)

หากคุณต้องการเรียกใช้แบบสอบถามฐานข้อมูลจากบทเรียนนี้บน MS SQL Server แต่ไม่ได้ติดตั้ง DBMS บนคอมพิวเตอร์ของคุณ คุณสามารถติดตั้งโดยใช้คำแนะนำในลิงก์นี้ .

อันดับแรก เราจะทำงานกับฐานข้อมูลของบริษัท - Company1. สคริปต์สำหรับสร้างฐานข้อมูล ตาราง และกรอกข้อมูลตารางอยู่ในไฟล์ที่ลิงค์นี้ .

ตัวอย่าง 1มีฐานข้อมูลของบริษัทพร้อมข้อมูลเกี่ยวกับแผนกและพนักงาน ตารางพนักงานยังมีคอลัมน์ที่มีข้อมูลเงินเดือนพนักงานอีกด้วย การเลือกจากตารางมีรูปแบบดังต่อไปนี้ (หากต้องการขยายรูปภาพ ให้คลิกปุ่มซ้ายของเมาส์):

เพื่อให้ได้ผลรวมของเงินเดือนทั้งหมด เราใช้แบบสอบถามต่อไปนี้ (ใน MS SQL Server - ด้วยคำนำหน้า USE company1;):

เลือก SUM (เงินเดือน) จากพนักงาน

ข้อความค้นหานี้จะคืนค่า 287664.63

และตอนนี้ . ในแบบฝึกหัด เราเริ่มทำให้งานซับซ้อนขึ้นแล้ว โดยนำพวกเขาเข้าใกล้สิ่งที่พบในทางปฏิบัติมากขึ้น

ฟังก์ชัน SQL MIN

ฟังก์ชัน SQL MIN ยังทำงานในคอลัมน์ที่มีค่าเป็นตัวเลขและคืนค่าต่ำสุดของค่าทั้งหมดในคอลัมน์ ฟังก์ชันนี้มีไวยากรณ์คล้ายกับฟังก์ชัน SUM

ตัวอย่างที่ 3ฐานข้อมูลและตารางจะเหมือนกับในตัวอย่างที่ 1

จำเป็นต้องค้นหาค่าจ้างขั้นต่ำของพนักงานในแผนกหมายเลข 42 ในการทำเช่นนี้เราเขียนแบบสอบถามต่อไปนี้ (บน MS SQL Server - ด้วยคำนำหน้า USE company1;):

ข้อความค้นหาจะส่งคืนค่า 10505.90

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

ตัวอย่างที่ 4ตารางองค์กรถูกเพิ่มลงในตารางพนักงาน ซึ่งมีข้อมูลเกี่ยวกับแผนกของบริษัท แสดงจำนวนปีขั้นต่ำที่พนักงานคนเดียวทำงานในแผนกที่ตั้งอยู่ในเมืองบอสตัน

ฟังก์ชัน SQL MAX

ฟังก์ชัน SQL MAX ทำงานคล้ายกันและมีรูปแบบที่คล้ายกัน ซึ่งใช้เมื่อคุณต้องการกำหนดค่าสูงสุดจากค่าทั้งหมดของคอลัมน์

ตัวอย่างที่ 5

จำเป็นต้องค้นหาเงินเดือนสูงสุดของพนักงานในแผนกหมายเลข 42 ในการทำเช่นนี้เราเขียนแบบสอบถามต่อไปนี้ (บน MS SQL Server - ด้วยคำนำหน้า USE company1;):

แบบสอบถามจะส่งคืนค่า18352.80

ได้เวลา แบบฝึกหัดเพื่อการตัดสินใจ.

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

ฟังก์ชัน SQL AVG

สิ่งที่กล่าวเกี่ยวกับไวยากรณ์สำหรับฟังก์ชันที่อธิบายไว้ก่อนหน้านี้ก็เป็นความจริงเกี่ยวกับฟังก์ชัน SQL AVG ด้วยเช่นกัน ฟังก์ชันนี้ส่งคืนค่าเฉลี่ยของค่าทั้งหมดในคอลัมน์

ตัวอย่าง 7ฐานข้อมูลและตารางจะเหมือนกับในตัวอย่างก่อนหน้า

ให้จำเป็นต้องค้นหาความยาวเฉลี่ยของการบริการของพนักงานของแผนกหมายเลข 42 ในการทำเช่นนี้เราเขียนแบบสอบถามต่อไปนี้ (บน MS SQL Server - ด้วยคำนำหน้า USE company1;):

ผลลัพธ์จะเป็น 6.33

ตัวอย่างที่ 8เราทำงานกับโต๊ะเดียว - พนักงาน แสดงเงินเดือนเฉลี่ยของพนักงานที่มีประสบการณ์ตั้งแต่ 4 ถึง 6 ปี

ฟังก์ชัน SQL COUNT

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

ตัวอย่างที่ 9ฐานข้อมูลและตารางจะเหมือนกับในตัวอย่างก่อนหน้า

คุณต้องการทราบจำนวนพนักงานทั้งหมดที่ได้รับค่าคอมมิชชั่น จำนวนพนักงานที่มีค่าคอลัมน์ Comm ไม่ใช่ NULL จะส่งคืนแบบสอบถามต่อไปนี้ (บน MS SQL Server - ด้วย USE company1; prepending construct):

SELECT COUNT (Comm) จากเจ้าหน้าที่

ผลลัพธ์จะเป็นค่า 11

ตัวอย่าง 10ฐานข้อมูลและตารางจะเหมือนกับในตัวอย่างก่อนหน้า

หากคุณต้องการค้นหาจำนวนระเบียนทั้งหมดในตาราง เราใช้การสืบค้นที่มีเครื่องหมายดอกจันเป็นอาร์กิวเมนต์ของฟังก์ชัน COUNT (บน MS SQL Server - พร้อมคำนำหน้า USE company1;):

SELECT COUNT (*) จากเจ้าหน้าที่

ผลลัพธ์จะเป็นค่า 17

ต่อไป การออกกำลังกายเพื่อการตัดสินใจคุณต้องใช้แบบสอบถามย่อย

ตัวอย่างที่ 11เราทำงานกับโต๊ะเดียว - พนักงาน แสดงจำนวนพนักงานในแผนก Plains

รวมฟังก์ชันด้วย SQL GROUP BY

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

เราจะทำงานกับฐานข้อมูล "พอร์ทัลประกาศ 1" สคริปต์สำหรับสร้างฐานข้อมูล ตาราง และการกรอกข้อมูลในตารางข้อมูลอยู่ในไฟล์ที่ลิงค์นี้ .

ตัวอย่างที่ 12จึงมีฐานข้อมูลของพอร์ทัลโฆษณา มีตารางโฆษณาที่มีข้อมูลเกี่ยวกับโฆษณาที่ส่งสำหรับสัปดาห์ คอลัมน์หมวดหมู่มีข้อมูลเกี่ยวกับหมวดหมู่โฆษณาขนาดใหญ่ (เช่น อสังหาริมทรัพย์) และคอลัมน์ชิ้นส่วนมีข้อมูลเกี่ยวกับชิ้นส่วนขนาดเล็กที่รวมอยู่ในหมวดหมู่ (เช่น พาร์ทเมนท์และวิลล่าเป็นส่วนหนึ่งของหมวดหมู่อสังหาริมทรัพย์) คอลัมน์หน่วยประกอบด้วยข้อมูลเกี่ยวกับจำนวนโฆษณาที่ส่ง และคอลัมน์เงินประกอบด้วยจำนวนเงินที่ได้รับจากการส่งโฆษณา

หมวดหมู่	ส่วนหนึ่ง	หน่วย	เงิน
ขนส่ง	ยานยนต์	110	17600
อสังหาริมทรัพย์	อพาร์ตเมนต์	89	18690
อสังหาริมทรัพย์	กระท่อม	57	11970
ขนส่ง	รถจักรยานยนต์	131	20960
วัสดุก่อสร้าง	บอร์ด	68	7140
วิศวกรรมไฟฟ้า	ทีวี	127	8255
วิศวกรรมไฟฟ้า	ตู้เย็น	137	8905
วัสดุก่อสร้าง	Regips	112	11760
เวลาว่าง	หนังสือ	96	6240
อสังหาริมทรัพย์	บ้าน	47	9870
เวลาว่าง	ดนตรี	117	7605
เวลาว่าง	เกม	41	2665

ใช้คำสั่ง SQL GROUP BY หาจำนวนเงินที่สร้างขึ้นโดยการส่งโฆษณาในแต่ละหมวดหมู่ เราเขียนแบบสอบถามต่อไปนี้ (บน MS SQL Server - ด้วยคำนำหน้า USE adportal1;):

เลือกหมวดหมู่ SUM (เงิน) เป็นเงินจากกลุ่มโฆษณาตามประเภท

ตัวอย่างที่ 13ฐานข้อมูลและตารางจะเหมือนกับในตัวอย่างก่อนหน้า

ใช้คำสั่ง SQL GROUP BY ค้นหาว่าส่วนใดของแต่ละหมวดหมู่ถูกจัดเก็บ จำนวนมากที่สุดโฆษณา เราเขียนแบบสอบถามต่อไปนี้ (บน MS SQL Server - ด้วยคำนำหน้า USE adportal1;):

SELECT หมวดหมู่, ส่วน, MAX (หน่วย) AS สูงสุดจากกลุ่มโฆษณา BY Category

ผลลัพธ์จะเป็นตารางต่อไปนี้:

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

ฐานข้อมูลเชิงสัมพันธ์และภาษา SQL