home

หน้าแรก
 Computer >> บทช่วยสอนคอมพิวเตอร์ >  >> การเขียนโปรแกรม >> MySQL

เรียงลำดับคอลัมน์ข้อความเพื่อหาค่าสูงสุดใน MySQL (Text/Number Mixed)

ปัญหา: เรียงข้อความที่ผสมตัวเลข (Natural Sort)

คอลัมน์ TEXT หรือ VARCHAR ที่มีค่าแบบ 'STUDENT78', 'STUDENT98', 'John89' การเรียง ORDER BY column DESC แบบปกติจะเรียงแบบ Lexicographic (ตัวอักษร) ทำให้ 'STUDENT98' < 'STUDENT78' เพราะ '9' > '7' แต่ 'STUDENT98' มีหลักมากกว่า

วิธีแก้: ดึงตัวเลขท้ายออกมา แปลงเป็นตัวเลข แล้วเรียง

ใช้ RIGHT(), LENGTH(), CAST(... AS UNSIGNED)

สร้างตารางและข้อมูล

mysql> CREATE TABLE DemoTable1487 (StudentCode TEXT);
Query OK, 0 rows affected (0.91 sec)

mysql> INSERT INTO DemoTable1487 VALUES
    -> ('560'), ('789'), ('STUDENT78'), ('John89'), ('Carol101'), ('STUDENT98');
Query OK, 6 rows affected

ข้อมูลทั้งหมด

+-------------+
| StudentCode |
+-------------+
| 560         |
| 789         |
| STUDENT78   |
| John89      |
| Carol101    |
| STUDENT98   |
+-------------+

คำสั่ง: หาค่า STUDENTxxx ที่มีตัวเลขท้ายมากที่สุด

mysql> SELECT * FROM DemoTable1487 
    -> WHERE StudentCode LIKE 'STUDENT%' 
    -> ORDER BY CAST(RIGHT(StudentCode, LENGTH(StudentCode) - LENGTH('STUDENT')) AS UNSIGNED) DESC 
    -> LIMIT 1;

ผลลัพธ์

+-------------+
| StudentCode |
+-------------+
| STUDENT98   |
+-------------+

แยกวิเคราะห์สูตร

RIGHT(StudentCode, LENGTH(StudentCode) - LENGTH('STUDENT'))
-- LENGTH('STUDENT') = 7
-- LENGTH('STUDENT98') = 9
-- RIGHT('STUDENT98', 9-7) = RIGHT('STUDENT98', 2) = '98'
-- CAST('98' AS UNSIGNED) = 98 (ตัวเลข)

วิธีทางเลือก (MySQL 8.0+): ใช้ REGEXP_SUBSTR

SELECT * FROM DemoTable1487 
WHERE StudentCode REGEXP '^STUDENT[0-9]+$'
ORDER BY CAST(REGEXP_SUBSTR(StudentCode, '[0-9]+$') AS UNSIGNED) DESC
LIMIT 1;

วิธีทางเลือก: ใช้ SUBSTRING_INDEX (ถ้ามี Delimiter ชัดเจน)

-- ถ้ารูปแบบเป็น STUDENT_98
ORDER BY CAST(SUBSTRING_INDEX(StudentCode, '_', -1) AS UNSIGNED) DESC

สรุป

  • ต้องแยกส่วนตัวเลขออกมาก่อน แล้ว CAST เป็นตัวเลข
  • ใช้ UNSIGNED สำหรับตัวเลขบวก (เร็วกว่า SIGNED)
  • LIMIT 1 เพื่อ取ค่าสูงสุดเพียงค่าเดียว