Sắp xếp theo chuỗi cụ thể trong MySQL

Tạo bảng:

mysql> create table DemoTable    -> (    -> FirstName varchar(100)    -> ); Query OK, 0 rows affected (0.53 sec)

Chèn dữ liệu:

mysql> insert into DemoTable values('John'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('Adam'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values('Sam'); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable values('Johnny'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values('Joy'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values('Jace'); Query OK, 1 row affected (0.20 sec)

Hiển thị dữ liệu:

mysql> select *from DemoTable;

Kết quả

+-----------+ | FirstName | +-----------+ | John      | | Adam      | | Sam       | | Johnny    | | Joy       | | Jace      | +-----------+ 6 rows in set (0.00 sec)

Sắp xếp ưu tiên chuỗi con 'Jo' (2 ký tự đầu):

mysql> select *from DemoTable    -> order by case when substring(FirstName, 1, 2) = 'Jo' then 0 else 1 end;

Kết quả

+-----------+ | FirstName | +-----------+ | John      | | Johnny    | | Joy       | | Adam      | | Sam       | | Jace      | +-----------+ 6 rows in set (0.00 sec)