ASC
(ascent),升序排序;DESC
(descent),降序排序。如果没有显式声明ORDER BY
的排序顺序,则默认是升序排序,相当于ORDER BY xxx ASC
。ORDER BY
可以使用列的别名,可以使用列的别名进行排序。SELECT ... FROM ... WHERE ... ORDER BY...
。ORDER BY
在WHERE
后面。ORDER BY
可以使用列的别名,WHERE
不能使用列的别名。- 多列排序,用逗号分隔。比如,
ORDER BY xxx DESC,yyy ASC
。
DESC降序,ASC升序
- 按照salary从高到低进行排序。
# 按照salary从高到低排序
SELECT last_name,salary
FROM employees
ORDER BY salary DESC;
- 按照salary从低到高进行排序。
# 按照salary从低到高排序
SELECT last_name,salary
FROM employees
ORDER BY salary ASC;
SELECT last_name,salary
FROM employees
ORDER BY salary;
ORDER BY 使用列的别名排序
# 按照年薪从高到低进行排序
SELECT last_name,salary,12*salary*(1+IFNULL(commission_pct,0)) annual_salary
FROM employees
ORDER BY annual_salary DESC;
ORDER BY 在 WHERE 后面
# department_id为10或20或30的员工的年收入从高到底排序
SELECT department_id,last_name,salary,12*salary*(1+IFNULL(commission_pct,0)) annual_salary
FROM employees
WHERE department_id IN (10,20,30)
ORDER BY annual_salary DESC;
以上MySQL语句的执行顺序为,
第1步:FROM
。
第2步:WHERE
。
第3步:SELECT
。
第4步:ORDER BY
。
ORDER BY 多列排序,用逗号分隔
- 按照department_id降序排列
# 显示员工信息,按照department_id降序排列
SELECT department_id,last_name,salary
FROM employees
ORDER BY department_id DESC;
- 按照department_id降序排列,再按照salary升序排列(department_id相同时,按照salary升序排列)
SELECT department_id,last_name,salary
FROM employees
ORDER BY department_id DESC,salary ASC;