SQLite 是一个轻量级、嵌入式的关系型数据库管理系统，它以其零配置、跨平台和易于部署的特性而广受欢迎。尽管 SQLite 看起来简单，但其查询语言（SQL）功能强大，支持许多高级特性，能够处理复杂的数据操作。本文将深入探讨 SQLite 的高级查询技巧，通过实际示例展示如何利用这些技巧解决复杂的数据处理挑战。
1. 窗口函数（Window Functions）
窗口函数是 SQL 标准中的一项强大功能，允许在不减少行数的情况下对一组行执行计算。SQLite 从版本 3.25.0 开始支持窗口函数。窗口函数通常与
OVER
子句一起使用，用于定义窗口的分区、排序和框架。
1.1 基本语法
SELECT column1, column2,
window_function() OVER (
[PARTITION BY partition_column]
[ORDER BY order_column [ASC|DESC]]
[frame_clause]
) AS result
FROM table_name;
1.2 常用窗口函数
聚合函数
：
SUM()
,
AVG()
,
COUNT()
,
MIN()
,
MAX()
等。
排名函数
：
ROW_NUMBER()
,
RANK()
,
DENSE_RANK()
,
NTILE()
。
偏移函数
：
LAG()
,
LEAD()
,
FIRST_VALUE()
,
LAST_VALUE()
。
1.3 实战示例
假设我们有一个销售数据表
sales
，包含以下字段：
id
（销售ID）、
product
（产品）、
sale_date
（销售日期）、
amount
（销售金额）。
CREATE TABLE sales (
id INTEGER PRIMARY KEY,
product TEXT,
sale_date DATE,
amount REAL
);
INSERT INTO sales (product, sale_date, amount) VALUES
(‘A’, ‘2023-01-01’, 100),
(‘A’, ‘2023-01-02’, 150),
(‘A’, ‘2023-01-03’, 200),
(‘B’, ‘2023-01-01’, 300),
(‘B’, ‘2023-01-02’, 250),
(‘B’, ‘2023-01-03’, 400);
示例1：计算每个产品的累计销售额
SELECT
product,
sale_date,
amount,
SUM(amount) OVER (
PARTITION BY product
ORDER BY sale_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS cumulative_amount
FROM sales;
结果
：
product
sale_date
amount
cumulative_amount
A
2023-01-01
100
100
A
2023-01-02
150
250
A
2023-01-03
200
450
B
2023-01-01
300
300
B
2023-01-02
250
550
B
2023-01-03
400
950
示例2：计算每个产品的销售排名
SELECT
product,
sale_date,
amount,
RANK() OVER (
PARTITION BY product
ORDER BY amount DESC
) AS rank_by_amount
FROM sales;
结果
：
product
sale_date
amount
rank_by_amount
A
2023-01-03
200
1
A
2023-01-02
150
2
A
2023-01-01
100
3
B
2023-01-03
400
1
B
2023-01-01
300
2
B
2023-01-02
250
3
示例3：使用LAG函数比较相邻日期的销售额
SELECT
product,
sale_date,
amount,
LAG(amount, 1) OVER (
PARTITION BY product
ORDER BY sale_date
) AS previous_day_amount,
amount – LAG(amount, 1) OVER (
PARTITION BY product
ORDER BY sale_date
) AS amount_change
FROM sales;
结果
：
product
sale_date
amount
previous_day_amount
amount_change
A
2023-01-01
100
NULL
NULL
A
2023-01-02
150
100
50
A
2023-01-03
200
150
50
B
2023-01-01
300
NULL
NULL
B
2023-01-02
250
300
-50
B
2023-01-03
400
250
150
2. 公用表表达式（Common Table Expressions, CTEs）
CTEs 是一种临时命名的结果集，可以在查询中多次引用。它们使复杂查询更易读、更易维护。SQLite 从版本 3.8.3 开始支持 CTEs。
2.1 基本语法
WITH cte_name (column1, column2, …) AS (
— 子查询
SELECT …
)
SELECT …
FROM cte_name;
2.2 递归CTE
递归CTE用于处理层次结构数据，如组织结构、树形数据等。
2.3 实战示例
假设我们有一个员工表
employees
，包含
id
、
name
、
manager_id
（上级ID）。
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name TEXT,
manager_id INTEGER
);
INSERT INTO employees (id, name, manager_id) VALUES
(1, ‘Alice’, NULL),
(2, ‘Bob’, 1),
(3, ‘Charlie’, 1),
(4, ‘David’, 2),
(5, ‘Eve’, 2),
(6, ‘Frank’, 3);
示例1：使用CTE计算每个员工的层级
WITH EmployeeHierarchy AS (
SELECT
id,
name,
manager_id,
1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT
e.id,
e.name,
e.manager_id,
eh.level + 1
FROM employees e
INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM EmployeeHierarchy;
结果
：
id
name
manager_id
level
1
Alice
NULL
1
2
Bob
1
2
3
Charlie
1
2
4
David
2
3
5
Eve
2
3
6
Frank
3
3
示例2：使用CTE计算每个员工的下属数量
WITH EmployeeSubordinates AS (
SELECT
manager_id,
COUNT(*) AS subordinate_count
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
)
SELECT
e.id,
e.name,
COALESCE(es.subordinate_count, 0) AS subordinate_count
FROM employees e
LEFT JOIN EmployeeSubordinates es ON e.id = es.manager_id;
结果
：
id
name
subordinate_count
1
Alice
2
2
Bob
2
3
Charlie
1
4
David
0
5
Eve
0
6
Frank
0
3. JSON函数（JSON Functions）
SQLite 从版本 3.9.0 开始支持 JSON 函数，这使得处理 JSON 数据变得非常方便。JSON 函数包括
json()
,
json_array()
,
json_object()
,
json_extract()
,
json_set()
,
json_remove()
,
json_insert()
,
json_replace()
,
json_patch()
,
json_group_array()
,
json_group_object()
等。
3.1 基本语法
— 创建JSON对象
SELECT json_object(‘key1’, ‘value1’, ‘key2’, ‘value2’);
— 提取JSON值
SELECT json_extract(json_column, ‘$.key’);
— 更新JSON值
SELECT json_set(json_column, ‘$.key’, ‘new_value’);
3.2 实战示例
假设我们有一个产品表
products
，包含
id
、
name
、
attributes
（JSON格式的属性）。
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT,
attributes TEXT
);
INSERT INTO products (id, name, attributes) VALUES
(1, ‘Laptop’, ‘{“color”: “black”, “size”: “15-inch”, “price”: 1200}’),
(2, ‘Phone’, ‘{“color”: “white”, “size”: “6-inch”, “price”: 800}’),
(3, ‘Tablet’, ‘{“color”: “silver”, “size”: “10-inch”, “price”: 600}’);
示例1：提取JSON字段
SELECT
id,
name,
json_extract(attributes, ‘$.color’) AS color,
json_extract(attributes, ‘$.size’) AS size,
json_extract(attributes, ‘$.price’) AS price
FROM products;
结果
：
id
name
color
size
price
1
Laptop
black
15-inch
1200
2
Phone
white
6-inch
800
3
Tablet
silver
10-inch
600
示例2：更新JSON字段
UPDATE products
SET attributes = json_set(attributes, ‘$.price’, 1300)
WHERE id = 1;
SELECT * FROM products WHERE id = 1;
结果
：
id
name
attributes
1
Laptop
{“color”:“black”,“size”:“15-inch”,“price”:1300}
示例3：使用JSON聚合函数
SELECT
json_group_array(
json_object(
‘id’, id,
‘name’, name,
‘attributes’, attributes
)
) AS all_products
FROM products;
结果
：
all_products
[{“id”:1,“name”:“Laptop”,“attributes”:“{\“color\”:\“black\”,\“size\”:\“15-inch\”,\“price\”:1300}“}, {“id”:2,“name”:“Phone”,“attributes”:“{\“color\”:\“white\”,\“size\”:\“6-inch\”,\“price\”:800}“}, {“id”:3,“name”:“Tablet”,“attributes”:“{\“color\”:\“silver\”,\“size\”:\“10-inch\”,\“price\”:600}“}]
4. 高级连接（Advanced Joins）
SQLite 支持标准的
INNER JOIN
、
LEFT JOIN
、
RIGHT JOIN
（通过
LEFT JOIN
模拟）、
FULL OUTER JOIN
（通过
LEFT JOIN
和
UNION
模拟），以及
CROSS JOIN
。此外，SQLite 还支持
NATURAL JOIN
和
USING
子句。
4.1 自连接（Self Join）
自连接是将表与自身连接，常用于处理层次结构数据。
示例：员工与经理的连接
SELECT
e.id AS employee_id,
e.name AS employee_name,
m.id AS manager_id,
m.name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
结果
：
employee_id
employee_name
manager_id
manager_name
1
Alice
NULL
NULL
2
Bob
1
Alice
3
Charlie
1
Alice
4
David
2
Bob
5
Eve
2
Bob
6
Frank
3
Charlie
4.2 交叉连接（Cross Join）
交叉连接返回两个表的笛卡尔积。
示例：生成所有产品和颜色的组合
CREATE TABLE colors (color TEXT);
INSERT INTO colors (color) VALUES (‘red’), (‘green’), (‘blue’);
SELECT
p.name,
c.color
FROM products p
CROSS JOIN colors c;
结果
：
name
color
Laptop
red
Laptop
green
Laptop
blue
Phone
red
Phone
green
Phone
blue
Tablet
red
Tablet
green
Tablet
blue
4.3 使用
USING
子句简化连接
当连接条件中的列名相同时，可以使用
USING
子句。
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
product_id INTEGER,
quantity INTEGER
);
INSERT INTO orders (order_id, product_id, quantity) VALUES
(1, 1, 2),
(2, 2, 1),
(3, 3, 3);
SELECT
p.name,
o.quantity
FROM products p
INNER JOIN orders o USING (product_id);
结果
：
name
quantity
Laptop
2
Phone
1
Tablet
3
5. 条件表达式（Conditional Expressions）
SQLite 提供了
CASE
表达式，允许在查询中进行条件判断。此外，还有
COALESCE
、
NULLIF
等函数。
5.1 CASE 表达式
SELECT
column1,
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE result3
END AS new_column
FROM table_name;
5.2 实战示例
示例1：根据销售额分类
SELECT
product,
sale_date,
amount,
CASE
WHEN amount >= 300 THEN ‘High’
WHEN amount >= 200 THEN ‘Medium’
ELSE ‘Low’
END AS amount_category
FROM sales;
结果
：
product
sale_date
amount
amount_category
A
2023-01-01
100
Low
A
2023-01-02
150
Low
A
2023-01-03
200
Medium
B
2023-01-01
300
High
B
2023-01-02
250
Medium
B
2023-01-03
400
High
示例2：使用COALESCE处理NULL值
SELECT
e.id,
e.name,
COALESCE(m.name, ‘No Manager’) AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
结果
：
id
name
manager_name
1
Alice
No Manager
2
Bob
Alice
3
Charlie
Alice
4
David
Bob
5
Eve
Bob
6
Frank
Charlie
6. 高级子查询（Advanced Subqueries）
子查询是嵌套在另一个查询中的查询。SQLite 支持相关子查询和非相关子查询。
6.1 相关子查询
相关子查询依赖于外部查询的列。
示例：查找销售额高于平均销售额的产品
SELECT
product,
sale_date,
amount
FROM sales s1
WHERE amount > (
SELECT AVG(amount)
FROM sales s2
WHERE s2.product = s1.product
);
结果
：
product
sale_date
amount
A
2023-01-03
200
B
2023-01-01
300
B
2023-01-03
400
6.2 派生表（Derived Tables）
派生表是子查询的结果作为临时表使用。
示例：计算每个产品的总销售额和平均销售额
SELECT
product,
total_amount,
avg_amount
FROM (
SELECT
product,
SUM(amount) AS total_amount,
AVG(amount) AS avg_amount
FROM sales
GROUP BY product
) AS product_summary;
结果
：
product
total_amount
avg_amount
A
450
150
B
950
316.666
7. 高级分组（Advanced Grouping）
SQLite 支持
GROUP BY
、
HAVING
和
GROUPING SETS
（从版本 3.30.0 开始）。
GROUPING SETS
允许在单个查询中生成多个分组级别的汇总。
7.1 GROUPING SETS
SELECT
product,
sale_date,
SUM(amount) AS total_amount
FROM sales
GROUP BY GROUPING SETS (
(product),
(sale_date),
(product, sale_date)
);
结果
：
product
sale_date
total_amount
A
NULL
450
B
NULL
950
NULL
2023-01-01
400
NULL
2023-01-02
400
NULL
2023-01-03
600
A
2023-01-01
100
A
2023-01-02
150
A
2023-01-03
200
B
2023-01-01
300
B
2023-01-02
250
B
2023-01-03
400
7.2 ROLLUP 和 CUBE
SQLite 不直接支持
ROLLUP
和
CUBE
，但可以通过
GROUPING SETS
模拟。
模拟 ROLLUP
SELECT
product,
sale_date,
SUM(amount) AS total_amount
FROM sales
GROUP BY GROUPING SETS (
(product, sale_date),
(product),
()
);
结果
：
product
sale_date
total_amount
A
2023-01-01
100
A
2023-01-02
150
A
2023-01-03
200
A
NULL
450
B
2023-01-01
300
B
2023-01-02
250
B
2023-01-03
400
B
NULL
950
NULL
NULL
1400
8. 高级索引（Advanced Indexing）
索引是提高查询性能的关键。SQLite 支持多种索引类型，包括唯一索引、复合索引、部分索引和虚拟表索引。
8.1 复合索引
复合索引包含多个列，可以提高多列查询的性能。
CREATE INDEX idx_sales_product_date ON sales (product, sale_date);
8.2 部分索引
部分索引只对满足特定条件的行创建索引。
CREATE INDEX idx_sales_high_amount ON sales (amount) WHERE amount > 200;
8.3 覆盖索引
覆盖索引包含查询所需的所有列，避免回表操作。
CREATE INDEX idx_sales_covering ON sales (product, sale_date, amount);
8.4 索引优化示例
假设我们有一个大型销售表，需要频繁查询特定产品的销售额。
— 创建覆盖索引
CREATE INDEX idx_sales_product_amount ON sales (product, amount);
— 查询使用索引
SELECT
product,
SUM(amount) AS total_amount
FROM sales
WHERE product = ‘A’
GROUP BY product;
9. 事务和并发控制（Transactions and Concurrency Control）
SQLite 支持事务，确保数据的完整性和一致性。SQLite 使用锁机制来处理并发访问。
9.1 事务的基本操作
BEGIN TRANSACTION;
— 执行多个DML操作
INSERT INTO sales (product, sale_date, amount) VALUES (‘C’, ‘2023-01-04’, 500);
UPDATE sales SET amount = 600 WHERE id = 1;
— 提交或回滚
COMMIT; — 或 ROLLBACK;
9.2 保存点（Savepoints）
保存点允许在事务中设置回滚点。
BEGIN TRANSACTION;
SAVEPOINT sp1;
INSERT INTO sales (product, sale_date, amount) VALUES (‘D’, ‘2023-01-05’, 700);
SAVEPOINT sp2;
UPDATE sales SET amount = 800 WHERE id = 2;
— 回滚到sp2
ROLLBACK TO sp2;
— 提交到sp1
COMMIT;
9.3 并发控制
SQLite 使用锁机制来处理并发访问。默认情况下，SQLite 使用
DATABASE LOCKED
错误来处理冲突。可以通过设置
PRAGMA
来调整锁行为。
— 设置锁超时时间（毫秒）
PRAGMA lock_timeout = 5000;
— 设置事务模式
PRAGMA journal_mode = WAL; — Write-Ahead Logging 模式，提高并发性能
10. 高级数据操作（Advanced Data Manipulation）
SQLite 支持
INSERT
、
UPDATE
、
DELETE
的高级用法，如
INSERT OR REPLACE
、
INSERT OR IGNORE
、
UPSERT
（从版本 3.24.0 开始）等。
10.1 UPSERT（插入或更新）
UPSERT 允许在插入时如果存在冲突则更新。
CREATE TABLE users (
id INTEGER PRIMARY KEY,
username TEXT UNIQUE,
email TEXT
);
INSERT INTO users (id, username, email) VALUES (1, ‘alice’, ‘alice@example.com’);
— UPSERT 操作
INSERT INTO users (id, username, email)
VALUES (1, ‘alice’, ‘alice_new@example.com’)
ON CONFLICT(username) DO UPDATE SET email = excluded.email;
10.2 批量操作
批量操作可以提高性能，减少事务开销。
BEGIN TRANSACTION;
INSERT INTO sales (product, sale_date, amount) VALUES
(‘E’, ‘2023-01-06’, 900),
(‘F’, ‘2023-01-07’, 1000),
(‘G’, ‘2023-01-08’, 1100);
COMMIT;
11. 性能优化技巧（Performance Optimization Tips）
11.1 使用EXPLAIN分析查询
EXPLAIN QUERY PLAN
SELECT * FROM sales WHERE product = ‘A’ AND sale_date > ‘2023-01-01’;
11.2 避免使用SELECT *
— 不推荐
SELECT * FROM sales;
— 推荐
SELECT id, product, sale_date, amount FROM sales;
11.3 使用LIMIT和OFFSET分页
SELECT * FROM sales
ORDER BY sale_date DESC
LIMIT 10 OFFSET 20;
11.4 避免在WHERE子句中使用函数
— 不推荐（可能无法使用索引）
SELECT * FROM sales WHERE DATE(sale_date) = ‘2023-01-01’;
— 推荐
SELECT * FROM sales WHERE sale_date = ‘2023-01-01’;
11.5 使用临时表处理复杂查询
CREATE TEMPORARY TABLE temp_sales AS
SELECT product, SUM(amount) AS total_amount
FROM sales
GROUP BY product;
SELECT * FROM temp_sales;
12. 实战案例：复杂数据处理挑战
12.1 案例：销售数据分析
假设我们需要分析销售数据，找出每个产品的月度销售额、同比增长率、以及在所有产品中的排名。
步骤1：创建数据表
CREATE TABLE monthly_sales (
product TEXT,
month TEXT,
amount REAL
);
INSERT INTO monthly_sales (product, month, amount) VALUES
(‘A’, ‘2023-01’, 450),
(‘A’, ‘2023-02’, 500),
(‘A’, ‘2023-03’, 600),
(‘B’, ‘2023-01’, 950),
(‘B’, ‘2023-02’, 1000),
(‘B’, ‘2023-03’, 1100);
步骤2：使用窗口函数计算同比增长率
WITH monthly_sales_with_prev AS (
SELECT
product,
month,
amount,
LAG(amount, 1) OVER (
PARTITION BY product
ORDER BY month
) AS prev_month_amount
FROM monthly_sales
)
SELECT
product,
month,
amount,
prev_month_amount,
CASE
WHEN prev_month_amount IS NULL THEN NULL
ELSE (amount – prev_month_amount) / prev_month_amount * 100
END AS growth_rate_percent
FROM monthly_sales_with_prev;
结果
：
product
month
amount
prev_month_amount
growth_rate_percent
A
2023-01
450
NULL
NULL
A
2023-02
500
450
11.11
A
2023-03
600
500
20.00
B
2023-01
950
NULL
NULL
B
2023-02
1000
950
5.26
B
2023-03
1100
1000
10.00
步骤3：计算产品排名
WITH product_summary AS (
SELECT
product,
SUM(amount) AS total_amount
FROM monthly_sales
GROUP BY product
)
SELECT
product,
total_amount,
RANK() OVER (ORDER BY total_amount DESC) AS rank
FROM product_summary;
结果
：
product
total_amount
rank
B
3050
1
A
1550
2
12.2 案例：组织结构分析
假设我们需要分析公司组织结构，找出每个员工的直接下属数量、间接下属数量，以及整个团队的层级。
步骤1：使用递归CTE计算层级和下属数量
WITH RECURSIVE employee_hierarchy AS (
— 锚点：根节点（没有上级的员工）
SELECT
id,
name,
manager_id,
1 AS level,
CAST(id AS TEXT) AS path
FROM employees
WHERE manager_id IS NULL
UNION ALL
— 递归：连接下属
SELECT
e.id,
e.name,
e.manager_id,
eh.level + 1,
eh.path || ‘,’ || e.id
FROM employees e
INNER JOIN employee_hierarchy eh ON e.manager_id = eh.id
),
direct_subordinates AS (
SELECT
manager_id,
COUNT(*) AS direct_count
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
),
indirect_subordinates AS (
SELECT
manager_id,
COUNT(*) – 1 AS indirect_count
FROM employee_hierarchy
GROUP BY manager_id
)
SELECT
e.id,
e.name,
COALESCE(ds.direct_count, 0) AS direct_subordinates,
COALESCE(isub.indirect_count, 0) AS indirect_subordinates,
eh.level AS hierarchy_level,
eh.path
FROM employees e
LEFT JOIN direct_subordinates ds ON e.id = ds.manager_id
LEFT JOIN indirect_subordinates isub ON e.id = isub.manager_id
LEFT JOIN employee_hierarchy eh ON e.id = eh.id;
结果
：
id
name
direct_subordinates
indirect_subordinates
hierarchy_level
path
1
Alice
2
3
1
1
2
Bob
2
1
2
1,2
3
Charlie
1
0
2
1,3
4
David
0
0
3
1,2,4
5
Eve
0
0
3
1,2,5
6
Frank
0
0
3
1,3,6
13. 总结
SQLite 虽然轻量，但其查询功能非常强大。通过掌握窗口函数、CTEs、JSON函数、高级连接、条件表达式、高级子查询、高级分组、高级索引、事务控制、高级数据操作和性能优化技巧，你可以轻松应对各种复杂的数据处理挑战。
在实际应用中，建议：
根据需求选择合适的技巧
：不同的场景适合不同的技巧。
注重性能
：合理使用索引，避免不必要的复杂查询。
保持代码可读性
：使用CTEs和适当的注释使查询更易维护。
测试和优化
：使用
EXPLAIN QUERY PLAN
分析查询性能，不断优化。
通过本文的实战指南，你应该能够自信地使用 SQLite 处理各种复杂的数据操作，从而提高工作效率和数据处理能力。