Sql customername และ ม ลค ารวมเพ ยง 1 record

ทั้งนี้ สามารถอ่านเพิ่มเติมได้จาก Wikipedia https://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL

สำหรับบทความนี้ จะกล่าวถึง CTEs ที่ใช้ใน Microsoft SQL Server เท่านั้น

CTEs ถือเป็นนิพจน์ตาราง (Table Expression) แบบหนึ่ง นิพจน์ตาราง คือ นิพจน์ที่ทำหน้าที่เสมือนตารางได้

สังเกตว่าอะไรที่สามารถประกาศได้ในประโยค FROM ของคำสั่ง SELECT ได้ นั่นก็คือ นิพจน์ตาราง ซึ่งมีทั้งที่สามารถจัดเก็บลงในฐานข้อมูลได้ และจัดเก็บไม่ได้

นิพจน์ตารางที่จัดเก็บเป็น Object ลงในฐานข้อมูล

Views
Table-Valued Functions (หรือ Parameterized Views ในกรณี Oracle)

ผู้อ่านน่าจะคุ้นเคยกับ View เป็นอย่างดี ผู้เขียนจะอธิบายคร่าว ๆ เกี่ยวกับ View ดังนี้ หากผู้เขียนสร้างคำสั่ง SELECT ของตนเองขึ้นมา อาทิเช่น

USE AdventureWorks; GO

SELECT E.BusinessEntityID as EmpID , P.FirstName , P.MiddleName , P.LastName , E.BirthDate , E.HireDate , E.Gender , E.MaritalStatus , A.AddressLine1 , A.AddressLine2 , A.City , SP.StateProvinceCode as State , SP.CountryRegionCode as Country FROM HumanResources.Employee as E INNER JOIN Person.Person as P ON E.BusinessEntityID=P.BusinessEntityID INNER JOIN Person.BusinessEntityAddress as BA ON P.BusinessEntityID=BA.BusinessEntityID INNER JOIN Person.Address as A ON BA.AddressID=A.AddressID INNER JOIN Person.StateProvince as SP ON A.StateProvinceID=SP.StateProvinceID;

ผลลัพธ์ที่ได้จากคำสั่ง SELECT นี้คือ

จากนั้นผู้เขียนต้องการจะสร้างคำสั่ง SELECT นี้ให้กลายเป็น View (ก่อนอื่นจะต้องมีสิทธิในการสร้าง View เสียก่อน อาจได้รับมาจาก Fixed Database Role ชื่อว่า db_ddladmin ก็ได้)

ผู้เขียนก็เพียงแค่เพิ่มคำสั่ง CREATE VIEW เหนือคำสั่ง SELECT โดย View ที่สร้างชื่อว่า vEmployees

เพียงเท่านี้ก็จะเป็นการสร้าง View แล้วดังแสดง

USE AdventureWorks; GO

CREATE VIEW HumanResources.vEmployees as SELECT E.BusinessEntityID as EmpID , P.FirstName , P.MiddleName , P.LastName , E.BirthDate , E.HireDate , E.Gender , E.MaritalStatus , A.AddressLine1 , A.AddressLine2 , A.City , SP.StateProvinceCode as State , SP.CountryRegionCode as Country FROM HumanResources.Employee as E INNER JOIN Person.Person as P ON E.BusinessEntityID=P.BusinessEntityID INNER JOIN Person.BusinessEntityAddress as BA ON P.BusinessEntityID=BA.BusinessEntityID INNER JOIN Person.Address as A ON BA.AddressID=A.AddressID INNER JOIN Person.StateProvince as SP ON A.StateProvinceID=SP.StateProvinceID;

เมื่อสร้าง View เสร็จเรียบร้อยแล้ว View ดังกล่าวจะถูกจัดเก็บลงในฐานข้อมูล เราสามารถจัดการกับ View อาทิการให้สิทธิ์การเข้าถึง ผู้ใช้ที่มีสิทธิ์ก็จะสามารถเรียกใช้ View ได้ ด้วยการ Select ข้อมูลจาก View ดังแสดง

USE AdventureWorks; GO

SELECT * FROM HumanResources.vEmployees;

ผลลัพธ์ที่ได้จากคำสั่ง SELECT นี้คือ

สรุปได้ว่า View ได้กลายเป็น Object ในฐานข้อมูลเรียบร้อยแล้ว ผู้ใช้คนใดที่มีสิทธิ์ก็เรียกใช้ได้

ส่วนนิพจน์ตารางแบบจัดเก็บลงฐานข้อมูลอีกอย่างหนึ่งคือ Table-Valued Functions หรือ TVFs นั้น ผู้เขียนขอยกตัวอย่างอย่างง่ายผ่าน Inline TVF หรือ TVF ที่จบในหนึ่งบรรทัดดังแสดง

USE AdventureWorks; GO

CREATE FUNCTION HumanResources.udfEmployees (@pamCountry as nchar(2)) RETURNS TABLE as RETURN SELECT E.BusinessEntityID as EmpID , P.FirstName , P.MiddleName , P.LastName , E.BirthDate , E.HireDate , E.Gender , E.MaritalStatus , A.AddressLine1 , A.AddressLine2 , A.City , SP.StateProvinceCode as State , SP.CountryRegionCode as Country FROM HumanResources.Employee as E INNER JOIN Person.Person as P ON E.BusinessEntityID=P.BusinessEntityID INNER JOIN Person.BusinessEntityAddress as BA ON P.BusinessEntityID=BA.BusinessEntityID INNER JOIN Person.Address as A ON BA.AddressID=A.AddressID INNER JOIN Person.StateProvince as SP ON A.StateProvinceID=SP.StateProvinceID WHERE SP.CountryRegionCode=@pamCountry;

ที่เรียกว่า Table-Valued Function ก็เพราะเป็นฟังก์ชั่นที่คืนค่าออกมาเป็นตาราง ซึ่งสามารถนำไปประกาศหลังประโยค FROM ในคำสั่ง SELECT ได้จึงถือว่าเป็นนิพจน์ตาราง ดังแสดง

USE AdventureWorks; GO

SELECT * FROM HumanResources.udfEmployees('GB');

ผลลัพธ์ที่ได้จากคำสั่ง SELECT นี้คือ

จะเห็นว่าเมื่อสร้างฟังก์ชันด้วยคำสั่ง CREATE FUNCTION ฟังก์ชันดังกล่าวจะถูกจัดเก็บลงฐานข้อมูล และสามารถให้สิทธิ์การเข้าถึงฟังก์ชันดังกล่าวแก่ผู้ใช้ได้ และผู้ใช้ที่มีสิทธิ์ก็สามารถเรียกใช้ฟังก์ชันดังกล่าวได้ เช่นเดียวกันกับ VIEW

แต่ที่ต่างกันก็คือ เราสามารถส่งพารามิเตอร์ เข้าไปข้างในฟังก์ชันเพื่อกรองข้อมูลตามพารามิเตอร์ที่ส่งเข้าไป ทำให้เกิดความ Dynamic มากขึ้น ซึ่งจากตัวอย่าง จะกรองข้อมูลให้แสดงเฉพาะ Record ที่ Country เป็น GB เท่านั้น

ใน Oracle ถึงเรียกสิ่งนี้ว่า Parameterized View หรือ View ที่สามารถส่งพารามิเตอร์ได้นั่นเอง

นิพจน์ตารางที่ไม่จัดเก็บลงฐานข้อมูล