2020-07-26

排序查询

#一、按单个字段进行排序
select * from employees order by salary asc;
#二、按多个字段进行排序
select * from employees order by salary desc, department_id asc;
#三、按表达式排序#案例：按年薪降序
select salary * 12 *(1+IFNULL(commission_pct,0)) from employees order by salary * 12 *(1+IFNULL(commission_pct,0)) desc;
#案例：按姓名中的字节长度大小降序
select * from employees order by LENGTH(first_name) desc;
#1.查询员工的姓名和部门号和年薪，按年薪降序 按姓名升序
select first_name,department_id,salary * 12 * (1+IFNULL(commission_pct,0)) from employees order by salary * 12 * (1+IFNULL(commission_pct,0)) desc, first_name asc;
#2.选择工资不在8000到17000的员工的姓名和工资，按工资降序
select first_name,salary from employees where salary between 8000 and 17000 order by salary desc;
#3.查询邮箱中包含e的员工信息，并先按邮箱的字节数降序，再按部门号升序
select * from employees where email like ‘%e%’ order by LENGTH(email),department_id;