排序 ORDER BY

概述

ORDER BY子句用于对查询结果进行排序，可以按照一个或多个列进行升序（ASC）或降序（DESC）排列。排序是SQL查询中最常用的功能之一。

基本语法

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

测试数据准备

-- 创建员工表
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(30),
    position VARCHAR(30),
    salary DECIMAL(10,2),
    hire_date DATE,
    age INT
);

-- 插入测试数据
INSERT INTO employees VALUES
(1, '张三', '技术部', '高级工程师', 12000, '2020-01-15', 28),
(2, '李四', '技术部', '工程师', 8000, '2021-03-20', 25),
(3, '王五', '技术部', '初级工程师', 6000, '2022-06-10', 23),
(4, '赵六', '销售部', '销售经理', 10000, '2019-08-05', 32),
(5, '钱七', '销售部', '销售代表', 7000, '2021-11-12', 26),
(6, '孙八', '销售部', '销售代表', 6500, '2022-02-28', 24),
(7, '周九', '人事部', '人事经理', 9000, '2020-05-18', 30),
(8, '吴十', '人事部', '人事专员', 5500, '2021-09-30', 22),
(9, '郑一', '财务部', '财务经理', 11000, '2019-12-01', 35),
(10, '王二', '财务部', '会计', 6800, '2022-01-20', 27);

-- 创建产品表
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(50),
    category VARCHAR(30),
    price DECIMAL(10,2),
    stock_quantity INT,
    created_date DATE
);

-- 插入产品数据
INSERT INTO products VALUES
(1, 'iPhone 14', '手机', 5999.00, 50, '2023-01-10'),
(2, 'MacBook Pro', '笔记本', 12999.00, 20, '2023-01-15'),
(3, 'iPad Air', '平板', 3999.00, 30, '2023-02-01'),
(4, 'AirPods Pro', '耳机', 1999.00, 100, '2023-02-10'),
(5, 'Apple Watch', '手表', 2999.00, 40, '2023-03-01');

单列排序

升序排序（ASC）

-- 按工资升序排列（ASC可以省略，默认为升序）
SELECT name, salary
FROM employees
ORDER BY salary ASC;

-- 省略ASC
SELECT name, salary
FROM employees
ORDER BY salary;

降序排序（DESC）

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

-- 按入职日期降序排列（最新入职的在前）
SELECT name, hire_date
FROM employees
ORDER BY hire_date DESC;

多列排序

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

-- 先按部门，再按职位，最后按工资
SELECT name, department, position, salary
FROM employees
ORDER BY department, position, salary DESC;

-- 复杂多列排序
SELECT name, department, age, salary, hire_date
FROM employees
ORDER BY 
    department ASC,     -- 部门升序
    age DESC,          -- 年龄降序
    salary DESC,       -- 工资降序
    hire_date ASC;     -- 入职日期升序

按表达式排序

计算字段排序

-- 按年薪排序
SELECT name, salary, salary * 12 as annual_salary
FROM employees
ORDER BY salary * 12 DESC;

-- 按工作年限排序
SELECT 
    name, 
    hire_date,
    DATEDIFF(CURDATE(), hire_date) / 365 as work_years
FROM employees
ORDER BY DATEDIFF(CURDATE(), hire_date) DESC;

条件表达式排序

-- 按工资等级排序
SELECT 
    name, 
    salary,
    CASE 
        WHEN salary >= 10000 THEN '高薪'
        WHEN salary >= 7000 THEN '中薪'
        ELSE '低薪'
    END as salary_level
FROM employees
ORDER BY 
    CASE 
        WHEN salary >= 10000 THEN 1
        WHEN salary >= 7000 THEN 2
        ELSE 3
    END;

按列位置排序

-- 按SELECT列表中的位置排序
SELECT name, department, salary
FROM employees
ORDER BY 2, 3 DESC;  -- 按第2列（department）和第3列（salary降序）排序

-- 注意：不推荐使用列位置，可读性差
-- 推荐使用列名
SELECT name, department, salary
FROM employees
ORDER BY department, salary DESC;

字符串排序

基本字符串排序

-- 按姓名字母顺序排序
SELECT name, department
FROM employees
ORDER BY name;

-- 按部门名称排序
SELECT name, department
FROM employees
ORDER BY department, name;

中文排序

-- MySQL中文排序（使用CONVERT函数）
SELECT name, department
FROM employees
ORDER BY CONVERT(name USING gbk);

-- 或者使用COLLATE
SELECT name, department
FROM employees
ORDER BY name COLLATE utf8mb4_unicode_ci;

字符串长度排序

-- 按姓名长度排序
SELECT name, LENGTH(name) as name_length
FROM employees
ORDER BY LENGTH(name), name;

日期时间排序

基本日期排序

-- 按入职日期排序
SELECT name, hire_date
FROM employees
ORDER BY hire_date;

-- 最新入职的员工在前
SELECT name, hire_date
FROM employees
ORDER BY hire_date DESC;

日期部分排序

-- 按入职月份排序（忽略年份）
SELECT name, hire_date, MONTH(hire_date) as hire_month
FROM employees
ORDER BY MONTH(hire_date), DAY(hire_date);

-- 按星期几排序
SELECT name, hire_date, DAYNAME(hire_date) as day_of_week
FROM employees
ORDER BY DAYOFWEEK(hire_date);

NULL值排序

-- 创建包含NULL值的测试表
CREATE TABLE test_null (
    id INT,
    name VARCHAR(50),
    score INT
);

INSERT INTO test_null VALUES
(1, '张三', 85),
(2, '李四', NULL),
(3, '王五', 92),
(4, '赵六', NULL),
(5, '钱七', 78);

-- 默认NULL值排序（MySQL中NULL值排在最前面）
SELECT * FROM test_null
ORDER BY score;

-- 将NULL值排在最后
SELECT * FROM test_null
ORDER BY score IS NULL, score;

-- 将NULL值排在最前
SELECT * FROM test_null
ORDER BY score IS NOT NULL, score;

-- 使用COALESCE处理NULL值
SELECT * FROM test_null
ORDER BY COALESCE(score, 0);  -- 将NULL替换为0

与其他子句结合

与WHERE结合

-- 查询技术部员工，按工资降序排列
SELECT name, salary
FROM employees
WHERE department = '技术部'
ORDER BY salary DESC;

-- 查询工资大于7000的员工，按部门和工资排序
SELECT name, department, salary
FROM employees
WHERE salary > 7000
ORDER BY department, salary DESC;

与GROUP BY结合

-- 按部门统计平均工资，按平均工资降序排列
SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;

-- 按部门统计员工数，按员工数和部门名排序
SELECT department, COUNT(*) as employee_count
FROM employees
GROUP BY department
ORDER BY employee_count DESC, department;

与LIMIT结合

-- 查询工资最高的3名员工
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

-- 查询工资第4-6名的员工
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3 OFFSET 3;

实战案例

案例1：电商产品排序

-- 创建订单表
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    product_id INT,
    customer_id INT,
    quantity INT,
    order_date DATE,
    total_amount DECIMAL(10,2)
);

-- 插入订单数据
INSERT INTO orders VALUES
(1, 1, 101, 2, '2023-01-15', 11998.00),
(2, 2, 102, 1, '2023-01-16', 12999.00),
(3, 3, 103, 1, '2023-02-10', 3999.00),
(4, 1, 104, 1, '2023-02-15', 5999.00),
(5, 4, 105, 3, '2023-03-05', 5997.00);

-- 产品销量排行榜
SELECT 
    p.product_name,
    p.category,
    SUM(o.quantity) as total_sold,
    SUM(o.total_amount) as total_revenue
FROM products p
JOIN orders o ON p.product_id = o.product_id
GROUP BY p.product_id, p.product_name, p.category
ORDER BY total_sold DESC, total_revenue DESC;

-- 按价格和库存综合排序
SELECT 
    product_name,
    price,
    stock_quantity,
    price * stock_quantity as inventory_value
FROM products
ORDER BY 
    CASE 
        WHEN stock_quantity = 0 THEN 1  -- 缺货的排在最后
        ELSE 0
    END,
    price DESC,
    stock_quantity DESC;

案例2：学生成绩排名

-- 创建学生成绩表
CREATE TABLE student_grades (
    student_id INT,
    student_name VARCHAR(50),
    subject VARCHAR(30),
    grade DECIMAL(5,2),
    exam_date DATE
);

-- 插入成绩数据
INSERT INTO student_grades VALUES
(1, '张三', '数学', 85.5, '2023-06-15'),
(1, '张三', '语文', 78.0, '2023-06-16'),
(1, '张三', '英语', 92.0, '2023-06-17'),
(2, '李四', '数学', 76.5, '2023-06-15'),
(2, '李四', '语文', 88.0, '2023-06-16'),
(2, '李四', '英语', 84.5, '2023-06-17');

-- 学生总分排名
SELECT 
    student_name,
    SUM(grade) as total_score,
    AVG(grade) as avg_score,
    COUNT(*) as subject_count
FROM student_grades
GROUP BY student_id, student_name
ORDER BY total_score DESC, avg_score DESC;

-- 各科成绩排名
SELECT 
    subject,
    student_name,
    grade,
    RANK() OVER (PARTITION BY subject ORDER BY grade DESC) as ranking
FROM student_grades
ORDER BY subject, ranking;

案例3：销售业绩分析

-- 创建销售记录表
CREATE TABLE sales_records (
    sale_id INT PRIMARY KEY,
    salesperson VARCHAR(50),
    region VARCHAR(30),
    sale_amount DECIMAL(10,2),
    sale_date DATE,
    customer_type VARCHAR(20)
);

-- 插入销售数据
INSERT INTO sales_records VALUES
(1, '张三', '华北', 15000, '2023-01-15', 'VIP'),
(2, '李四', '华南', 8000, '2023-01-20', '普通'),
(3, '王五', '华东', 12000, '2023-02-10', 'VIP'),
(4, '张三', '华北', 20000, '2023-02-15', 'VIP'),
(5, '赵六', '华西', 9000, '2023-03-05', '普通');

-- 销售员业绩排名
SELECT 
    salesperson,
    COUNT(*) as sale_count,
    SUM(sale_amount) as total_sales,
    AVG(sale_amount) as avg_sale,
    MAX(sale_amount) as max_sale
FROM sales_records
GROUP BY salesperson
ORDER BY total_sales DESC, avg_sale DESC;

-- 按地区和客户类型排序
SELECT 
    region,
    customer_type,
    COUNT(*) as sale_count,
    SUM(sale_amount) as total_sales
FROM sales_records
GROUP BY region, customer_type
ORDER BY 
    region,
    CASE customer_type 
        WHEN 'VIP' THEN 1 
        WHEN '普通' THEN 2 
        ELSE 3 
    END,
    total_sales DESC;

高级排序技巧

1. 自定义排序顺序

-- 按自定义的部门优先级排序
SELECT name, department, salary
FROM employees
ORDER BY 
    CASE department
        WHEN '技术部' THEN 1
        WHEN '销售部' THEN 2
        WHEN '财务部' THEN 3
        WHEN '人事部' THEN 4
        ELSE 5
    END,
    salary DESC;

-- 按职位等级排序
SELECT name, position, salary
FROM employees
ORDER BY 
    CASE 
        WHEN position LIKE '%经理%' THEN 1
        WHEN position LIKE '%高级%' THEN 2
        WHEN position LIKE '%工程师%' THEN 3
        ELSE 4
    END,
    salary DESC;

2. 随机排序

-- 随机排序（MySQL）
SELECT name, department
FROM employees
ORDER BY RAND()
LIMIT 5;

-- 随机排序（PostgreSQL）
-- ORDER BY RANDOM()

-- 随机排序（SQL Server）
-- ORDER BY NEWID()

3. 条件排序

-- 根据条件决定排序方向
SET @sort_direction = 'DESC';

SELECT name, salary
FROM employees
ORDER BY 
    CASE 
        WHEN @sort_direction = 'ASC' THEN salary
        ELSE NULL
    END ASC,
    CASE 
        WHEN @sort_direction = 'DESC' THEN salary
        ELSE NULL
    END DESC;

性能优化

1. 索引优化

-- 为排序列创建索引
CREATE INDEX idx_salary ON employees(salary);
CREATE INDEX idx_dept_salary ON employees(department, salary);

-- 复合索引的列顺序很重要
-- 如果经常按(department, salary)排序，创建复合索引
CREATE INDEX idx_dept_salary_name ON employees(department, salary, name);

2. 避免排序的方法

-- 如果只需要最大值，避免排序
-- 低效
SELECT salary FROM employees ORDER BY salary DESC LIMIT 1;

-- 高效
SELECT MAX(salary) FROM employees;

-- 如果只需要前几条记录，考虑使用索引
-- 确保有适当的索引支持ORDER BY + LIMIT

3. 大数据量排序优化

-- 对于大表，避免复杂的排序表达式
-- 低效
SELECT * FROM large_table
ORDER BY SUBSTRING(name, 1, 3), LENGTH(description);

-- 考虑预计算排序键
ALTER TABLE large_table ADD COLUMN sort_key VARCHAR(10);
UPDATE large_table SET sort_key = SUBSTRING(name, 1, 3);
CREATE INDEX idx_sort_key ON large_table(sort_key);

SELECT * FROM large_table
ORDER BY sort_key;

常见错误

1. 在GROUP BY中使用ORDER BY

-- 错误：ORDER BY列不在GROUP BY中
SELECT department, COUNT(*)
FROM employees
GROUP BY department
ORDER BY salary;  -- salary不在GROUP BY中

-- 正确：使用聚合函数
SELECT department, COUNT(*), AVG(salary) as avg_salary
FROM employees
GROUP BY department
ORDER BY avg_salary;

2. 字符串数字排序

-- 错误：字符串排序
SELECT * FROM (
    SELECT '1' as num UNION ALL
    SELECT '10' UNION ALL
    SELECT '2'
) t
ORDER BY num;  -- 结果：1, 10, 2

-- 正确：数值排序
SELECT * FROM (
    SELECT '1' as num UNION ALL
    SELECT '10' UNION ALL
    SELECT '2'
) t
ORDER BY CAST(num AS UNSIGNED);  -- 结果：1, 2, 10

3. NULL值处理不当

-- 注意NULL值的排序行为
-- 在不同数据库中NULL值的排序位置可能不同
-- 明确指定NULL值的排序位置
SELECT * FROM test_null
ORDER BY score IS NULL, score DESC;

最佳实践

1. 性能考虑

• 为经常排序的列创建索引
• 避免在ORDER BY中使用复杂表达式
• 对于大结果集，考虑分页查询

2. 可读性

• 使用列名而不是列位置
• 明确指定ASC或DESC
• 复杂排序逻辑添加注释

3. 数据一致性

• 注意NULL值的处理
• 考虑字符集对排序的影响
• 验证排序结果的正确性

4. 维护性

• 避免硬编码排序逻辑
• 使用参数化排序
• 文档化特殊排序规则

总结

ORDER BY是SQL查询中控制结果顺序的重要子句。正确使用ORDER BY可以让查询结果更有意义和可读性。在实际应用中，需要考虑性能影响，特别是在大数据量的情况下。通过合理的索引设计和查询优化，可以显著提高排序查询的性能。