SQL 一行拆分成多行

小蓝 • 2024-12-12 12:23 • 编程

SQL 语言是访问和处理关系型数据的一种标准语言，SQL 一行拆分成多行常见于一些需要用到多行的sql查询。

一、将一行数据转换成多行

在实际的业务中，往往会遇到需要将数据进行拆分成多行的情况。例如，一条记录包括多个联系人，需要将联系人从一行数据拆分成多行。这时候我们可以使用 CROSS APPLY 函数，将文本字符串转换成行集合。

SELECT * 
FROM   (
           SELECT 'John,Male,30' AS Info 
           UNION ALL 
           SELECT 'Kelly,Female,25' AS Info 
           UNION ALL 
           SELECT 'James,Male,35' AS Info 
       ) T 
       CROSS APPLY
       (
           SELECT f.item  
           FROM STRING_SPLIT(t.info, ',') AS f
       ) x

在上述代码中，CROSS APPLY 函数根据逗号分割将文本串进行转换，变成多行数据。

二、 SQL 拆分多行

此时我们再看一个反向的操作，即将多行数据拆分成单行字符串。在实际业务中，往往会存在一些需要将多行数据转化为字符串的需求，例如生成数据的报表。为此，我们可以使用 STUFF 和 FOR XML PATH函数来完成这个过程。

DECLARE @Data TABLE
(
   Id    INT,
   Name  VARCHAR(50),
   Phone VARCHAR(50)
);

INSERT INTO @Data
VALUES (1, 'John', '111-111-1111'),
       (2, 'Frank', '222-222-2222'),
       (3, 'Kelly', '333-333-3333');

SELECT Id, 
       STUFF((SELECT '; ' + Phone 
              FROM   @Data 
              WHERE  Id = d.Id 
              FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS Phones
FROM   @Data AS d;

在上述代码中，STUFF 函数将多行数据拼接成一行字符串，而 FOR XML PATH 元素将每个值转化为XML元素。

三、 SQL 多行合并成一行

反之，我们也可以将多行数据合并成单行字符串，这时候就需要用到 GROUP_CONCAT 函数。

SELECT NAME, 
GROUP_CONCAT(DISTINCT Phone 
ORDER BY Phone DESC 
SEPARATOR '|') AS Phonelist
FROM   @Data 
GROUP  BY Name;

在上述代码中，GROUP_CONCAT 函数将所有数据行合并成单行，分隔符为 |。

四、SQL 逗号分割转成多行

在实际业务中，常常会遇到一些需要将逗号分隔字符串转换成多行数据的需求。这时候，我们可以使用 STRING_SPLIT 函数来完成这个操作：

SELECT value 
FROM   STRING_SPLIT('John,Kelly,James', ',')

这段代码可以将以逗号分隔的字符串拆分成多行。

五、SQL 一行拆分成多行的优化

在实际业务中，为了提高查询性能，我们通常需要对查询进行优化。首先需要确保语句的正确性。其次需要根据数据量的大小和查询频率来选择合适的索引。最后，我们可以使用分批处理（分页）来避免在查询大量数据时产生内存溢出等问题。

例如，在使用 STUFF 函数进行数据拼接时，如果一次拼接的数据过大，那么很容易就会出现内存溢出的问题。这时我们可以使用分批处理，每次只处理一定数量的数据，避免内存压力过大。

DECLARE @Start INT = 1;
DECLARE @End INT = 100;
 
WHILE (@Start <= (SELECT MAX(Id) FROM @Data))
BEGIN
    SELECT Id, STUFF((SELECT ', ' + Phone
                       FROM @Data
                       WHERE Id BETWEEN @Start AND @End
                       FOR XML PATH('')), 1, 1, '') AS Phones
    FROM @Data
    WHERE Id BETWEEN @Start AND @End;
 
    SET @Start = @End + 1;
    SET @End = @End + 100;
END;

在上述代码中，我们将大量数据分割成若干个小批次，以减少每次查询的数据量，提高查询性能。

六、总结

SQL 一行拆分成多行的应用十分常见，并且在实际业务操作中也十分必要。我们可以通过多种方式来实现这一操作，包括使用 CROSS APPLY 函数、 STUFF 和 FOR XML PATH函数、 GROUP_CONCAT 函数以及 STRING_SPLIT 函数。同时，在查询性能优化上，需要注意语句的正确性、索引选择以及分批处理等因素。

原创文章，作者：小蓝，如若转载，请注明出处：https://www.506064.com/n/240879.html