การเรียง sql order ตัวเลขแบบมนุษย์

ปกติแล้วเวลาที่เราเขียนคำสั่ง sql ให้เรียงตามมากไปน้อย หรือ น้อยไปมาก เช่น

SELECT * FROM `products` ORDER BY `id` ASC

หากช่องเก็บ id เป็น varchar/text ผลลัพธ์จะออกมาเป็น 1, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 2, 20, 21, 22, 23

มันไม่เรียงตามจำนวนจากมากไปน้อย เนื่องจาก computer มันเรียงของมันอย่างนั้น (หากย้อนไป Windows ยุคเก่าๆมันก็เรียงอย่างนี้เหมือนกัน)

ซึ่งทั้งๆที่เราระบุไว้แล้วว่าเรียงจากน้อยไปมาก (asc)

วิธีแก้

วิธีที่ 1

เพิ่ม ABS() เข้าไปใน field ที่จะสั่งให้เรียง เช่น

SELECT * FROM `products` ORDER BY ABS(`id`) ASC

ซึ่ง ABS() นี้ก็คือ absolute value หรือค่าจริงนี่เอง (อ้างอิงจาก https://dev.mysql.com/doc/refman/8.0/en/mathematical-functions.html). วิธีนี้มีข้อจำกัดคือ ค่าของตัวเลขในตารางต้องไม่มีค่าผสมกันระหว่าง + กับ - เช่น -1, 0, 1, 2 แบบนี้จะเรียงไม่ถูก.

วิธีที่ 2

ใช้ +0 เข้าไป เช่น

SELECT * FROM `products` ORDER BY `id`+0 ASC

วิธีที่ 3

ใช้ CAST()

SELECT * FROM `products` ORDER BY CAST(`id` AS SIGNED) ASC

CAST() คือแปลงค่าให้เป็นประเภทที่ระบุ (อ้างอิงจาก https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html#function_cast).

คราวนี้ผลลัพธ์ที่จะออกมาก็ควรจะเป็น 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23 ตามการเรียงเลขแบบที่มนุษย์เข้าใจ

เทคนิคนี้สามารถใช้ได้ทั้ง microsoft sql และ mysql

One comment on “การเรียง sql order ตัวเลขแบบมนุษย์”

  • ปกรณ์ เพชระ บอกว่า:

    ผมกำลังตามหาเลยครับ
    พอดีจะเรียง 1.1.1, 1.1.2, 1.1.10
    แล้วมันเรียงแบบมั่วๆ มาเป็น 1.1.1, 1.1.10, 1.1.2
    ขอบคุณมากๆ เลยครับ

    ตอบกลับ

ใส่ความเห็น

อีเมลของคุณจะไม่แสดงให้คนอื่นเห็น ช่องข้อมูลจำเป็นถูกทำเครื่องหมาย *

คุณอาจใช้แท็กHTMLและแอททริบิวต์เหล่านี้: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>