SQL一行拆分成多行|极客笔记

SQL一行拆分成多行

在数据库开发中，经常会遇到需要将一行数据拆分成多行的情况。例如，某个表中的一列是以逗号分隔的字符串，我们需要将其拆分成多行并逐行处理。本文将详细介绍如何在SQL中实现一行拆分成多行的操作。

1. 使用内置函数实现拆分

一种常见的方法是使用数据库的内置函数来实现拆分。不同的数据库系统有不同的内置函数可用。以下以MySQL为例，介绍如何使用内置函数实现拆分。

1.1 使用SUBSTRING_INDEX函数

MySQL提供了SUBSTRING_INDEX函数，该函数可以根据指定的分隔符将字符串拆分为多个子字符串。以下是使用SUBSTRING_INDEX函数将一行字符串拆分为多行的示例代码：

SELECT SUBSTRING_INDEX('apple,banana,orange', ',', 1) AS col1
UNION ALL
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('apple,banana,orange', ',', 2), ',', -1) AS col1
UNION ALL
SELECT SUBSTRING_INDEX('apple,banana,orange', ',', -1) AS col1;

运行以上代码后，将输出以下结果：

col1
-----
apple
banana
orange

在上述示例代码中，将'apple,banana,orange'字符串按照逗号进行拆分，并分别取出前1个、前2个和最后一个子字符串，通过UNION ALL将它们合并成多行。

1.2 使用REGEXP_SUBSTR函数

一些数据库系统还提供了正则表达式相关的函数，例如Oracle数据库的REGEXP_SUBSTR函数。使用该函数可以根据正则表达式将字符串拆分为多个子字符串。以下是使用REGEXP_SUBSTR函数将一行字符串拆分为多行的示例代码：

SELECT REGEXP_SUBSTR('apple,banana,orange', '[^,]+', 1, 1) AS col1 FROM dual
UNION ALL
SELECT REGEXP_SUBSTR('apple,banana,orange', '[^,]+', 1, 2) AS col1 FROM dual
UNION ALL
SELECT REGEXP_SUBSTR('apple,banana,orange', '[^,]+', 1, 3) AS col1 FROM dual;

运行以上代码后，将输出以下结果：

col1
-----
apple
banana
orange

在上述示例代码中，通过正则表达式[^,]+匹配非逗号的字符，并使用REGEXP_SUBSTR函数取出第1个、第2个和第3个子字符串，通过UNION ALL将它们合并成多行。

2. 使用递归查询实现拆分

除了使用内置函数，还可以使用递归查询实现拆分。递归查询是一种自引用查询，每次查询结果作为下一次查询的输入。以下以MySQL为例，介绍如何使用递归查询实现拆分。

2.1 创建递归查询函数

在MySQL中，可以使用存储过程和用户定义函数来实现递归查询。以下是创建一个递归查询函数的示例代码：

DELIMITER //
CREATE FUNCTION split_string(str VARCHAR(255), delim VARCHAR(1), pos INT)
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
    DECLARE val VARCHAR(255);
    SET val = REPLACE(SUBSTRING(SUBSTRING_INDEX(str, delim, pos),
                    LENGTH(SUBSTRING_INDEX(str, delim, pos - 1)) + 1),
                    delim,
                    '');
    RETURN val;
END //
DELIMITER ;

在上述示例代码中，split_string函数接受三个参数：待拆分的字符串、分隔符、需要取出的子字符串的位置。函数首先使用SUBSTRING_INDEX函数将字符串拆分为多个子字符串，再使用SUBSTRING、LENGTH和REPLACE函数过滤掉分隔符，最后返回指定位置的子字符串。

2.2 使用递归查询函数拆分字符串

以下是使用递归查询函数将一行字符串拆分为多行的示例代码：

WITH recursive cte AS (
    SELECT 'apple,banana,orange' AS str, 1 AS pos
    UNION ALL
    SELECT str, pos + 1
    FROM cte
    WHERE pos < LENGTH(str) - LENGTH(REPLACE(str, ',', '')) + 1
)
SELECT split_string(str, ',', pos) AS col1
FROM cte
ORDER BY pos;

运行以上代码后，将输出以下结果：

col1
-----
apple
banana
orange

以上示例代码中，使用WITH recursive关键字创建了一个递归查询，并在查询的起始部分设置了初始的字符串和位置。递归部分使用UNION ALL将上一次查询的结果连接到当前查询中，并通过判断当前位置是否小于字符串中的逗号个数决定是否继续递归。通过调用递归查询函数split_string并按位置排序，可以将字符串拆分成多行输出。