题目:获取Employees的first_name,查询按照first_name的最后两个字母,按照升序进行排列
建表
create table `employees` (
`emp_no` int (11),
`birth_date` date ,
`first_name` varchar (42),
`last name` varchar (48),
`gender` char (3),
`hire_date` date
);
插入数据
insert into `employees` (`emp_no`, `birth_date`, `first_name`, `last name`, `gender`, `hire_date`) values('11','2021-04-10','tom','li','f','2021-04-10');
insert into `employees` (`emp_no`, `birth_date`, `first_name`, `last name`, `gender`, `hire_date`) values('12','2021-04-10','mncc','ddd','f','2021-04-10');
insert into `employees` (`emp_no`, `birth_date`, `first_name`, `last name`, `gender`, `hire_date`) values('13','2021-04-10','bob','bt','f','2021-04-10');
insert into `employees` (`emp_no`, `birth_date`, `first_name`, `last name`, `gender`, `hire_date`) values('14','2021-04-10','curry','uy','f','2021-04-10');
表数据展示
查询first_name
并按最后两个字符排序
SELECT `first_name` FROM `employees`
ORDER BY SUBSTRING(`first_name`, -2)
结果展示
扩展1:降序排列
SELECT `first_name` FROM `employees`
ORDER BY SUBSTRING(`first_name`, -2) DESC
扩展2:排序整个表
SELECT * FROM `employees` ORDER BY SUBSTRING(`first_name`, -2) DESC