SQLite 数据库如何多行数据连接成字符串

在使用 SQLite 数据库进行数据处理时，常常会遇到需要将多行数据连接成一个字符串的需求。举例来说，假设我们有一个存储用户名的表，想将这些用户名用逗号分隔组合成一个字符串，以便展示或进一步处理。SQLite 本身不像某些其他数据库系统（如 MySQL、PostgreSQL）那样原生支持直接的字符串聚合函数（如 GROUP_CONCAT），但通过不同的方法，我们仍然可以实现这一需求。

本文将介绍三种在 SQLite 中将多行数据连接成字符串的方法，涵盖基础实现和进阶用法，并对比这些方法的优缺点，帮助读者选择最适合的方案。

一、需求场景和解决方案概述

场景介绍

我们有一个 users 表，结构如下：

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT
);

INSERT INTO users (name) VALUES 
('Alice'),
('Bob'),
('Charlie'),
('David');

目标是将表中的 name 字段连接成一个以逗号分隔的字符串，例如 Alice, Bob, Charlie, David。除此之外，还可能会遇到自定义分隔符和去重等场景。

解决方案概述

在 SQLite 中，可以通过以下三种方法实现多行数据的字符串连接：

1. 使用 SQLite 自带的 GROUP_CONCAT 函数。
2. 使用递归 CTE（Common Table Expression）。
3. 编写自定义聚合函数。

二、实现方法

方法一：使用 SQLite 自带的 GROUP_CONCAT 函数

SQLite 自带的 GROUP_CONCAT 是最简单且常用的方法。该函数将一个组内的所有非 NULL 值连接起来，默认用逗号分隔。

示例代码：

SELECT GROUP_CONCAT(name) AS names
FROM users;

输出结果：

names
--------------------
Alice,Bob,Charlie,David

自定义分隔符：

可以通过 GROUP_CONCAT 的第二个参数来自定义分隔符。

SELECT GROUP_CONCAT(name, ' | ') AS names
FROM users;

输出结果：

names
--------------------
Alice | Bob | Charlie | David

方法二：使用递归 CTE

如果你需要更复杂的逻辑处理，递归 CTE 是一种强大且灵活的方法。通过递归，我们可以手动控制如何构建字符串。

示例代码：

WITH RECURSIVE name_concat(id, names) AS (
    SELECT id, name FROM users WHERE id = 1  -- 初始化递归
    UNION ALL
    SELECT u.id, nc.names || ', ' || u.name
    FROM users u, name_concat nc
    WHERE u.id = nc.id + 1
)
SELECT names FROM name_concat ORDER BY id DESC LIMIT 1;

输出结果：

names
--------------------
Alice, Bob, Charlie, David

说明：

1. 递归 CTE 首先从初始值开始（第一个用户），然后逐行递归连接其后的用户名。
2. 可以根据需要对递归逻辑进行修改，如更改分隔符或根据条件跳过某些行。

方法三：编写自定义聚合函数

如果你的需求非常复杂，甚至需要跨多个查询使用同样的逻辑，SQLite 允许你编写自定义的聚合函数。通常这需要使用某些编程语言（如 Python 或 C）来扩展 SQLite 的功能。在 Python 中可以借助 sqlite3 模块实现。

示例代码（Python 扩展）：

import sqlite3

# 自定义聚合函数
def custom_concat(values):
    return ', '.join(values)

# 连接 SQLite 数据库
conn = sqlite3.connect(':memory:')
conn.create_aggregate("custom_concat", 1, custom_concat)

# 示例查询
conn.execute('''
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT
);
INSERT INTO users (name) VALUES ('Alice'), ('Bob'), ('Charlie'), ('David');

SELECT custom_concat(name) FROM users;
''').fetchall()

输出结果：

[('Alice, Bob, Charlie, David',)]

说明：

1. 通过编写聚合函数，我们可以灵活控制如何对多行数据进行字符串连接。
2. 这种方法适用于需要定制化聚合逻辑的场景，但实现起来较为复杂，特别是在嵌入式系统或高性能场景中。

三、对比不同方法的优缺点

四、最佳实践建议

1. 优先使用 GROUP_CONCAT： 对于大多数字符串拼接需求，GROUP_CONCAT 是首选方案。它简单且高效，能满足常见的需求。如果需要自定义分隔符，使用第二个参数即可轻松实现。

2. 递归 CTE 适合复杂需求： 如果你需要按特定顺序、根据条件拼接字符串或处理较复杂的业务逻辑，递归 CTE 是一种灵活的方案。尽管实现起来稍微复杂一些，但它在处理非线性问题时非常有用。

3. 自定义聚合函数适用于高级场景： 如果你的项目对字符串拼接有独特的需求，特别是需要跨查询重用相同逻辑，编写自定义聚合函数是最佳选择。尽管需要编写额外代码，但它为处理复杂场景提供了最大的灵活性。

五、参考资料

1. SQLite GROUP_CONCAT Funtion: Concat Non-NULL Values in a Column
2. How to Convert SQL Rows to a Comma-Delimited String in various RDBMSs | Learn Database Online