SQL按逗号拆分列为多行

1. 概述

在数据库中，经常会遇到一列数据中包含多个值，这些值之间通过逗号进行分隔。但是，有时候我们需要将这些多个值拆分为多行，以便更好地进行数据分析和查询。本文将详细介绍如何使用SQL将一列数据按逗号拆分为多行。

2. 示例数据

为了方便演示，我们首先创建一个包含多个值的示例表。假设我们有一个表名为items，其中有一个名为tags的列包含逗号分隔的多个标签。

CREATE TABLE items (
  id INT,
  name VARCHAR(50),
  tags VARCHAR(100)
);

INSERT INTO items (id, name, tags) VALUES
(1, 'Item 1', 'tag1,tag2,tag3'),
(2, 'Item 2', 'tag2,tag4'),
(3, 'Item 3', 'tag1,tag3,tag5');

以上代码创建了一个名为items的表，并向其插入了三行数据。其中tags列包含了多个标签，这些标签之间使用逗号进行分隔。

3. 拆分列为多行的SQL查询

下面介绍两种常用的SQL查询来拆分逗号分隔的列为多行。

3.1 使用`CROSS JOIN`和`STRING_SPLIT`函数

在SQL Server 2016及以上版本中，可以使用STRING_SPLIT函数来拆分逗号分隔的列为多行。

SELECT i.id, i.name, s.value AS tag
FROM items i
CROSS APPLY STRING_SPLIT(i.tags, ',') s;

以上SQL语句使用了CROSS APPLY和STRING_SPLIT函数。CROSS APPLY用于将每一行的tags列应用到STRING_SPLIT函数，而STRING_SPLIT函数则将逗号分隔的值拆分为多行。通过这种方式，可以将每个标签拆分为一行，并与原始数据关联。

运行以上查询，将会得到如下结果：

id	name	tag
1	Item 1	tag1
1	Item 1	tag2
1	Item 1	tag3
2	Item 2	tag2
2	Item 2	tag4
3	Item 3	tag1
3	Item 3	tag3
3	Item 3	tag5

3.2 使用递归CTE（通用方法）

如果使用的是不支持STRING_SPLIT函数的数据库或版本较低，可以使用递归CTE（Common Table Expression）来完成拆分逗号分隔的列为多行的操作。

以下是使用递归CTE的SQL语句：

WITH recursive_cte AS (
  SELECT id, name,
    LEFT(tags, CHARINDEX(',',tags+',')-1) AS tag,
    STUFF(tags, 1, CHARINDEX(',',tags+','), '') AS remaining_tags
  FROM items
  WHERE tags IS NOT NULL AND tags <> ''
  UNION ALL
  SELECT id, name,
    LEFT(remaining_tags, CHARINDEX(',',remaining_tags+',')-1) AS tag,
    STUFF(remaining_tags, 1, CHARINDEX(',',remaining_tags+','), '') AS remaining_tags
  FROM recursive_cte
  WHERE remaining_tags <> ''
)
SELECT id, name, tag
FROM recursive_cte;

以上SQL语句使用了递归CTE来实现拆分逗号分隔的列为多行的操作。通过递归的方式，将逗号分隔的值一个个拆分出来，并与原始数据关联。

运行以上查询，将会得到与之前示例相同的结果。