จากบทความ Retrieving the last record in each group จะเป็นการ JOIN แบบเลือกรายการล่าสุด เช่นกรณีที่ข้อมูลในตารางหลัก มีการเปลี่ยนแปลงรุ่น เปลี่ยนเวอร์ชั่น แต่ยังคงใช้รหัสเดิม และต้องการแค่ข้อมูลเวอร์ชั่นล่าสุดเรคอร์ดเดียวเท่านั้น
นอกจากนี้ยังมีอีกกรณี นั่นก็คือ เมื่อเราย้อนกลับไปดูข้อมูลที่เคยบันทึกเอาไว้ โดยอ้างอิงรหัสเดียวกัน ที่ปัจจุบันได้เปลี่ยนเวอร์ชั่นไปแล้ว ข้อมูลที่ JOIN มาจะแสดงอย่างไร??? จะรู้ได้อย่างไรว่า ณ วันที่นั้นๆ ใช้ข้อมูลเรคอร์ดไหน???
(ถ้าเราเก็บไอดี PK ไปเลยจะไม่เจอปัญหานี้ แต่เนื่องจากฟิลด์ Code ดันเป็น Unique ID ซึ่งยึดเป็น PK ไปในตัวก็เลยคิดว่าไม่จำเป็นต้องสร้าง ID ขึ้นมาอีก)
ตัวอย่างข้อมูลที่เคยบันทึกไว้ด้วยชื่อหมวดหมู่ก่อนการเปลี่ยนแปลง
กรรไกรสองรายการ ที่ใช้หมวดหมู่เดียวกัน แต่คนละปี
กรรไกรตัดกิ่ง ณ วันที่ 2017-06-01 ได้ทำรายการด้วยชื่อ "วัสดุทำสวน"
กรรไกรเล็ก ณ วันที่ 2018-01-31 ได้ทำรายการด้วยชื่อ "วัสดุอุปกรณ์ทำสวน"
SELECT * FROM product
WHERE name LIKE '%กรรไกร%'
เรามาดูข้อมูลเมื่อ JOIN กับตารางหมวดหมู่กันดู
SELECT product.*,tb_category.name AS cate_name FROM product
LEFT JOIN tb_category ON product.category_code = tb_category.code
WHERE product.name LIKE '%กรรไกร%'
จะเห็นว่าได้ข้อมูลมาหลายเรคอร์ด และเรคอร์ดที่ตรงกับข้อมูลที่เคยทำรายการจริงก็คือแถวที่ 3 และ แถวที่ 6 ที่ลูกศรสีแดงชี้
เราจะทำการ GROUP รหัสสินค้า เพื่อลดจำนวนเรคอร์ดที่ซ้ำซ้อนออกไป
SELECT
product.*
, tb_category.name AS cate_name
, tb_category.start_used_date
FROM
product
LEFT JOIN
tb_category ON product.category_code = tb_category.code
WHERE
product.name LIKE '%กรรไกร%'
GROUP BY
product.id
ผลปรากฏว่า ชื่อหมวดหมู่ที่แสดงไม่ถูกต้อง เมื่อสังเกตวันที่ start_used_date จะเห็นว่า ได้ดึงแค่เรคอร์ดแรกมาเท่านั้น ไม่ได้ดึงตามช่วงเวลาของข้อมูลจริง
วิธีแก้ไขก็คือ เพิ่มเงื่อนไขเข้าไปในการ JOIN
โดยกำหนด Sub Query ให้เลือกเฉพาะรายการที่เปิดใช้งานก่อนนำไปใช้งานในครั้งนั้นๆ
SELECT
product.*
, cate.name AS cate_name
, cate.start_used_date
FROM
product
LEFT JOIN
tb_category cate ON cate.id = (
SELECT MAX(bb.id)
FROM tb_category AS bb
WHERE bb.code = product.category_code
AND bb.start_used_date <= product.active_date
)
WHERE
product.name LIKE '%กรรไกร%'
GROUP BY product.id
เมื่อเราเพิ่มเงื่อนไข start_used_date <= product.active_date เข้าไปใน Sub Query ก็จะได้ ID ของเรคอร์ดที่เปิดใช้งานในช่วง active_date ของรายการนั้นๆ ซึ่งตรงกับที่เกิดขึ้นจริง
หากเห็นว่าเป็นประโยชน์ หรือมีกรณีใกล้เคียงกัน ก็ลองเอาไปปรับใช้กันดูนะครับ
สำหรับแหล่งอ้างอิง Retrieving the last record in each group
