SQLite Order By

SQLite 的 ORDER BY 子句 用于对查询结果进行排序，可以按一个或多个列升序（ASC）或降序（DESC）排列。

---

基本语法

SELECT column1, column2, ...
FROM table_name
[WHERE ...]
[ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...]
[LIMIT ...];

• 默认排序：ASC（升序）
• 支持多列排序：从左到右依次排序
• 可结合表达式、函数、别名

---

示例表结构

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    department TEXT,
    salary REAL,
    hire_date DATE,
    active INTEGER  -- 1=在职
);

INSERT INTO employees (name, department, salary, hire_date, active) VALUES
('Alice', 'IT',        8500, '2023-06-15', 1),
('Bob',   'HR',        6200, '2022-09-10', 1),
('Cathy', 'IT',        9200, '2021-03-20', 1),
('David', 'Finance',   7800, '2024-01-05', 1),
('Eve',   'IT',        8800, '2023-11-01', 0),
('Frank', 'HR',        5800, '2025-02-28', 1);

---

1. 单列排序

-- 按薪资降序排列
SELECT name, salary FROM employees
ORDER BY salary DESC;

结果：

name  | salary
------|-------
Cathy | 9200
Eve   | 8800
Alice | 8500
David | 7800
Bob   | 6200
Frank | 5800

---

2. 多列排序（优先级从左到右）

-- 先按部门升序，再按薪资降序
SELECT name, department, salary FROM employees
ORDER BY department ASC, salary DESC;

结果：

name  | department | salary
------|------------|-------
David | Finance    | 7800
Bob   | HR         | 6200
Frank | HR         | 5800
Cathy | IT         | 9200
Eve   | IT         | 8800
Alice | IT         | 8500

---

3. 使用列位置（不推荐）

-- 按第 3 列（salary）降序
SELECT name, department, salary FROM employees
ORDER BY 3 DESC;

警告：可读性差，避免使用

---

4. 按表达式或函数排序

-- 按姓名长度降序
SELECT name, LENGTH(name) AS len FROM employees
ORDER BY LENGTH(name) DESC;

-- 按入职年份排序
SELECT name, hire_date FROM employees
ORDER BY STRFTIME('%Y', hire_date);

---

5. 按别名排序（SQLite 支持！）

SELECT name, salary * 12 AS annual_salary
FROM employees
ORDER BY annual_salary DESC;

---

6. 空值排序规则

• NULL 值默认排在最前（ASC）或最后（DESC）

-- 让 NULL 排在最后（即使是 DESC）
SELECT name, salary FROM employees
ORDER BY salary DESC NULLS LAST;

提示：SQLite 3.30.0+ 支持 NULLS FIRST/LAST

---

7. 结合 WHERE 和 LIMIT

-- IT 部门在职员工，薪资前 3 高
SELECT name, salary FROM employees
WHERE department = 'IT' AND active = 1
ORDER BY salary DESC
LIMIT 3;

---

8. 随机排序

-- 随机打乱顺序
SELECT name FROM employees
ORDER BY RANDOM();

---

9. 性能优化

-- 为排序列建立索引
CREATE INDEX idx_dept_salary ON employees(department, salary);
CREATE INDEX idx_hire_date ON employees(hire_date);

ORDER BY 若走索引，性能大幅提升！

---

10. 常见错误

错误	正确
ORDER BY name, salary（无方向）	ORDER BY name ASC, salary DESC
ORDER BY 在子查询外	必须在最外层 SELECT
排序字段不在 SELECT 列表	允许，但不推荐

---

快速参考

需求	SQL
降序	ORDER BY col DESC
多列	ORDER BY col1, col2 DESC
按函数	ORDER BY UPPER(name)
随机	ORDER BY RANDOM()
分页	ORDER BY id LIMIT 10 OFFSET 20
NULL 最后	ORDER BY col DESC NULLS LAST

---

最佳实践

1. 始终显式写 ASC/DESC → 避免歧义
2. 排序列建索引 → 尤其多列排序
   3.
3. 大结果集 + LIMIT 搭配使用

---

需要我帮你写一个针对你表的 ORDER BY 查询吗？
请提供表名、想排序的字段和顺序！