手把手教MySQL查询：语法、案例、真题 (4. 排序查询)

4. 排序查询

1. 语法
	order by 排序列表【ASC|DESC】

2. 特点
	* 排序列表：支持单个字段、多个字段、表达式、函数、别名
    * ASC：升序，默认的；DESC：降序
	* 如果有多个排序条件，则当前边的条件值一样时，才会判断第二条

3.案例
	
	-- 查询员工的姓名、部门号、年薪，按年薪降序，姓名升序
	select last_name,department_id,salary*12*(1+ifnull(commission_pct,0)) 年薪
	from employees
	order by 年薪 DESC，last_name ASC;
	
	select last_name,department_id,salary*12*(1+ifhull(commission_pct,0)) 年薪
	from employees
	order by salary*12*(1+ifnull(commission_pct,0)) DESC，last_name ASC;
	
	-- 选择工资不在8000到17000的员工的姓名和工资，按工资降序
	select concat(last_name,first_name) 姓名,salary
	from employees
	where salary not between 8000 and 17000
	order by salary desc;
	
	--查询邮箱中包含e的员工信息，并先按邮箱的字节数降序，再按部门号升序
	select *,length(email)
	from employees
	where email like '%e%'
	order by length(email) desc,department_id asc;
	
	-- 查询最低平均工资的部门信息
	select *
	from departments 
	where department_id = (
		select department_id
		from employees
		group by department_id
		order by avg(salary)
		limit 0,1
	);
	
	select d.*
	from departments d
	inner join employees e
	on d.department_id = e.department_id
	where e.salary = (
		select min(avg(salary))
		from employees
		group by department_id
	);
	
	select d.* 
	from department d
	inner join (
		select avg(salary) ag,department_id
		from employees
		group by department_id
	) a
	on a.department_id=d.department_id
	order by a.ag
	limit 0,1;