การสร้างเขตข้อมูลจากการคำนวณ
โดยทั่วไปเพื่อสร้าง ฟิลด์คำนวณ (ได้มา)รายการ SELECT ต้องมีนิพจน์ SQL บางส่วน นิพจน์เหล่านี้ใช้การดำเนินการทางคณิตศาสตร์ของการบวก ลบ การคูณ การหาร รวมถึงฟังก์ชัน SQL ที่มีอยู่แล้วภายใน คุณสามารถระบุชื่อของคอลัมน์ (เขตข้อมูล) ของตารางหรือแบบสอบถามได้ แต่ใช้เฉพาะชื่อคอลัมน์ของตารางหรือแบบสอบถามที่แสดงอยู่ในรายการส่วนคำสั่ง FROM ของคำสั่งที่เกี่ยวข้องเท่านั้น เมื่อสร้างนิพจน์ที่ซับซ้อน อาจจำเป็นต้องใช้วงเล็บ
มาตรฐาน SQL อนุญาตให้คุณระบุชื่อของคอลัมน์ของตารางผลลัพธ์ซึ่งใช้ AS clause ได้อย่างชัดเจน
เลือก Product.Name, Product.Price, Deal.Quantity, Product.Price*Deal.Quantity AS Cost จาก Product INNER JOIN Deal ON Product.ProductCode=Deal.ProductCode ตัวอย่างที่ 6.1
การคำนวณต้นทุนรวมสำหรับแต่ละธุรกรรมตัวอย่างที่ 6.2
รับรายชื่อบริษัทที่ระบุนามสกุลและชื่อย่อของลูกค้า เลือกบริษัท นามสกุล+""+ ซ้าย(ชื่อจริง,1)+"."+ซ้าย(ชื่อกลาง,1)+""เป็นชื่อเต็มจากลูกค้า
ตัวอย่างที่ 6.2
การได้รับรายชื่อบริษัทที่ระบุนามสกุลและชื่อย่อของลูกค้าคำขอใช้ฟังก์ชันด้านซ้ายในตัว ซึ่งช่วยให้คุณตัดอักขระหนึ่งตัวจากด้านซ้ายในตัวแปรข้อความในกรณีนี้
ตัวอย่างที่ 6.3 รับรายการผลิตภัณฑ์ที่ระบุปีและเดือนที่ขาย
เลือก Product.Name, Year(Transaction.Date) AS Year, Month(Transaction.Date) AS Month จาก Product INNER JOIN Transaction ON Product.ProductCode=Transaction.ProductCode
ตัวอย่างที่ 6.3
รับรายการสินค้าระบุปีและเดือนที่จำหน่าย แบบสอบถามใช้ฟังก์ชันที่มีอยู่แล้วภายในปีและเดือนเพื่อแยกปีและเดือนออกจากวันที่การใช้ฟังก์ชันสรุป
โดยการใช้ ฟังก์ชันสุดท้าย (รวม):
- จำนวน (นิพจน์) - กำหนดจำนวนบันทึกในชุดเอาต์พุตของแบบสอบถาม SQL
- ต่ำสุด/สูงสุด (นิพจน์) - กำหนดชุดค่าที่เล็กที่สุดและใหญ่ที่สุดในฟิลด์คำขอเฉพาะ
- Avg (นิพจน์) - ฟังก์ชั่นนี้ช่วยให้คุณสามารถคำนวณค่าเฉลี่ยของชุดค่าที่จัดเก็บไว้ในฟิลด์เฉพาะของบันทึกที่เลือกโดยแบบสอบถาม เป็นค่าเฉลี่ยเลขคณิตเช่น ผลรวมของค่าหารด้วยตัวเลข
- ผลรวม (นิพจน์) - คำนวณผลรวมของชุดค่าที่มีอยู่ในฟิลด์เฉพาะของระเบียนที่เลือกโดยแบบสอบถาม
ส่วนใหญ่แล้ว ชื่อคอลัมน์จะถูกใช้เป็นนิพจน์ นิพจน์สามารถคำนวณได้โดยใช้ค่าของหลายตาราง
ฟังก์ชันทั้งหมดนี้ทำงานกับค่าในคอลัมน์เดียวของตารางหรือในนิพจน์ทางคณิตศาสตร์และส่งกลับค่าเดียว ฟังก์ชัน COUNT , MIN และ MAX ใช้กับทั้งช่องตัวเลขและไม่ใช่ตัวเลข ในขณะที่ฟังก์ชัน SUM และ AVG ใช้ได้กับช่องตัวเลขเท่านั้น ยกเว้น COUNT(*) เมื่อคำนวณผลลัพธ์ของฟังก์ชันใด ๆ ค่า Null ทั้งหมดจะถูกตัดออกก่อนจากนั้นจึงใช้การดำเนินการที่จำเป็นกับค่าคอลัมน์เฉพาะที่เหลือเท่านั้น ตัวเลือก COUNT(*) คือการใช้งานพิเศษของฟังก์ชัน COUNT โดยมีวัตถุประสงค์เพื่อนับแถวทั้งหมดในตารางผลลัพธ์ ไม่ว่าจะมีค่าว่าง ซ้ำกัน หรือค่าอื่นใดก็ตาม
หากคุณต้องการกำจัดค่าที่ซ้ำกันก่อนที่จะใช้ฟังก์ชันทั่วไป คุณต้องนำหน้าชื่อคอลัมน์ในคำจำกัดความของฟังก์ชันด้วยคีย์เวิร์ด DISTINCT มันไม่มีความหมายสำหรับฟังก์ชัน MIN และ MAX แต่การใช้งานอาจส่งผลต่อผลลัพธ์ของฟังก์ชัน SUM และ AVG ดังนั้นคุณจึงต้องพิจารณาว่าควรจะมีอยู่ในแต่ละกรณีหรือไม่ นอกจากนี้ คีย์เวิร์ด DISTINCT สามารถระบุได้เพียงครั้งเดียวในการสืบค้นใดๆ
มันสำคัญมากที่จะต้องทราบว่า ฟังก์ชันสุดท้าย (รวม)สามารถใช้ในรายการในส่วนคำสั่ง SELECT และเป็นส่วนหนึ่งของส่วนคำสั่ง HAVING เท่านั้น ในกรณีอื่นๆ ทั้งหมดนี้เป็นสิ่งที่ยอมรับไม่ได้ หากรายการในส่วนคำสั่ง SELECT มี ฟังก์ชันสุดท้าย (รวม)และข้อความคิวรีไม่มี GROUP BY clause ซึ่งจัดให้มีการรวมข้อมูลเข้าเป็นกลุ่ม ดังนั้นองค์ประกอบรายการใดของ SELECT clause ไม่สามารถรวมการอ้างอิงใดๆ ไปยังฟิลด์ได้ ยกเว้นในสถานการณ์ที่ฟิลด์ทำหน้าที่เป็นอาร์กิวเมนต์ ฟังก์ชั่นสุดท้าย.
ตัวอย่างที่ 6.4กำหนดชื่อตามตัวอักษรแรกของผลิตภัณฑ์
เลือก Min(Product.Name) AS Min_Name จากผลิตภัณฑ์ ตัวอย่างที่ 6.4
การกำหนดชื่อตามตัวอักษรแรกของผลิตภัณฑ์ตัวอย่างที่ 6.5
กำหนดจำนวนธุรกรรม SELECT Count(*) AS Number_of_deals จากดีล
ตัวอย่างที่ 6.5กำหนดจำนวนธุรกรรม
ตัวอย่างที่ 6.6 กำหนดจำนวนรวมของสินค้าที่ขาย
เลือกผลรวม (Deal.Quantity) เป็น Item_Quantity จาก Dealตัวอย่างที่ 6.6
การกำหนดปริมาณรวมของสินค้าที่ขาย ตัวอย่างที่ 6.7 กำหนดราคาเฉลี่ยของสินค้าที่ขายเลือก Avg(Product.Price) AS Avg_price จาก Product INNER JOIN Deal ON Product.ProductCode=Deal.ProductCode;
ตัวอย่างที่ 6.7 คำนิยาม
ราคาเฉลี่ย
ขายสินค้า ฟังก์ชันสุดท้าย (รวม)เลือกผลรวม (Product.Price*Transaction.Quantity) เป็นต้นทุนจากธุรกรรมเข้าร่วมภายในของผลิตภัณฑ์บน Product.ProductCode=Transaction.ProductCode
ตัวอย่างที่ 6.8 การคำนวณต้นทุนรวมของสินค้าที่ขายจัดกลุ่มตามข้อ
คิวรีมักต้องมีการสร้างผลรวมย่อย ซึ่งโดยปกติจะระบุด้วยลักษณะของวลี "สำหรับแต่ละ..." ในคิวรี GROUP BY clause ถูกใช้ในคำสั่ง SELECT เพื่อจุดประสงค์นี้ คิวรีที่มี GROUP BY เรียกว่าคิวรีแบบจัดกลุ่ม เนื่องจากจะจัดกลุ่มข้อมูลที่ส่งกลับโดยการดำเนินการ SELECT จากนั้นจะสร้างแถวสรุปเดี่ยวสำหรับแต่ละกลุ่ม มาตรฐาน SQL กำหนดให้ส่วนคำสั่ง SELECT และส่วนคำสั่ง GROUP BY มีความสัมพันธ์กันอย่างใกล้ชิด เมื่อคำสั่ง SELECT มีส่วนคำสั่ง GROUP BY แต่ละองค์ประกอบรายการในส่วนคำสั่ง SELECT จะต้องมีค่าเดียวสำหรับทั้งกลุ่ม นอกจากนี้ ส่วนคำสั่ง SELECT สามารถรวมองค์ประกอบประเภทต่อไปนี้เท่านั้น: ชื่อฟิลด์
ค่าคงที่และนิพจน์ที่มีการรวมกันขององค์ประกอบที่แสดงไว้ข้างต้น
ชื่อฟิลด์ทั้งหมดที่อยู่ในส่วนคำสั่ง SELECT จะต้องปรากฏในส่วนคำสั่ง GROUP BY ด้วย - เว้นแต่จะใช้ชื่อคอลัมน์ในคำนวณปริมาณการซื้อเฉลี่ยของลูกค้าแต่ละราย
เลือก Client.LastName, Avg(Transaction.Quantity) AS Average_Quantity จากลูกค้าเข้าร่วมภายใน ซื้อขาย Client.ClientCode=Transaction.ClientCode GROUP BY Client.LastName ตัวอย่างที่ 6.9
คำนวณปริมาณการซื้อเฉลี่ยของลูกค้าแต่ละราย วลี “ลูกค้าทุกคน” สะท้อนให้เห็นในการสืบค้น SQL ในรูปแบบของประโยค.
จัดกลุ่มตามลูกค้านามสกุลตัวอย่างที่ 6.10
กำหนดจำนวนสินค้าแต่ละรายการที่ขายได้ เลือก Product.Name, Sum(Product.Price*Transaction.Quantity) เป็นต้นทุนจาก Product INNER JOIN Deal ON Product.ProductCode=Transaction.ProductCode GROUP BY Product.Name
ตัวอย่างที่ 6.10 การกำหนดจำนวนที่จะขายผลิตภัณฑ์แต่ละรายการ
เลือก Client.Company นับ (Transaction.TransactionCode) AS Number_of_transactions จากไคลเอ็นต์เข้าร่วมธุรกรรมบน Client.ClientCode=Transaction.ClientCode GROUP BY Client.Company ตัวอย่างที่ 6.11
การนับจำนวนธุรกรรมที่แต่ละบริษัททำเลือก Customer.Company, Sum(Transaction.Quantity) AS Total_Quantity, Sum(Product.Price*Transaction.Quantity) เป็นต้นทุนจาก Product INNER JOIN (ลูกค้าเข้าร่วมธุรกรรมภายในบน Customer.ClientCode=Transaction.CustomerCode) บน Product.ProductCode=Transaction .รหัสสินค้า GROUP BY Client.บริษัท
ตัวอย่างที่ 6.12 การคำนวณปริมาณรวมของสินค้าที่ซื้อสำหรับแต่ละบริษัทและต้นทุน
ตัวอย่างที่ 6.13กำหนดต้นทุนรวมของแต่ละผลิตภัณฑ์ในแต่ละเดือน
เลือก Product.Name, Month(Transaction.Date) AS Month, Sum(Product.Price*Transaction.Quantity) AS Cost from Product INNER JOIN Transaction ON Product.ProductCode=Transaction.ProductCode GROUP BY Product.Name, Month(Transaction.Date) ) ตัวอย่างที่ 6.13
การกำหนดต้นทุนรวมของแต่ละผลิตภัณฑ์ในแต่ละเดือน
ตัวอย่างที่ 6.14 กำหนดต้นทุนรวมของผลิตภัณฑ์ชั้นนำแต่ละรายการในแต่ละเดือนเลือก Product.Name, Month(Transaction.Date) AS Month, Sum(Product.Price*Transaction.Quantity) AS Cost from Product INNER JOIN Transaction ON Product.ProductCode=Transaction.ProductCode WHERE Product.Grade="First" GROUP BY Product .ชื่อ, เดือน(รายการ.วันที่)
ตัวอย่างที่ 6.14
- การกำหนดต้นทุนรวมของผลิตภัณฑ์ชั้นหนึ่งแต่ละรายการในแต่ละเดือน
- WHERE ไม่รวมบันทึกที่ไม่ตรงตามเงื่อนไขจากการคำนวณค่ารวมโดยการจัดกลุ่ม
- ไม่สามารถระบุฟังก์ชันการรวมในเงื่อนไขการค้นหา WHERE
ตัวอย่างที่ 6.15ระบุบริษัทที่มีจำนวนธุรกรรมทั้งหมดเกินสามรายการ
เลือก Client.Company, Count(Trade.Quantity) AS Number_of_deals จากลูกค้าเข้าร่วมภายใน ซื้อขายกับ Client.ClientCode=Transaction.ClientCode GROUP BY Client.Company HAVING Count(Transaction.Quantity)>3 ตัวอย่างที่ 6.15
การระบุบริษัทที่มีจำนวนธุรกรรมทั้งหมดเกินสามรายการตัวอย่างที่ 6.16
แสดงรายการสินค้าที่ขายมากกว่า 10,000 รูเบิล เลือก Product.Name, Sum(Product.Price*Deal.Quantity) เป็นต้นทุนจาก Product INNER JOIN Deal ON Product.ProductCode=Transaction.ProductCode GROUP BY Product.Name HAVING Sum(Product.Price*Deal.Quantity)>10000
ตัวอย่างที่ 6.16แสดงรายการสินค้าที่ขายมากกว่า 10,000 รูเบิล
ตัวอย่างที่ 6.17 แสดงรายการสินค้าที่ขายเกิน 10,000 รายการ โดยไม่ระบุจำนวน
เลือก Product.Name จาก Product INNER JOIN Deal ON Product.ProductCode=Deal.ProductCode GROUP BY Product.Name HAVING Sum(Product.Price*Transaction.Quantity)>10000 ตัวอย่างที่ 6.17แสดงรายการสินค้าที่ขายเกิน 10,000 รายการ โดยไม่ระบุจำนวน ในเรื่องนี้หนังสือเรียน คุณจะได้เรียนรู้วิธีการใช้งานฟังก์ชันนับ
วี
เซิร์ฟเวอร์ SQL (Transact-SQL) พร้อมไวยากรณ์และตัวอย่างคำอธิบาย
ใน SQL Server (Transact-SQL)
ฟังก์ชันนับ
ส่งกลับจำนวนแถวของเขตข้อมูลหรือนิพจน์ในชุดผลลัพธ์
ไวยากรณ์
ไวยากรณ์สำหรับฟังก์ชัน COUNT ใน SQL Server (Transact-SQL) คือ:
หรือไวยากรณ์สำหรับฟังก์ชัน COUNT เมื่อจัดกลุ่มผลลัพธ์ของคอลัมน์ตั้งแต่หนึ่งคอลัมน์ขึ้นไปคือ:
พารามิเตอร์หรืออาร์กิวเมนต์
expression1 , expression2 , … expression_n
นิพจน์ที่ไม่รวมอยู่ในฟังก์ชัน COUNT และต้องรวมอยู่ใน GROUP BY clause ที่ส่วนท้ายของคำสั่ง SQL
integrated_expression คือคอลัมน์หรือนิพจน์ที่จะนับค่าที่ไม่ใช่ NULL
ตาราง - ตารางที่คุณต้องการรับบันทึก ต้องมีอย่างน้อยหนึ่งตารางที่แสดงอยู่ในส่วนคำสั่ง FROM
เงื่อนไข WHERE - ไม่จำเป็น นี่คือเงื่อนไขที่ต้องปฏิบัติตามสำหรับเรกคอร์ดที่เลือก รวมถึงค่าที่ไม่ใช่ NULLไม่ใช่ทุกคนที่เข้าใจสิ่งนี้ แต่ฟังก์ชัน COUNT จะนับเฉพาะระเบียนเหล่านั้นโดยที่ค่าของนิพจน์ใน COUNT (aggregate_expression) ไม่ใช่ NULL เมื่อนิพจน์มีค่า NULL จะไม่รวมอยู่ในตัวนับ COUNT
ตัวอย่างเช่น หากคุณมีตารางต่อไปนี้ที่เรียกว่าตลาด:
นี้ ตัวอย่าง COUNTจะส่งกลับ 3 เนื่องจากค่า market_id ทั้งหมดในชุดผลลัพธ์แบบสอบถามไม่เป็นค่าว่าง
อย่างไรก็ตาม หากคุณรันคำสั่ง SELECT ต่อไปนี้ ซึ่งใช้ฟังก์ชัน COUNT:
ธุรกรรม-SQL
SELECT COUNT(filials) จากตลาด; --ผลลัพธ์: 1
ตัวอย่าง COUNT นี้จะส่งคืนค่า 1 เท่านั้น เนื่องจากมีเพียงค่าลูกกตัญญูเพียงค่าเดียวในชุดผลลัพธ์การสืบค้นที่ไม่เป็นค่าว่าง นี่จะเป็นบรรทัดแรกที่ระบุว่า filials = "ใช่" นี่เป็นแถวเดียวที่รวมอยู่ในการคำนวณของฟังก์ชัน COUNT
แอปพลิเคชัน
ฟังก์ชัน COUNT สามารถใช้ได้ดังต่อไปนี้ เวอร์ชัน SQLเซิร์ฟเวอร์ (ธุรกรรม-SQL):
เซิร์ฟเวอร์ SQL vNext, เซิร์ฟเวอร์ SQL 2016, เซิร์ฟเวอร์ SQL 2015, เซิร์ฟเวอร์ SQL 2014, เซิร์ฟเวอร์ SQL 2012, เซิร์ฟเวอร์ SQL 2008 R2, เซิร์ฟเวอร์ SQL 2008, เซิร์ฟเวอร์ SQL 2005
ตัวอย่างที่มีหนึ่งฟิลด์
มาดูกันบ้างครับ ตัวอย่าง SQLฟังก์ชัน COUNT ของเซิร์ฟเวอร์เพื่อทำความเข้าใจวิธีใช้ฟังก์ชัน COUNT ใน SQL Server (Transact-SQL)
ตัวอย่างเช่น คุณสามารถดูได้ว่าผู้ใช้ที่มีนามสกุล Last_name = "Rasputin" มีผู้ติดต่อกี่ราย
ในตัวอย่างฟังก์ชัน COUNT นี้ เราได้ระบุนามแฝง "จำนวนผู้ติดต่อ" ให้กับนิพจน์ COUNT (*) ดังนั้นชุดผลลัพธ์จะแสดง "จำนวนผู้ติดต่อ" เป็นชื่อฟิลด์
ตัวอย่างการใช้ DISTINCT
คุณสามารถใช้ตัวดำเนินการ DISTINCT ในฟังก์ชัน COUNT ได้ ตัวอย่างเช่น คำสั่ง SQL ด้านล่างนี้จะส่งกลับจำนวนแผนกที่ไม่ซ้ำกัน โดยที่พนักงานอย่างน้อยหนึ่งคนมี first_name = 'Samvel'
ในการกำหนดจำนวนบันทึกในตาราง MySQL คุณต้องใช้ฟังก์ชันพิเศษ COUNT()
ฟังก์ชัน COUNT() ส่งกลับจำนวนเรคคอร์ดในตารางที่ตรงกับเกณฑ์ที่กำหนด
ฟังก์ชัน COUNT(expr) จะนับเฉพาะแถวที่ผลลัพธ์ของ expr ไม่ใช่ NULL เสมอ
ข้อยกเว้นสำหรับกฎนี้คือเมื่อใช้ฟังก์ชัน COUNT() โดยมีเครื่องหมายดอกจันเป็นอาร์กิวเมนต์ - COUNT(*) ในกรณีนี้ แถวทั้งหมดจะถูกนับ ไม่ว่าจะเป็น NULL หรือ NOT NULL
ตัวอย่างเช่น ฟังก์ชัน COUNT(*) จะส่งคืนจำนวนระเบียนทั้งหมดในตาราง:
เลือก COUNT(*) จาก table_name
วิธีนับจำนวนบันทึกและแสดงบนหน้าจอ
ตัวอย่างโค้ด PHP+MySQL สำหรับการนับและแสดงจำนวนแถวทั้งหมด:
$res = mysql_query("SELECT COUNT(*) FROM table_name") $row = mysql_fetch_row($res); $รวม = $แถว; // บันทึกทั้งหมดสะท้อน $total; -
ตัวอย่างนี้แสดงให้เห็นถึงการใช้ฟังก์ชัน COUNT() ที่ง่ายที่สุด แต่คุณยังสามารถทำงานอื่น ๆ โดยใช้ฟังก์ชันนี้ได้
โดยการระบุคอลัมน์ตารางเฉพาะเป็นพารามิเตอร์ ฟังก์ชัน COUNT(column_name) จะส่งกลับจำนวนระเบียนในคอลัมน์นั้นที่ไม่มีค่า NULL บันทึกที่มีค่า NULL จะถูกละเว้น
เลือก COUNT(column_name) จาก table_name
ไม่สามารถใช้ฟังก์ชัน mysql_num_rows() ได้ เนื่องจากคุณต้องเรียกใช้ฟังก์ชันเพื่อหาจำนวนระเบียนทั้งหมด เลือกแบบสอบถาม* FROM db นั่นคือรับบันทึกทั้งหมด แต่สิ่งนี้ไม่พึงประสงค์ ดังนั้นจึงควรใช้ฟังก์ชันการนับ
$result = mysql_query("SELECT COUNT (*) as rec FROM db");
การใช้ฟังก์ชัน COUNT() เป็นตัวอย่าง
นี่เป็นอีกตัวอย่างหนึ่งของการใช้ฟังก์ชัน COUNT() สมมติว่ามีตาราง ice_cream ที่มีแค็ตตาล็อกไอศกรีม ซึ่งมีตัวระบุหมวดหมู่และชื่อไอศกรีม
คอมพิวเตอร์
ฟังก์ชั่นสรุป
นิพจน์คิวรี SQL มักต้องมีการประมวลผลข้อมูลล่วงหน้า เพื่อจุดประสงค์นี้ มีการใช้ฟังก์ชันและสำนวนพิเศษ
บ่อยครั้งที่คุณต้องค้นหาว่ามีกี่ระเบียนที่ตรงกับข้อความค้นหาหนึ่งๆผลรวมของค่าของคอลัมน์ตัวเลขบางคอลัมน์คือค่าสูงสุดค่าต่ำสุดและค่าเฉลี่ย เพื่อจุดประสงค์นี้ จะใช้ฟังก์ชันสุดท้ายที่เรียกว่า (สถิติ การรวม) ฟังก์ชันสรุปจะประมวลผลชุดของเร็กคอร์ดที่ระบุ เช่น โดยส่วนคำสั่ง WHERE หากคุณรวมไว้ในรายการคอลัมน์ตามคำสั่ง SELECT ตารางผลลัพธ์จะไม่เพียงประกอบด้วยคอลัมน์ตารางฐานข้อมูลเท่านั้น แต่ยังรวมถึงค่าที่คำนวณโดยฟังก์ชันเหล่านี้ด้วย ต่อไปนี้คือรายการฟังก์ชันสรุป.
- นับ (พารามิเตอร์ ) ส่งคืนจำนวนบันทึกที่ระบุในพารามิเตอร์ หากคุณต้องการรับจำนวนระเบียนทั้งหมด คุณควรระบุเครื่องหมายดอกจัน (*) เป็นพารามิเตอร์ หากคุณระบุชื่อคอลัมน์เป็นพารามิเตอร์ ฟังก์ชันจะส่งกลับจำนวนระเบียนที่คอลัมน์นี้มีค่าอื่นที่ไม่ใช่ NULL หากต้องการทราบว่าคอลัมน์ประกอบด้วยค่าต่างๆ เท่าใด ให้ใส่คีย์เวิร์ด DISTINCT นำหน้าชื่อคอลัมน์ ตัวอย่างเช่น:
เลือก COUNT(*) จากลูกค้า;
เลือก COUNT(Order_Amount) จากลูกค้า
เลือก COUNT (DISTINCT Order_Amount) จากลูกค้า
การพยายามเรียกใช้แบบสอบถามต่อไปนี้จะส่งผลให้เกิดข้อความแสดงข้อผิดพลาด:
เลือกภูมิภาค , COUNT(*) จากไคลเอนต์ ;
- ผลรวม (พารามิเตอร์ ) ส่งคืนผลรวมของค่าของคอลัมน์ที่ระบุในพารามิเตอร์ พารามิเตอร์ยังสามารถเป็นนิพจน์ที่มีชื่อของคอลัมน์ได้ด้วย ตัวอย่างเช่น:
เลือกผลรวม (Order_Amount) จากลูกค้า
คำสั่ง SQL นี้ส่งคืนตารางหนึ่งคอลัมน์และหนึ่งระเบียนที่มีผลรวมของค่าที่กำหนดไว้ทั้งหมดสำหรับคอลัมน์ Order_Amount จากตารางลูกค้า
สมมติว่าในตารางแหล่งที่มาค่าของคอลัมน์ Order_Amount จะแสดงเป็นรูเบิลและเราจำเป็นต้องคำนวณจำนวนเงินทั้งหมดเป็นดอลลาร์ หากอัตราแลกเปลี่ยนปัจจุบันคือ 27.8 คุณจะได้รับผลลัพธ์ที่ต้องการโดยใช้นิพจน์:
เลือกผลรวม (Order_amount*27.8) จากลูกค้า
- AVG (พารามิเตอร์ ) ส่งคืนค่าเฉลี่ยเลขคณิตของค่าทั้งหมดของคอลัมน์ที่ระบุในพารามิเตอร์ พารามิเตอร์อาจเป็นนิพจน์ที่มีชื่อของคอลัมน์ ตัวอย่างเช่น:
เลือก AVG (Order_Amount) จากลูกค้า
เลือก AVG (Order_Amount*27.8) จากลูกค้า
ที่ไหน ภูมิภาค<>"นอร์ธ_3เวสต์";
- สูงสุด (พารามิเตอร์ ) ส่งคืนค่าสูงสุดในคอลัมน์ที่ระบุในพารามิเตอร์ พารามิเตอร์ยังสามารถเป็นนิพจน์ที่มีชื่อของคอลัมน์ได้ด้วย ตัวอย่างเช่น:
เลือกสูงสุด (Order_Amount) จากลูกค้า
เลือกสูงสุด(Order_Amount*27.8) จากลูกค้า
ที่ไหน ภูมิภาค<>"นอร์ธ_3เวสต์";
- ขั้นต่ำ (พารามิเตอร์ ) ส่งคืนค่าต่ำสุดในคอลัมน์ที่ระบุในพารามิเตอร์ พารามิเตอร์อาจเป็นนิพจน์ที่มีชื่อของคอลัมน์ ตัวอย่างเช่น:
เลือก MIN(Order_Amount) จากลูกค้า
เลือก MIN (จำนวนคำสั่งซื้อ*27.8) จากลูกค้า
ที่ไหน ภูมิภาค<>"นอร์ธ_3เวสต์";
ในทางปฏิบัติมักจำเป็นต้องได้รับตารางสุดท้ายที่มีค่ารวม, ค่าเฉลี่ย, สูงสุดและต่ำสุดของคอลัมน์ตัวเลข เมื่อต้องการทำเช่นนี้ คุณควรใช้ฟังก์ชันการจัดกลุ่ม (GROUP BY) และสรุป
เลือกภูมิภาค SUM (Order_amount) จากลูกค้า
จัดกลุ่มตามภูมิภาค
ตารางผลลัพธ์สำหรับการสืบค้นนี้ประกอบด้วยชื่อของภูมิภาคและจำนวนคำสั่งซื้อทั้งหมด (ทั้งหมด) จากลูกค้าทั้งหมดจากภูมิภาคที่เกี่ยวข้อง (รูปที่ 5)
ตอนนี้ให้พิจารณาคำขอเพื่อรับข้อมูลสรุปทั้งหมดตามภูมิภาค:
เลือกภูมิภาค, SUM (Order_Amount), AVG (จำนวนการสั่งซื้อ), MAX (จำนวนการสั่งซื้อ), MIN (คำสั่งซื้อ_จำนวน)
จากลูกค้า
จัดกลุ่มตามภูมิภาค
ตารางต้นฉบับและผลลัพธ์จะแสดงในรูป 8. ในตัวอย่าง เฉพาะภูมิภาคตะวันตกเฉียงเหนือเท่านั้นที่จะแสดงในตารางต้นฉบับด้วยระเบียนมากกว่าหนึ่งรายการ ดังนั้นในตารางผลลัพธ์ ฟังก์ชันสรุปที่แตกต่างกันจะให้ค่าที่ต่างกัน
ข้าว. 8. ตารางสุดท้ายของจำนวนการสั่งซื้อตามภูมิภาค
เมื่อคุณใช้ฟังก์ชันสรุปในรายการคอลัมน์ในคำสั่ง SELECT ส่วนหัวของคอลัมน์ที่เกี่ยวข้องในตารางผลลัพธ์จะเป็น Expr1001, Expr1002 และอื่นๆ (หรืออะไรที่คล้ายกัน ขึ้นอยู่กับการใช้งาน SQL) อย่างไรก็ตาม คุณสามารถตั้งค่าส่วนหัวสำหรับค่าของฟังก์ชันสรุปและคอลัมน์อื่นๆ ได้ตามดุลยพินิจของคุณ เมื่อต้องการทำเช่นนี้ ถัดจากคอลัมน์ในคำสั่ง SELECT ให้ระบุนิพจน์ของแบบฟอร์ม:
AS column_heading
คำหลัก AS (as) หมายความว่าในตารางผลลัพธ์ คอลัมน์ที่เกี่ยวข้องจะต้องมีส่วนหัวที่ระบุหลัง AS ชื่อที่ได้รับมอบหมายเรียกอีกอย่างว่านามแฝง ตัวอย่างต่อไปนี้ (รูปที่ 9) ตั้งชื่อแทนสำหรับคอลัมน์จากการคำนวณทั้งหมด:
เลือกภูมิภาค
ผลรวม (Order_Amount) AS [จำนวนการสั่งซื้อทั้งหมด],
เฉลี่ย (Order_Amount) AS [จำนวนออร์เดอร์เฉลี่ย],
MAX(Order_Amount) AS สูงสุด,
นาที (Order_amount) เป็นขั้นต่ำ
จากลูกค้า
จัดกลุ่มตามภูมิภาค
ข้าว. 9. ตารางสุดท้ายของจำนวนการสั่งซื้อตามภูมิภาคโดยใช้ชื่อแทนคอลัมน์
ชื่อเล่นที่ประกอบด้วยคำหลายคำคั่นด้วยช่องว่างจะอยู่ในวงเล็บเหลี่ยม
ฟังก์ชันสรุปสามารถใช้ได้ในส่วนคำสั่ง SELECT และ HAVING แต่ไม่สามารถใช้ในส่วนคำสั่ง WHERE ได้ ตัวดำเนินการ HAVING คล้ายกับตัวดำเนินการ WHERE แต่ไม่เหมือนกับ WHERE ที่จะเลือกบันทึกในกลุ่ม
สมมติว่าคุณต้องการพิจารณาว่าภูมิภาคใดมีลูกค้ามากกว่าหนึ่งราย เพื่อจุดประสงค์นี้ คุณสามารถใช้แบบสอบถามต่อไปนี้:
เลือกภูมิภาค จำนวน(*)
จากลูกค้า
จัดกลุ่มตามภูมิภาคที่มี COUNT(*) > 1;
ฟังก์ชันการประมวลผลค่า
เมื่อทำงานกับข้อมูล คุณมักจะต้องประมวลผลข้อมูล (แปลงเป็นรูปแบบที่ต้องการ): เลือกสตริงย่อยในสตริง ลบช่องว่างนำหน้าและต่อท้าย ปัดเศษตัวเลข คำนวณรากที่สอง กำหนดเวลาปัจจุบัน ฯลฯ SQL มีฟังก์ชันสามประเภทดังต่อไปนี้:
- ฟังก์ชันสตริง
- ฟังก์ชันตัวเลข
- ฟังก์ชันวันที่-เวลา
ฟังก์ชันสตริง
ฟังก์ชันสตริงรับสตริงเป็นพารามิเตอร์และส่งกลับสตริงหรือ NULL หลังจากประมวลผลแล้ว
- สตริงย่อย (บรรทัดจากจุดเริ่มต้น)ส่งคืนสตริงย่อยที่เกิดจากสตริงที่ระบุเป็นพารามิเตอร์เส้น . สตริงย่อย เริ่มต้นด้วยอักขระที่ระบุหมายเลขซีเรียลในพารามิเตอร์เริ่มต้น และมีความยาวที่ระบุในพารามิเตอร์ความยาว อักขระในสตริงจะมีหมายเลขจากซ้ายไปขวา โดยเริ่มจาก 1 วงเล็บเหลี่ยมที่นี่เพียงบ่งชี้ว่านิพจน์ที่อยู่ในอักขระนั้นเป็นทางเลือกหรือไม่ หากเป็นการแสดงออกสำหรับความยาว ไม่ได้ใช้ ดังนั้นจะเป็นสตริงย่อยจากเริ่ม และไปจนสุดบรรทัดเดิม ค่าพารามิเตอร์เริ่มต้นและความยาว ต้องเลือกเพื่อให้สตริงย่อยที่ค้นหาอยู่ภายในสตริงต้นฉบับ มิฉะนั้น ฟังก์ชัน SUBSTRING จะส่งคืนค่า NULL
ตัวอย่างเช่น:
SUBSTRING ("เรียน Masha!" จาก 9 ต่อ 4) ส่งคืน "Masha";
SUBSTRING ("เรียน Masha!" จาก 9) ส่งคืน "Masha!";
SUBSTRING("Dear Masha!" จาก 15) ส่งคืนค่า NULL
คุณสามารถใช้ฟังก์ชันนี้ในนิพจน์ SQL ได้ เช่น:
เลือก * จากไคลเอนต์
โดยที่ SUBSTRING (ภูมิภาคจาก 1 ต่อ 5) = "ภาคเหนือ";
- บน(string ) แปลงอักขระทั้งหมดของสตริงที่ระบุในพารามิเตอร์เป็นตัวพิมพ์ใหญ่
- ล่าง(string ) แปลงอักขระทั้งหมดของสตริงที่ระบุในพารามิเตอร์ให้เป็นตัวพิมพ์เล็ก
- TRIM (นำหน้า | ต่อท้าย | ทั้งสอง ["ตัวละคร"] จากสตริง ) ลบอักขระนำหน้า (LEADING) ต่อท้าย (TRAILING) หรือทั้งสอง (BOTH) ออกจากสตริง ตามค่าเริ่มต้น อักขระที่จะลบจะเป็นช่องว่าง (" ") จึงสามารถละเว้นได้ ส่วนใหญ่แล้ว ฟังก์ชันนี้ใช้เพื่อลบช่องว่าง
ตัวอย่างเช่น:
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!" IN "Hello every") คืนค่า 0
ในตารางลูกค้า (ดูรูปที่ 1) คอลัมน์ที่อยู่ประกอบด้วยนอกเหนือจากชื่อเมือง รหัสไปรษณีย์ ชื่อถนน และข้อมูลอื่นๆ คุณอาจต้องเลือกเรกคอร์ดสำหรับลูกค้าที่อาศัยอยู่ในเมืองใดเมืองหนึ่ง ดังนั้น หากคุณต้องการเลือกเรกคอร์ดที่เกี่ยวข้องกับลูกค้าที่อาศัยอยู่ในเซนต์ปีเตอร์สเบิร์ก คุณสามารถใช้นิพจน์คิวรี SQL ต่อไปนี้:
เลือก * จากไคลเอนต์
ตำแหน่งที่ไหน (" เซนต์ปีเตอร์สเบิร์ก " ที่อยู่ ) > 0;
โปรดทราบว่าคำขอเรียกข้อมูลแบบธรรมดานี้สามารถกำหนดสูตรที่แตกต่างกันได้:
เลือก * จากไคลเอนต์
ที่อยู่ LIKE "%Petersburg%" อยู่ที่ไหน;
- แยก (พารามิเตอร์ ) แยกองค์ประกอบจากค่าวันที่-เวลาหรือจากช่วงเวลา ตัวอย่างเช่น:
สารสกัด (เดือนนับจากวันที่ "2548-10-25")ส่งคืน 10
- CHARACTER_LENGTH(สตริง ) ส่งคืนจำนวนอักขระในสตริง
ตัวอย่างเช่น:
CHARACTER_LENGTH("สวัสดีทุกคน") ส่งคืน 11
- OCTET_LENGTH(สตริง ) ส่งคืนจำนวนอ็อกเท็ต (ไบต์) ในสตริง อักขระละตินหรือซีริลลิกแต่ละตัวจะแสดงเป็นหนึ่งไบต์ และอักขระตัวอักษรจีนจะแสดงเป็นสองไบต์
- จำนวนสมาชิก (พารามิเตอร์ ) รับคอลเลกชันขององค์ประกอบเป็นพารามิเตอร์และส่งกลับจำนวนองค์ประกอบในคอลเลกชัน (หมายเลขสำคัญ) คอลเลกชันอาจเป็นอาร์เรย์หรือหลายชุดที่มีองค์ประกอบประเภทต่างๆ
- เอบีเอส (หมายเลข ) ส่งกลับค่าสัมบูรณ์ของตัวเลข ตัวอย่างเช่น:
ABS (-123) ส่งกลับ 123;
ABS (2 - 5) ส่งคืน 3
- มธ. (หมายเลข 1, หมายเลข 2 ) ส่งคืนเศษที่เหลือของการหารจำนวนเต็มของตัวเลขแรกเป็นวินาที ตัวอย่างเช่น:
MOD(5,h) ส่งคืน 2;
MOD(2,h) ส่งคืนค่า 0
- LN (หมายเลข ) ส่งกลับค่าลอการิทึมธรรมชาติของตัวเลข
- EXP (ตัวเลข) ส่งกลับตัวเลข (ฐานของลอการิทึมธรรมชาติเป็นกำลังของตัวเลข)
- พาวเวอร์ (หมายเลข 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 ระบุช่วงตัวเลขที่แบ่งออกเป็นช่วงที่เท่ากัน ซึ่งจำนวนที่ระบุโดยพารามิเตอร์ number4 ฟังก์ชันจะกำหนด จำนวนช่วงเวลาที่ค่าตกเป็นหมายเลข 1 ถ้าหมายเลข 1 อยู่นอกช่วงที่ระบุ ฟังก์ชันจะส่งกลับ 0 หรือหมายเลข 4 + 1 ตัวอย่างเช่น
WIDTH_BUCKET(3.14, 0, 9, 5) ส่งคืน 2
ฟังก์ชันวันที่-เวลา
SQL มีสามฟังก์ชันที่ส่งคืนวันที่และเวลาปัจจุบัน
- CURRENT_DATE ส่งกลับวันที่ปัจจุบัน (พิมพ์ DATE)
ตัวอย่างเช่น: 18-06-2548
- CURRENT_TIME (หมายเลข ) ส่งคืนเวลาปัจจุบัน (ประเภท TIME) พารามิเตอร์จำนวนเต็มระบุความแม่นยำของการแทนค่าวินาที ตัวอย่างเช่น ค่า 2 จะแสดงวินาทีถึงตำแหน่งที่ใกล้ที่สุด (ทศนิยม 2 ตำแหน่ง):
12:39:45.27.
- CURRENT_TIMESTAMP (หมายเลข ) ส่งคืนวันที่และเวลา (ประเภท TIMESTAMP) ตัวอย่างเช่น 18-06-2548 12:39:45.27 น. พารามิเตอร์จำนวนเต็มระบุความแม่นยำของการแทนค่าวินาที
โปรดทราบว่าวันที่และเวลาที่ฟังก์ชันเหล่านี้ส่งคืนไม่ใช่ประเภทอักขระ หากคุณต้องการแสดงเป็นสตริงอักขระ คุณควรใช้ฟังก์ชันการแปลงประเภท CAST() เพื่อทำสิ่งนี้
ฟังก์ชันวันที่-เวลามักใช้ในคิวรีเพื่อแทรก อัปเดต และลบข้อมูล เช่น เมื่อบันทึกข้อมูลการขาย ให้กรอก วันที่ปัจจุบันและเวลา หลังจากสรุปผลเป็นเวลาหนึ่งเดือนหรือไตรมาสแล้ว ข้อมูลการขายสำหรับรอบระยะเวลารายงานก็สามารถลบออกได้
นิพจน์ที่คำนวณแล้ว
นิพจน์ที่คำนวณถูกสร้างขึ้นจากค่าคงที่ (ตัวเลข สตริง ตรรกะ) ฟังก์ชัน ชื่อฟิลด์ และข้อมูลประเภทอื่นๆ โดยการเชื่อมต่อกับตัวดำเนินการทางคณิตศาสตร์ สตริง ตรรกะ และตัวดำเนินการอื่นๆ ในทางกลับกัน นิพจน์สามารถรวมกันได้โดยใช้ตัวดำเนินการให้เป็นนิพจน์ที่ซับซ้อนมากขึ้น (แบบผสม) วงเล็บใช้เพื่อควบคุมลำดับการประเมินนิพจน์
ตัวดำเนินการเชิงตรรกะ AND, OR และ NOT และฟังก์ชัน ได้รับการพูดคุยกันก่อนหน้านี้
ตัวดำเนินการทางคณิตศาสตร์:
- + นอกจากนี้;
- - ลบ;
- * การคูณ;
- / แผนก.
ตัวดำเนินการสตริงมีเพียงตัวดำเนินการต่อข้อมูลหรือต่อสตริงเดียวเท่านั้น (| |) ในการใช้งาน SQL บางอย่าง (เช่น ไมโครซอฟต์ แอคเซส) แทน (| |) จะใช้สัญลักษณ์ (+) ตัวดำเนินการต่อท้ายสตริงที่สองต่อท้ายตัวอย่างแรก นิพจน์:
"ซาช่า" | - "รัก" | - "โบกมือ"
จะส่งกลับสตริง "Sasha รัก Masha" ตามผลลัพธ์
เมื่อเขียนนิพจน์ คุณต้องตรวจสอบให้แน่ใจว่าตัวถูกดำเนินการของตัวดำเนินการนั้นเป็นประเภทที่ถูกต้อง ตัวอย่างเช่น นิพจน์: 123 + "Sasha" ไม่ถูกต้อง เนื่องจากมีการใช้ตัวดำเนินการบวกเลขคณิตกับตัวถูกดำเนินการสตริง
นิพจน์ที่คำนวณอาจปรากฏขึ้นหลังจากนั้น คำสั่งเลือกเช่นเดียวกับการแสดงออกตามเงื่อนไขของคำสั่ง WHERE และ HAVIเอ็น.จี.
ลองดูตัวอย่างบางส่วน
ให้ตารางยอดขายประกอบด้วยคอลัมน์ประเภทผลิตภัณฑ์ ปริมาณ และราคา และเราต้องการทราบรายได้ของผลิตภัณฑ์แต่ละประเภท ในการดำเนินการนี้ เพียงรวมนิพจน์ Volume*Price ในรายการคอลัมน์หลังคำสั่ง SELECT:
เลือก Product_type, จำนวน, ราคา, จำนวน*ราคาเช่น
รวมจากการขาย;
ซึ่งใช้คำสำคัญ AS (as) เพื่อระบุนามแฝงสำหรับคอลัมน์ข้อมูลที่คำนวณ
ในรูป รูปที่ 10 แสดงตารางการขายดั้งเดิมและตารางผลลัพธ์แบบสอบถาม
ข้าว. 10. ผลการสืบค้นพร้อมคำนวณรายได้สำหรับสินค้าแต่ละประเภท
หากคุณต้องการทราบรายได้รวมจากการขายสินค้าทั้งหมด ให้ใช้คำค้นหาต่อไปนี้:
เลือกผลรวม (ปริมาณ*ราคา) จากการขาย;
แบบสอบถามต่อไปนี้ประกอบด้วยนิพจน์จากการคำนวณทั้งในรายการคอลัมน์และเงื่อนไขของส่วนคำสั่ง WHERE เขาเลือกผลิตภัณฑ์ที่มีรายได้จากการขายมากกว่า 1,000 รายการจากตารางการขาย:
เลือก Product_type, จำนวน*ราคา AS รวม
จากการขาย
โดยที่ ปริมาณ*ราคา > 1,000;
สมมติว่าคุณต้องการได้ตารางที่มีสองคอลัมน์:
สินค้าที่มีประเภทและราคาสินค้า
รวมรายได้
เนื่องจากสันนิษฐานว่าในตารางการขายเดิม คอลัมน์ Product_Type เป็นอักขระ (ประเภท CHAR) และคอลัมน์ราคาเป็นตัวเลข เมื่อรวมข้อมูล (ติดกาว) จากคอลัมน์เหล่านี้ จำเป็นต้องแปลงประเภทตัวเลขเป็นประเภทอักขระโดยใช้ ฟังก์ชัน CAST() แบบสอบถามที่ทำงานนี้มีลักษณะดังนี้ (รูปที่ 11):
เลือกประเภทผลิตภัณฑ์ | - " (ราคา: " | | CAST(ราคา AS CHAR(5)) | | ")" AS สินค้า, จำนวน*ราคา AS รวม
จากการขาย;
ข้าว. 11. ผลลัพธ์ของการสืบค้นรวมข้อมูลประเภทต่างๆ ไว้ในคอลัมน์เดียว
บันทึก. ใน Microsoft Access แบบสอบถามที่คล้ายกันจะมีลักษณะดังนี้:
เลือก Product_type + " (ราคา: " + Cถ (ราคา) + ")" AS สินค้า
ปริมาณ*ราคา AS รวม
จากการขาย;
นิพจน์แบบมีเงื่อนไขพร้อมคำสั่ง CASE
ภาษาโปรแกรมทั่วไปมีตัวดำเนินการข้ามแบบมีเงื่อนไขที่ให้คุณควบคุมกระบวนการคำนวณโดยขึ้นอยู่กับว่าเงื่อนไขบางอย่างเป็นจริงหรือไม่ ใน SQL ตัวดำเนินการนี้คือ CASE (กรณี, สถานการณ์, ตัวอย่าง- ใน SQL:2003 ตัวดำเนินการนี้จะส่งคืนค่า ดังนั้นจึงสามารถนำมาใช้ในนิพจน์ได้ มีสองรูปแบบหลักซึ่งเราจะดูในส่วนนี้
คำสั่ง CASE ที่มีค่า
คำสั่ง CASE ที่มีค่ามีไวยากรณ์ดังต่อไปนี้:
CASE ตรวจสอบ_ค่าแล้ว
เมื่อค่า 1 แล้วผลลัพธ์ 1
เมื่อค่า 2 แล้วผลลัพธ์ 2
. . .
เมื่อค่าของ N แล้วผลลัพธ์ของ N
อย่างอื่นผลลัพธ์X
ในกรณีที่ ตรวจสอบแล้ว_ค่าเท่ากับค่า 1 คำสั่ง CASE จะส่งคืนค่าผลลัพธ์1 ระบุหลังคีย์เวิร์ด THEN มิฉะนั้น check_value จะถูกเปรียบเทียบด้วยค่า2 และหากเท่ากัน ค่า result2 จะถูกส่งกลับ มิฉะนั้น ค่าที่กำลังทดสอบจะถูกเปรียบเทียบกับค่าถัดไปที่ระบุหลังคีย์เวิร์ด WHEN เป็นต้น หาก test_value ไม่เท่ากับค่าใดๆ เหล่านี้ ค่าจะถูกส่งกลับผลลัพธ์ X ระบุหลังคีย์เวิร์ด ELSE (else)
คำหลัก ELSE เป็นทางเลือก หากไม่มีหายไปและไม่มีค่าใดที่เปรียบเทียบจะเท่ากับค่าที่กำลังทดสอบ คำสั่ง CASE จะส่งคืน NULL
สมมติว่าตามตารางไคลเอนต์ (ดูรูปที่ 1) คุณต้องการรับตารางที่ชื่อของภูมิภาคถูกแทนที่ด้วยหมายเลขรหัส หากมีภูมิภาคที่แตกต่างกันไม่มากนักในตารางต้นฉบับ เพื่อแก้ไขปัญหานี้ จะสะดวกในการใช้แบบสอบถามกับตัวดำเนินการ CASE:
เลือกชื่อ ที่อยู่
กรณี ภูมิภาค
เมื่อ "มอสโก" แล้วก็ "77"
เมื่อ "ภูมิภาคตเวียร์" แล้ว "69"
. . .
ภูมิภาคอื่นๆ
รหัสภูมิภาค AS
จากลูกค้า;
คำสั่ง CASE พร้อมเงื่อนไขการค้นหา
รูปแบบที่สองของตัวดำเนินการ CASE เกี่ยวข้องกับการใช้เมื่อค้นหาตารางสำหรับระเบียนที่ตรงตามเงื่อนไขบางประการ:
กรณี
เมื่อเงื่อนไข 1 แล้วผลลัพธ์ 1
เมื่อ catch2 แล้วผลลัพธ์ 2
. . .
เมื่อเงื่อนไข N แล้ว ผลลัพธ์ N
อย่างอื่นผลลัพธ์X
คำสั่ง CASE จะทดสอบว่าเงื่อนไข 1 เป็นจริงสำหรับระเบียนแรกในชุดที่กำหนดโดยส่วนคำสั่ง WHERE หรือทั้งตารางหากไม่มี WHERE ถ้าใช่ CASE จะส่งกลับผลลัพธ์ 1 มิฉะนั้น จะมีการตรวจสอบเงื่อนไข 2 สำหรับเรกคอร์ดนี้ หากเป็นจริง ค่า result2 จะถูกส่งกลับ เป็นต้น หากไม่มีเงื่อนไขใดเป็นจริง ผลลัพธ์ของค่าจะถูกส่งกลับเอ็กซ์ ระบุหลังคีย์เวิร์ด ELSE
คำหลัก ELSE เป็นทางเลือก หากไม่มีเงื่อนไขใดเป็นจริง คำสั่ง CASE จะหมุนเวียนเป็น NULL หลังจากดำเนินการคำสั่งที่มี CASE สำหรับเรคคอร์ดแรกแล้ว มันจะย้ายไปยังเรคคอร์ดถัดไป สิ่งนี้จะดำเนินต่อไปจนกระทั่งประมวลผลบันทึกทั้งชุด
สมมติว่าในตารางหนังสือ (ชื่อเรื่อง, ราคา) คอลัมน์จะเป็น NULL หากหนังสือที่เกี่ยวข้องหมด แบบสอบถามต่อไปนี้ส่งกลับตารางที่แสดง "สินค้าหมด" แทนที่จะเป็น NULL:
เลือกหัวข้อ
กรณี
เมื่อราคาเป็นโมฆะ แสดงว่า "สินค้าหมด"
นักแสดงอื่น(ราคาเป็น CHAR(8))
AS ราคา
จากหนังสือ;
ค่าทั้งหมดในคอลัมน์เดียวกันต้องเป็นประเภทเดียวกัน ดังนั้นใน คำขอนี้ฟังก์ชันการแปลงประเภท CAST ใช้เพื่อแปลงค่าตัวเลขของคอลัมน์ราคาเป็นประเภทอักขระ
โปรดทราบว่าคุณสามารถใช้รูปแบบที่สองของคำสั่ง CASE แทนรูปแบบแรกได้เสมอ:
กรณี
เมื่อ test_value = value1 แล้วให้ผลลัพธ์ 1
เมื่อ test_value = value2 แล้วให้ผลลัพธ์ 2
. . .
เมื่อ test_value = ค่า N แล้วผลลัพธ์N
ผลลัพธ์อื่น
ฟังก์ชัน NULLIF และ COALESCE
ในบางกรณี โดยเฉพาะอย่างยิ่งในการร้องขอให้อัปเดตข้อมูล (ตัวดำเนินการ UPDATE) จะสะดวกกว่าที่จะใช้ฟังก์ชัน NULLIF() (NULL if) และ COALESCE() (รวม) ที่มีขนาดกะทัดรัดกว่า แทนที่จะใช้ตัวดำเนินการ CASE ที่ยุ่งยาก
ฟังก์ชัน NULLIF ( ค่า1, ค่า2) ส่งคืนค่า NULL หากค่าของพารามิเตอร์ตัวแรกตรงกับค่าของพารามิเตอร์ตัวที่สอง ในกรณีที่ไม่ตรงกัน ค่าของพารามิเตอร์ตัวแรกจะถูกส่งกลับโดยไม่มีการเปลี่ยนแปลง นั่นคือ ถ้าค่าความเท่าเทียมกัน 1 = value2 เป็นจริง ฟังก์ชันจะส่งกลับค่า NULL หรือมิฉะนั้นก็ให้ค่า value1
ฟังก์ชันนี้เทียบเท่ากับคำสั่ง CASE ในสองรูปแบบต่อไปนี้:
- ค่ากรณี1
เมื่อค่า 2 แล้วเป็นโมฆะ
ค่าอื่น1
- กรณี
เมื่อ value1 = value2 แล้วเป็นโมฆะ
ค่าอื่น1
ฟังก์ชัน COALESCE( ค่า1, ค่า2, ... ,ค่า N) ยอมรับรายการค่าซึ่งอาจเป็น NULL หรือ NULL ก็ได้ ฟังก์ชันส่งคืนค่าที่ระบุจากรายการหรือ NULL หากค่าทั้งหมดไม่ได้ถูกกำหนดไว้
ฟังก์ชันนี้เทียบเท่ากับคำสั่ง CASE ต่อไปนี้:
กรณี
เมื่อค่า 1 ไม่เป็นโมฆะ แสดงว่าค่า 1
เมื่อค่า 2 ไม่เป็นโมฆะ แล้วค่า 2
. . .
เมื่อค่า N ไม่เป็นโมฆะ แล้วค่า N
มิฉะนั้นจะเป็นโมฆะ
สมมติว่าในตารางหนังสือ (ชื่อเรื่อง, ราคา) คอลัมน์ราคาจะเป็น NULL หากหนังสือที่เกี่ยวข้องหมด แบบสอบถามต่อไปนี้ส่งคืนตารางโดยที่แทนที่จะเป็นโมฆะ ข้อความ "สินค้าหมด" ปรากฏขึ้น:
เลือกชื่อ COALESCE (CAST(ราคา AS CHAR(8))
"สินค้าหมด") ตามราคา
จากหนังสือ;
ฉันจะทราบจำนวนรุ่นพีซีที่ผลิตโดยซัพพลายเออร์รายใดรายหนึ่งได้อย่างไร วิธีกำหนดราคาเฉลี่ยของคอมพิวเตอร์ที่มีราคาเท่ากัน ข้อกำหนดทางเทคนิค- คำถามเหล่านี้และคำถามอื่นๆ ที่เกี่ยวข้องกับข้อมูลทางสถิติสามารถตอบได้โดยใช้ แบบสอบถามใช้ฟังก์ชันที่มีอยู่แล้วภายในปีและเดือนเพื่อแยกปีและเดือนออกจากวันที่- มาตรฐานมีฟังก์ชันรวมดังต่อไปนี้:
ฟังก์ชันทั้งหมดนี้ส่งคืนค่าเดียว ขณะเดียวกันก็มีฟังก์ชั่นต่างๆ นับ, นาทีและ สูงสุดใช้ได้กับข้อมูลทุกประเภทในขณะนั้น ผลรวมและ เฉลี่ยใช้สำหรับช่องตัวเลขเท่านั้น ความแตกต่างระหว่างฟังก์ชั่น นับ(*)และ นับ(<имя поля>) คืออันที่สองไม่ได้คำนึงถึงค่า NULL เมื่อคำนวณ
ตัวอย่าง. ค้นหาราคาต่ำสุดและสูงสุดสำหรับคอมพิวเตอร์ส่วนบุคคล:
ตัวอย่าง. ค้นหาจำนวนคอมพิวเตอร์ที่ผลิตโดยผู้ผลิต A:
ตัวอย่าง. หากเราสนใจปริมาณ รุ่นต่างๆผลิตโดยผู้ผลิต A จากนั้นสามารถกำหนดแบบสอบถามได้ดังนี้ (โดยใช้ข้อเท็จจริงที่ว่าในตารางผลิตภัณฑ์แต่ละรุ่นจะถูกบันทึกหนึ่งครั้ง):
ตัวอย่าง. ค้นหาจำนวนรุ่นต่างๆ ที่ผลิตโดยผู้ผลิต A แบบสอบถามคล้ายกับรุ่นก่อนหน้าซึ่งจำเป็นต้องระบุ จำนวนทั้งหมดรุ่นที่ผลิตโดยผู้ผลิต A. ที่นี่คุณยังต้องค้นหาจำนวนรุ่นต่างๆ ในตาราง PC (เช่น มีวางจำหน่ายทั่วไป)
เพื่อให้แน่ใจว่าจะใช้เฉพาะค่าที่ไม่ซ้ำเมื่อได้รับตัวบ่งชี้ทางสถิติเมื่อใด อาร์กิวเมนต์ของฟังก์ชันรวมสามารถใช้ พารามิเตอร์ที่แตกต่าง- อื่น พารามิเตอร์ทั้งหมดเป็นค่าเริ่มต้นและถือว่าค่าที่ส่งคืนทั้งหมดในคอลัมน์ถูกนับ ผู้ดำเนินการ
หากเราต้องการได้รับจำนวนพีซีรุ่นที่ผลิต ทุกคนผู้ผลิตคุณจะต้องใช้ จัดกลุ่มตามข้อตามด้วยวากยสัมพันธ์ ข้อไหน.
ราคาเฉลี่ย
จัดกลุ่มตามข้อใช้เพื่อกำหนดกลุ่มของสตริงเอาต์พุตที่สามารถนำมาใช้ได้ ฟังก์ชันรวม (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 จัดกลุ่มตามข้อจะถูกดำเนินการกับแถวแบบสอบถามผลลัพธ์ทั้งหมด หากคำขอประกอบด้วย แต่ละชุดแถวที่มีค่าเท่ากันของคอลัมน์หรือกลุ่มคอลัมน์ที่ระบุจัดกลุ่มตามข้อ ฟังก์ชันรวมจะดำเนินการสำหรับแต่ละกลุ่มแยกกัน
การกำหนดต้นทุนรวมของแต่ละผลิตภัณฑ์ในแต่ละเดือน
หลังจากนี้ ฟังก์ชันรวมจะถูกนำไปใช้กับแต่ละกลุ่ม โปรดทราบว่าสำหรับ GROUP BY ค่า NULL ทั้งหมดจะถือว่าเท่ากันนั่นคือ เมื่อจัดกลุ่มตามฟิลด์ที่มีค่า NULL แถวดังกล่าวทั้งหมดจะรวมอยู่ในกลุ่มเดียว ข้อไหนกำหนดภาคแสดงสำหรับการกรองแถวแล้ว มีข้อเสนอใช้ หลังจากจัดกลุ่มเพื่อกำหนดภาคแสดงที่คล้ายกันซึ่งกรองกลุ่มตามค่า ฟังก์ชันรวม- ข้อนี้จำเป็นในการตรวจสอบความถูกต้องของค่าที่ได้รับโดยใช้ ฟังก์ชันรวมไม่ใช่จากแต่ละแถวของแหล่งบันทึกที่กำหนดไว้ จากข้อและจาก กลุ่มสายดังกล่าว- จึงไม่สามารถบรรจุเช็คดังกล่าวได้ ข้อไหน.