SQL逗号分隔转成多行|极客教程

SQL逗号分隔转成多行

在SQL数据库中，有时候我们会遇到逗号分隔的数据需要转换成多行的情况。这种情况在数据库设计不规范或者数据导入过程中常常会遇到。本文将介绍如何将逗号分隔的数据转换成多行的形式，方便数据的处理和分析。

问题背景

假设我们有一个名为students的表，其中有一列为hobbies，存储着学生的爱好信息，数据格式如下：

| id | name   | hobbies            |
|----|--------|--------------------|
| 1  | 小明   | 篮球,足球,游泳     |
| 2  | 小红   | 美术,音乐,阅读     |
| 3  | 小军   | 唱歌,跳舞          |

我们希望将hobbies列中逗号分隔的数据拆分成多行的形式，使数据更容易进行分析和处理。

解决方法

为了将逗号分隔的数据转换成多行，我们可以使用数据库的一些内置函数来实现。下面分别介绍在MySQL和PostgreSQL数据库中如何解决这个问题。

MySQL

在MySQL中，我们可以使用SUBSTRING_INDEX和LENGTH等函数来实现逗号分隔数据转换成多行的操作。具体的SQL代码如下：

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

运行以上SQL语句后，将会得到以下结果：

| id | name   | hobby |
|----|--------|-------|
| 1  | 小明   | 篮球  |
| 1  | 小明   | 足球  |
| 1  | 小明   | 游泳  |
| 2  | 小红   | 美术  |
| 2  | 小红   | 音乐  |
| 2  | 小红   | 阅读  |
| 3  | 小军   | 唱歌  |
| 3  | 小军   | 跳舞  |

通过以上SQL语句，我们成功地将逗号分隔的数据转换成了多行的形式，方便后续的数据处理。

PostgreSQL

在PostgreSQL中，我们可以使用unnest函数来实现逗号分隔数据转换成多行的操作。具体的SQL代码如下：

SELECT id,
       name,
       unnest(string_to_array(hobbies, ',')) AS hobby
FROM students;

运行以上SQL语句后，将会得到和MySQL中相同的结果：

| id | name   | hobby |
|----|--------|-------|
| 1  | 小明   | 篮球  |
| 1  | 小明   | 足球  |
| 1  | 小明   | 游泳  |
| 2  | 小红   | 美术  |
| 2  | 小红   | 音乐  |
| 2  | 小红   | 阅读  |
| 3  | 小军   | 唱歌  |
| 3  | 小军   | 跳舞  |

通过以上两种方法，我们成功地将逗号分隔的数据转换成了多行的形式，使数据更易于处理和分析。