SQL Server : OUTER APPLY and CROSS APPLY

หลายคนคงเคยใช้คำสั่ง JOIN เพื่อทำการเชื่อมโยงตารางตั้งแต่ 2 ตารางขึ้นไป แล้วให้ได้ผลลัพธ์ที่ต้องการ แต่มีคำสั่ง APPLY ที่ทำงานได้ลักษณะเดียวกันกับ JOIN แต่มีความคล่องตัวและยืดหยุ่นกว่า ซึ่งจริง ๆ แล้วคำสั่ง APPLY เกิดขึ้นมานานมากตั้งแต่ SQL Server 2005 การทำงานจะเหมือนการใช้งานคำสั่ง JOIN แต่จะแตกต่างกันตรงที่ตารางด้านขวาของ APPLY สามารถประมวลผลภายใต้เงื่อน where ไขของตารางด้านซ้ายได้ หรือสามารถเขียนเป็น table-value function แล้วส่งค่ากลับมาเชื่อมโยงกับตารางฝั่งซ้ายมือก็ได้ เป็นต้น

การใช้ APPLY หลัก ๆ จะใช้อยู่ 2 แบบคือ OUTER APPLY กับ CROSS APPLY โดยที่

    OUTER APPLY เปรียบได้กับการใช้ LEFT JOIN

    CROSS APPLY เปรียบได้กับการใช้ INNER JOIN

ดังนั้นการนำไปใช้งาน สามารถเลือกใช้ได้ตามความเหมาะสม

หลายท่านคงมีคำถามในใจ แล้วเราจะเอาไปประยุกต์ใช้ในงานใดได้บ้าง 

ดังนั้น : ขอยกตัวอย่าง สัก 2 - 3 ตัวอย่าง เพื่อให้พอเห็นภาพ เพื่อท่าน ๆ จะได้นำไปประยุกต์ใช้ได้ตามต้องการ

 

 

ตัวอย่าง การใช้คำสั่ง JOIN เปรียบเทียบกับการใช้คำสั่ง APPLY

SELECT p.[prod_no], p.[prod_name], t1.[in_min], t1.[in_max], t2.[in_last]
  FROM [Products] p
OUTER APPLY (
SELECT MIN(t.[qty]) in_min, MAX(t.[qty]) in_max
FROM [Transactions] t
WHERE t.type = 'IN' AND t.[prod_no] = p.[prod_no] GROUP BY t.[prod_no] 
) t1
OUTER APPLY  (
SELECT TOP 1 t.[qty] in_last
FROM [Transactions] t
WHERE t.type = 'IN' AND t.[prod_no] = p.[prod_no] ORDER BY t.[tran_id] DESC
) t2

SQL SERVER : UPDATE JOIN

มีอยู่วันหนึ่งไปเจอคำถาม ซึ่งไม่เกี่ยวกับผู้เขียน แต่อดที่จะตอบไม่ได้

"ผมจะใช้คำสั่ง SQL เพื่อ Update Table A โดยเอายอดรวมจาก Select sum ของ Table B ไปใส่ใน Table A ที่มี Key ตรงกันกับ Table B ได้อย่างไรครับ"

คำถามแบบนี้ชอบที่ยื่นมือเข้าไปตอบ ถ้าพูดภาษาชาวบ้านเขาเรียกว่า เสือก 555

การกระทำแบบนี้ สามารถใช้คำสั่ง SQL ได้หลายตัว เช่น UPDATE Join หรือ MERGE ก็ได้
แต่.... คำถาม ต้องการแค่ปรับปรุงยอดรวมเฉย ๆ ก็เลยใช้ UPDATE ร่วมกับ JOIN แทนการใช้คำสั่ง MERGE แต่คำสั่ง MERGER จริง ๆ แล้วทำอะไรได้มากกว่า มันสามารถ INSERT, UPDATE, DELETE จากแหล่งข้อมูลอื่นมาใส่ตารางเป้าหมายได้ในคำสั่งเดียวเลย อ้าว... นอกเรื่องอีกแล้ว 

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

เพื่อให้เห็นภาพ จะขอยกตัวอย่างการปรับปรุงยอดสต๊อกสินค้า จากรายการรับ/จ่ายที่เกิดขึ้น

ให้สร้างตารางเพื่อทดสอบ ดังนี้

สร้างตาราง Products

CREATE TABLE [Products] (
    [prod_no] [nchar](10) NOT NULL,
    [prod_name] [nvarchar](100),
    [onhand] [numeric](10, 0),
    PRIMARY KEY ([prod_no]))

สร้างตาราง Transactions สำหรับเก็บรายการ รับ/จ่าย สินค้า

CREATE TABLE [Transactions] (
    [tran_id] [int] IDENTITY(1,1) NOT NULL,
    [prod_no] [nchar](10),
    [type] [nchar](3),
    [qty] [numeric](10, 0),
    PRIMARY KEY ([tran_id])) 

ทำการเพิ่มข้อมูลตัวอย่างเข้าสู่ตาราง

INSERT INTO [Products] VALUES ('001','วิทยุทรานซิสเตอร์ ธานินทร์',0),
('002','ถ่านไฟฉาย ตรากบ',0),
('003','ยาแก้ปวด ทันใจ',0),
('004','บะหมี่กึ่งสำเร็จรูป มาม่า',0),
('005','นมข้นหวาน ตราหมี',0) ;

INSERT INTO [Transactions] VALUES('001','IN',20),
('003','IN',10),
('002','IN',100),
('005','IN',50),
('002','OUT',10),
('001','OUT',1),
('001','OUT',2),
('003','IN',15),
('003','OUT',10),
('001','OUT',2) ;

หลังจากทำการเพิ่มข้อมูลเรียบร้อยแล้ว ลองทำการคิวรีข้อมูลออกมาแสดงจะได้ ดังนี้

SELECT * FROM [Products]
SELECT * FROM [Transactions]

ให้ผู้อ่านลองดูข้อมูลในตาราง Transactions จะเห็นว่าข้อมูลจะมีคอลัมน์ type ซึ่งเก็บสถานะการรับ (in) และจ่าย (out) หากเราจะหาผลรวมที่เป็นยอดคงเหลือโดยใช้คำสั่ง SUM(qty) ก็จะมีปัญหาเนื่องจากยอดในคอลัมน์ qty มีค่าเป็นบวกหมด ดังนั้นเราต้องทำการแปลงเป็นค่า บวก หรือ ลบ โดยถ้า type มีค่าเป็น IN ให้ยอด qty มีค่าเป็นบวก แต่ถ้า type มีค่าเป็น OUT ก็ให้ยอด qty มีค่าเป็นลบ เพื่อจะได้หายอดคงเหลือของการรับ/จ่ายได้ สามารถเขียนคำสั่งได้ ดังนี้

SELECT * FROM [Transactions] ORDER BY [prod_no], [type]

SELECT [prod_no], SUM(CASE WHEN [type]='IN' THEN [qty] ELSE [qty] * -1 END) AS [onhand]  FROM [Transactions] GROUP BY [prod_no]

จากคำสั่ง SUM(CASE WHEN [type]='IN' THEN [qty] ELSE [qty] * -1 END) ข้างต้นที่เราจะได้ยอดคงเหลือมาแสดงเรียบร้อย

ขั้นต่อมาคือนำไปปรับปรุงยอดในคอลัมน์ onhand ของตาราง Products ซึ่งการทำเราจะใช้คำสั่ง UPDATE ร่วมกับ JOIN เพื่อปรับปรุงยอด ดังนี้

--ปรับปรุงข้อมูลตาราง Products ในคอลัมน์ onhand จากยอด SUM ของตาราง Transactions 
UPDATE P SET P.[onhand] = T.[onhand]
    FROM [Products] P
    INNER JOIN (SELECT [prod_no], SUM(CASE WHEN [type]='IN' THEN [qty]  ELSE [qty] * -1  END) AS [onhand] 
    FROM [Transactions] GROUP BY [prod_no]) T 
    ON P.[prod_no] = T.[prod_no]

--แสดงยอดที่ปรับปรุงแล้วของตาราง Products
SELECT * FROM [Products]

จะเห็นว่าการใช้ UPDATE JOIN ช่วยได้มากเลย คำสั่ง SQL เชื่อมโยงรหัส (prod_no) ที่ตรงกันแล้วทำกันปรับปรุงยอดให้ทำให้เรามันใจว่าอย่างไรเสียข้อมูลไม่มีวันผิดพลาด

สวัสดี ผลสลากกินแบ่ง งวด 1 พย. 65 รางวัลที่ 1 ..... 913106