มีอยู่วันหนึ่งไปเจอคำถาม ซึ่งไม่เกี่ยวกับผู้เขียน แต่อดที่จะตอบไม่ได้
"ผมจะใช้คำสั่ง 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
ไม่มีความคิดเห็น:
แสดงความคิดเห็น