sql: บันทึกค้นข้อมูลแบบให้น้ำหนัก

ขออนุญาตบันทึกการทดลองเพื่อเอาไว้ดูครับ

สมมุติถ้ามีตาราง t_phone เอาไว้เก็บข้อมูลลูกค้า มีโครงสร้างคือ

CREATE TABLE t_phone 
(   phoneid	SERIAL,
    custid      VARCHAR(10),
    name        VARCHAR(128),
    phone       VARCHAR(64),
    cat         VARCHAR(64),
    rem         TEXT
);

ถ้าเราต้องการค้นข้อมูลจากฟิลด์ rem เป็นข้อมูลสองชุด เช่น
ค้นว่าในฟิลด์ rem ต้องมีข้อมูลว่า 'S1' และ/หรือ 'S2' ประกอบอยู่
เราอาจใช้เงื่อนไขคือ

SELECT name, phone, cat, rem FROM t_phone
WHERE rem LIKE "%S1%S2"
    OR rem LIKE "%S1%"
    OR rem LIKE "%S2%"

ข้อมูลที่ได้จะเป็นข้อมูลผสม โดยไม่สามารถเรียงลำดับความสำคัญของข้อมูลได้

ในที่นี้ความต้องการคือ ต้องการให้เรียงลำดับความสำคัญว่า ถ้าพบข้อมูล "S1" และ "S2" อยู่ด้วยกัน จะมีความสำคัญมากกว่า แต่ถ้าพบเพียงข้อมูลตัวเดียว จะให้มีความสำคัญน้อยกว่า

ในที่นี้จึงต้องใช้คำสั่ง CASE เข้ามาช่วย โดยเพิ่มฟิลด์ score เขามาในการจัดเรียง
คำสั่งจะกลายเป็น

SELECT
CASE WHEN rem LIKE '%S1%S2%' THEN 4
  WHEN rem LIKE '%S1%' THEN 2
  WHEN rem LIKE '%S2%' THEN 2
  ELSE 0
END AS score,
name, phone, cat, rem FROM t_phone
WHERE rem LIKE "%S1%"
    OR rem LIKE "%S2%"
ORDER BY score DESC

เราจะได้ผลลัพธ์ที่ถูกเรียงลำดับตามความสำคัญเรียบร้อย

อีกวิธีนึงคือใช้ UNION ALL

SELECT SUM(score) AS score, name, phone, rem FROM
(
  SELECT 1 AS score, name, phone, rem FROM t_phone 
  WHERE rem LIKE '%S1%' 
UNION ALL
  SELECT 1 AS score, name, phone, rem FROM t_phone
  WHERE rem LIKE '%S2%' 
) AS a
GROUP BY a.name, a.phone, a.rem
ORDER BY score DESC

วิธีหลังดูจะช้ากว่า แต่ก็น่าจะเขียนโค๊ดง่ายกว่า