SQL 拆分逗号隔开的值|极客教程

SQL 拆分逗号隔开的值

在数据库中，经常会遇到一个字段存储了多个值，这些值之间用逗号进行分隔。在某些情况下，我们需要将这些多个值分开，以便能够单独处理或者进行分析。本文将详细介绍如何在 SQL 中拆分逗号分隔的值。

场景描述

假设我们有一张表students，其中有一个字段interests存储了学生的兴趣爱好，这些兴趣爱好是用逗号进行分隔的。现在我们需要将这些兴趣爱好分开，以便统计每种兴趣爱好的人数。

示例数据如下：

id	name	interests
1	Alice	music,sports
2	Bob	sports
3	Cindy	music
4	David	reading,sports

方法一：使用 SUBSTRING_INDEX 函数

SUBSTRING_INDEX 函数可以用来获取字符串中指定分隔符的子串，其语法如下：

SUBSTRING_INDEX(str, delimiter, count)

其中str为要处理的字符串，delimiter为分隔符，count为要返回的子串的个数。如果count为正数，则从左边开始截取；如果count为负数，则从右边开始截取。

下面演示如何使用SUBSTRING_INDEX函数来拆分兴趣爱好：

SELECT
    id,
    name,
    SUBSTRING_INDEX(SUBSTRING_INDEX(interests, ',', n), ',', -1) AS interest
FROM
    students
    JOIN (
        SELECT 1 n UNION ALL
        SELECT 2 UNION ALL
        SELECT 3
    ) nums
    ON CHAR_LENGTH(interests) - CHAR_LENGTH(REPLACE(interests, ',', '')) >= n - 1
ORDER BY id, n;

上述 SQL 查询中，我们通过JOIN子查询生成了一个连续序列，并对interests字段进行拆分。假设interests字段最多拥有三个兴趣爱好（可以根据实际情况调整子查询中的序号数量），通过SUBSTRING_INDEX函数实现了拆分。

运行上述 SQL 查询，将得到以下结果：

id	name	interest
1	Alice	music
1	Alice	sports
2	Bob	sports
3	Cindy	music
4	David	reading
4	David	sports

方法二：使用 REGEXP_SUBSTR 函数

在某些数据库中，如 Oracle、PostgreSQL 等，可以使用REGEXP_SUBSTR函数来实现正则表达式匹配，并提取匹配的子串。

SELECT
    id,
    name,
    REGEXP_SUBSTR(interests, '[^,]+', 1, n) AS interest
FROM
    students
    JOIN (
        SELECT LEVEL AS n
        FROM DUAL
        CONNECT BY LEVEL <= (SELECT MAX(LENGTH(interests) - LENGTH(REPLACE(interests, ',', '')) + 1 FROM students)
    ) nums
    ON 1 = 1
ORDER BY id, n;

在上述 SQL 查询中，通过递归的方式生成了一个序列，然后利用REGEXP_SUBSTR函数实现了对interests字段的拆分。在正则表达式[^,]+中，表示匹配任意非逗号字符，从而实现了拆分的功能。

运行上述 SQL 查询，将得到与方法一相同的结果。

方法三：使用 LATERAL JOIN

在支持 LATERAL JOIN 的数据库中（如 PostgreSQL），可以使用 LATERAL JOIN 来拆分逗号分隔的值。

SELECT
    s.id,
    s.name,
    i.interest
FROM
    students s,
    LATERAL (
        SELECT REGEXP_SPLIT_TO_TABLE(s.interests, ',') AS interest
    ) i
ORDER BY s.id;

上述 SQL 查询中，通过 LATERAL JOIN 实现了对interests字段的拆分。REGEXP_SPLIT_TO_TABLE函数用于将字符串按照给定的正则表达式拆分成多行结果，从而实现了拆分的功能。