数据库

SQL高级查询：提升数据检索效率的技巧

本文详细介绍SQL高级查询技巧，包括复杂连接查询、子查询、窗口函数、CTE等，帮助你提升数据检索效率。

SQL高级查询：提升数据检索效率的技巧

引言

结构化查询语言（SQL）是数据库操作的标准语言，掌握SQL的基本语法只是开始。在实际应用中，我们经常需要处理复杂的数据查询场景，如多表连接、数据汇总、高级过滤等。本文将详细介绍SQL的高级查询技巧，帮助你提升数据检索效率，更好地应对复杂的数据处理需求。

连接查询（JOIN）进阶

连接查询是SQL中最常用的高级查询技术之一，用于从多个表中获取相关数据。

1. INNER JOIN（内连接）

内连接返回两个表中满足连接条件的行。

1
2
3
4
5
6
7

SELECT 
    o.order_id, 
    o.order_date, 
    c.customer_name, 
    c.email
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;

2. LEFT JOIN（左连接）

左连接返回左表中的所有行，以及右表中满足连接条件的行。如果右表中没有匹配的行，则返回NULL。

1
2
3
4
5
6

SELECT 
    c.customer_name, 
    o.order_id, 
    o.order_date
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;

3. RIGHT JOIN（右连接）

右连接返回右表中的所有行，以及左表中满足连接条件的行。如果左表中没有匹配的行，则返回NULL。

1
2
3
4
5
6
7

SELECT 
    o.order_id, 
    o.order_date, 
    p.product_name, 
    p.price
FROM orders o
RIGHT JOIN products p ON o.product_id = p.id;

4. FULL OUTER JOIN（全外连接）

全外连接返回两个表中的所有行，当其中一个表中没有匹配的行时，返回NULL。

注意：MySQL不直接支持FULL OUTER JOIN，但可以通过UNION实现类似功能。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

-- PostgreSQL/SQL Server/Oracle支持
SELECT 
    c.customer_name, 
    o.order_id
FROM customers c
FULL OUTER JOIN orders o ON c.id = o.customer_id;

-- MySQL实现方式
SELECT 
    c.customer_name, 
    o.order_id
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
UNION
SELECT 
    c.customer_name, 
    o.order_id
FROM customers c
RIGHT JOIN orders o ON c.id = o.customer_id;

5. CROSS JOIN（交叉连接）

交叉连接返回两个表的笛卡尔积，即左表中的每一行与右表中的每一行组合。

1
2
3
4
5

SELECT 
    c.customer_name, 
    p.product_name
FROM customers c
CROSS JOIN products p;

6. SELF JOIN（自连接）

自连接是表与自身的连接，用于处理表中存在层次关系或引用关系的数据。

1
2
3
4
5

SELECT 
    e1.employee_name AS "员工",
    e2.employee_name AS "主管"
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;

子查询

子查询是嵌套在另一个SQL查询中的查询语句。子查询可以出现在SELECT、FROM、WHERE或HAVING子句中。

1. 标量子查询

返回单个值的子查询。

1
2
3
4
5
6

-- 查询订单金额大于平均订单金额的订单
SELECT 
    order_id, 
    amount
FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders);

2. 行子查询

返回一行多列数据的子查询。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- 查询与特定客户有相同地址的其他客户
SELECT 
    customer_name,
    city,
    country
FROM customers
WHERE (city, country) = (
    SELECT city, country 
    FROM customers 
    WHERE customer_name = '张三'
);

3. 列子查询

返回单列多行数据的子查询。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- 查询已购买特定产品的客户
SELECT 
    customer_name,
    email
FROM customers
WHERE id IN (
    SELECT customer_id 
    FROM orders 
    WHERE product_id = 101
);

4. 表子查询

返回多行多列数据的子查询，通常在FROM子句中使用。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

-- 查询每个客户的订单总数和总金额
SELECT 
    c.customer_name,
    o.order_count,
    o.total_amount
FROM customers c
JOIN (
    SELECT 
        customer_id,
        COUNT(*) AS order_count,
        SUM(amount) AS total_amount
    FROM orders
    GROUP BY customer_id
) o ON c.id = o.customer_id;

5. 相关子查询

引用外部查询列的子查询，对外部查询的每一行执行一次。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- 查询每个客户的最新订单
SELECT 
    o1.order_id,
    o1.customer_id,
    o1.order_date,
    o1.amount
FROM orders o1
WHERE o1.order_date = (
    SELECT MAX(o2.order_date)
    FROM orders o2
    WHERE o2.customer_id = o1.customer_id
);

窗口函数

窗口函数在结果集的"窗口"上执行计算，不改变结果集行数。

1. 排名函数

ROW_NUMBER()

为结果集中的每一行分配一个唯一的行号。

1
2
3
4
5
6
7

-- 为每个产品类别中的产品按价格排序并分配行号
SELECT 
    category_id,
    product_name,
    price,
    ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) AS row_num
FROM products;

RANK()

为结果集分配排名，相同值的行会有相同的排名，下一个排名会跳过相应的位置。

1
2
3
4
5
6

-- 为产品按价格排名
SELECT 
    product_name,
    price,
    RANK() OVER (ORDER BY price DESC) AS rank
FROM products;

DENSE_RANK()

为结果集分配排名，相同值的行会有相同的排名，但下一个排名不会跳过位置。

1
2
3
4
5
6

-- 为产品按价格密集排名
SELECT 
    product_name,
    price,
    DENSE_RANK() OVER (ORDER BY price DESC) AS dense_rank
FROM products;

2. 聚合窗口函数

在窗口上执行聚合计算。

1
2
3
4
5
6
7
8

-- 计算每个客户的订单金额及其占该客户总订单金额的百分比
SELECT 
    customer_id,
    order_id,
    amount,
    SUM(amount) OVER (PARTITION BY customer_id) AS customer_total,
    ROUND(amount * 100.0 / SUM(amount) OVER (PARTITION BY customer_id), 2) AS percentage
FROM orders;

3. 偏移窗口函数

访问当前行前后的行数据。

LAG()

访问当前行之前的行数据。

1
2
3
4
5
6
7
8

-- 查询每个产品与前一个产品的价格差异
SELECT 
    product_id,
    product_name,
    price,
    LAG(price, 1) OVER (ORDER BY product_id) AS previous_price,
    price - LAG(price, 1) OVER (ORDER BY product_id) AS price_diff
FROM products;

LEAD()

访问当前行之后的行数据。

1
2
3
4
5
6
7
8

-- 查询每个产品与后一个产品的价格差异
SELECT 
    product_id,
    product_name,
    price,
    LEAD(price, 1) OVER (ORDER BY product_id) AS next_price,
    LEAD(price, 1) OVER (ORDER BY product_id) - price AS price_diff
FROM products;

公用表表达式（CTE）

CTE是在查询中定义的临时结果集，可以使复杂查询更加清晰。

1. 基本CTE 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

WITH customer_orders AS (
    SELECT 
        customer_id,
        COUNT(*) AS order_count,
        SUM(amount) AS total_amount
    FROM orders
    GROUP BY customer_id
)
SELECT 
    c.customer_name,
    co.order_count,
    co.total_amount
FROM customers c
JOIN customer_orders co ON c.id = co.customer_id
WHERE co.order_count > 5;

2. 递归CTE

递归CTE用于查询层次数据，如组织结构、评论树等。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

-- 查询组织结构
WITH RECURSIVE employee_hierarchy AS (
    -- 基础查询
    SELECT 
        id,
        employee_name,
        manager_id,
        1 AS level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- 递归查询
    SELECT 
        e.id,
        e.employee_name,
        e.manager_id,
        eh.level + 1 AS level
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy ORDER BY level;

高级聚合查询

1. GROUP BY CUBE

生成多维度的聚合结果。

1
2
3
4
5
6
7
8
9

-- 按产品类别和年份进行多维聚合
SELECT 
    category_id,
    EXTRACT(YEAR FROM order_date) AS order_year,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount
FROM orders o
JOIN products p ON o.product_id = p.id
GROUP BY CUBE(category_id, order_year);

2. GROUP BY ROLLUP

生成层次化的聚合结果。

1
2
3
4
5
6
7
8
9

-- 按产品类别和年份进行层次聚合
SELECT 
    category_id,
    EXTRACT(YEAR FROM order_date) AS order_year,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount
FROM orders o
JOIN products p ON o.product_id = p.id
GROUP BY ROLLUP(category_id, order_year);

3. GROUPING SETS

指定多个分组组合。

1
2
3
4
5
6
7
8
9

-- 按指定的分组组合进行聚合
SELECT 
    category_id,
    EXTRACT(YEAR FROM order_date) AS order_year,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount
FROM orders o
JOIN products p ON o.product_id = p.id
GROUP BY GROUPING SETS (category_id, order_year, (category_id, order_year));

高级过滤技术

1. CASE表达式

在查询中执行条件逻辑。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- 使用CASE表达式对订单金额进行分类
SELECT 
    order_id,
    customer_id,
    amount,
    CASE 
        WHEN amount < 100 THEN '小额订单'
        WHEN amount >= 100 AND amount < 500 THEN '中额订单'
        ELSE '大额订单'
    END AS order_category
FROM orders;

2. 正则表达式过滤

使用正则表达式进行模式匹配。

1
2
3
4
5
6
7
8

-- 查询邮箱以特定域名结尾的客户
SELECT 
    customer_name,
    email
FROM customers
WHERE email ~ '\.com$'; -- PostgreSQL语法
-- 或
-- WHERE email REGEXP '\.com$'; -- MySQL语法

3. EXISTS子句

检查子查询是否返回任何行。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- 查询至少有一个订单的客户
SELECT 
    customer_name,
    email
FROM customers c
WHERE EXISTS (
    SELECT 1 
    FROM orders o 
    WHERE o.customer_id = c.id
);

查询优化技巧

1. 使用EXPLAIN分析查询计划 

1
2
3
4
5
6
7

-- 分析查询执行计划
EXPLAIN ANALYZE SELECT 
    c.customer_name,
    COUNT(o.order_id) AS order_count
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.id;

2. 合理使用索引

  • 为WHERE子句中的条件列创建索引
  • 为JOIN条件中的列创建索引
  • 为ORDER BY和GROUP BY中的列创建索引
  • 避免在索引列上使用函数

3. 避免全表扫描

  • 使用WHERE子句过滤数据
  • 限制结果集大小（使用LIMIT）
  • 避免SELECT *

4. 使用UNION ALL代替UNION

如果不需要去重，使用UNION ALL可以提高性能。

1
2
3
4

-- 不需要去重时使用UNION ALL
SELECT product_id FROM orders_2023
UNION ALL
SELECT product_id FROM orders_2024;

5. 优化JOIN顺序

在FROM子句中，将小表放在前面可以减少中间结果集的大小。

实用查询示例

1. 分页查询 

1
2
3
4
5
6
7
8

-- 每页显示10条记录，获取第3页
SELECT 
    customer_id,
    customer_name,
    email
FROM customers
ORDER BY id
LIMIT 10 OFFSET 20; -- 跳过前20条记录，获取接下来的10条

2. 查找重复记录 

1
2
3
4
5
6
7

-- 查找重复的邮箱
SELECT 
    email,
    COUNT(*) AS count
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

3. 日期范围查询 

1
2
3
4
5
6
7

-- 查询最近30天的订单
SELECT 
    order_id,
    order_date,
    amount
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days';

4. 计算留存率 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

-- 计算用户次日留存率
WITH first_orders AS (
    SELECT 
        customer_id,
        MIN(order_date) AS first_order_date
    FROM orders
    GROUP BY customer_id
),
next_day_orders AS (
    SELECT 
        fo.customer_id
    FROM first_orders fo
    JOIN orders o ON fo.customer_id = o.customer_id
    WHERE o.order_date = fo.first_order_date + INTERVAL '1 day'
)
SELECT 
    COUNT(DISTINCT fo.customer_id) AS total_new_users,
    COUNT(DISTINCT nfo.customer_id) AS retained_users,
    ROUND(COUNT(DISTINCT nfo.customer_id) * 100.0 / COUNT(DISTINCT fo.customer_id), 2) AS retention_rate
FROM first_orders fo
LEFT JOIN next_day_orders nfo ON fo.customer_id = nfo.customer_id;

结语

掌握SQL高级查询技巧对于处理复杂的数据需求至关重要。本文介绍了连接查询、子查询、窗口函数、CTE和高级聚合查询等技术，希望能够帮助你提升数据检索和分析能力。在实际应用中，建议多练习这些技巧，并根据具体场景选择合适的查询方法。记住，好的SQL查询不仅要能得到正确的结果，还要考虑性能和可读性。

在下一篇文章中，我们将学习数据库设计的最佳实践，敬请期待！

CC BY-NC-SA 4.0
comments powered by Disqus
© 2020 - 2026 miroliu
使用 Hugo 构建
主题 Stack 由 Jimmy 设计