ORDER BY 是 SQL 中用于对查询结果进行排序的关键子句,它能让数据按照特定顺序呈现,提高数据的可读性和分析效率。
SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;
升序排序 (ASC)
降序排序 (DESC)
可以指定多个排序列,优先级从左到右:
SELECT employee_id, first_name, last_name, salary, hire_date
FROM employees
ORDER BY department_id ASC, salary DESC;
SELECT product_name, price, quantity, (price * quantity) AS total_value
FROM products
ORDER BY total_value DESC;
可以使用列在选择列表中的位置代替列名:
SELECT first_name, last_name, salary
FROM employees
ORDER BY 3 DESC, 2 ASC; -- 先按salary(第3列)降序,再按last_name(第2列)升序
自定义排序顺序:
SELECT product_name, category
FROM products
ORDER BY
CASE category
WHEN 'Electronics' THEN 1
WHEN 'Clothing' THEN 2
WHEN 'Books' THEN 3
ELSE 4
END;
NULL值处理:
SELECT employee_id, commission_pct
FROM employees
ORDER BY commission_pct DESC NULLS LAST;
-- 按日期降序查看最近的订单
SELECT order_id, customer_id, order_date, total_amount
FROM orders
ORDER BY order_date DESC;
-- 按部门分组后按薪资降序排列员工
SELECT department_id, employee_id, last_name, salary
FROM employees
ORDER BY department_id, salary DESC;
-- 按名称长度排序产品
SELECT product_name, LENGTH(product_name) AS name_length
FROM products
ORDER BY name_length DESC;
掌握ORDER BY子句能让你更有效地组织和呈现数据,是SQL查询中不可或缺的技能。