sql合并重复数合并为一条

K-seo • 2024-05-21 10:45 • 行业资讯 • 93 views

要合并SQL中的重复数据，可以使用GROUP BY和COUNT()函数，将重复的记录合并为一条。

在SQL Server中，我们经常会遇到需要将重复行数据合并为一行并用逗号分隔的情况，这种情况通常发生在数据分析或者报表生成等场景中，为了实现这个需求，我们可以使用SQL Server中的一些内置函数和操作符，如GROUP BY、STRING_AGG等，下面我将详细介绍如何使用这些函数和操作符来实现这个功能。

1. 使用GROUP BY和STRING_AGG函数

我们需要使用GROUP BY子句对数据进行分组，我们可以使用STRING_AGG函数将每个分组中的重复行数据合并为一行并用逗号分隔。

假设我们有一个名为sales_data的表，其中包含以下字段：product_id（产品ID）、sale_date（销售日期）和sale_amount（销售金额），我们想要将每个产品ID的销售数据合并为一行并用逗号分隔。

SELECT product_id, STRING_AGG(sale_date, ', ') AS sale_dates, STRING_AGG(sale_amount, ', ') AS sale_amounts
FROM sales_data
GROUP BY product_id;

在这个查询中，我们首先使用GROUP BY子句按照product_id对数据进行分组，我们使用STRING_AGG函数将每个分组中的sale_date和sale_amount字段合并为一行并用逗号分隔。

2. 使用FOR XML PATH和STUFF函数

除了使用STRING_AGG函数外，我们还可以使用FOR XML PATH和STUFF函数来实现相同的功能。

SELECT product_id, STUFF((SELECT ', ' + CAST(sale_date AS NVARCHAR(MAX))
                     FROM sales_data as s2
                     WHERE s2.product_id = sales_data.product_id
                     FOR XML PATH('')), 1, 2, '') AS sale_dates,
       STUFF((SELECT ', ' + CAST(sale_amount AS NVARCHAR(MAX))
               FROM sales_data as s2
               WHERE s2.product_id = sales_data.product_id
               FOR XML PATH('')), 1, 2, '') AS sale_amounts
FROM sales_data;

在这个查询中，我们首先使用FOR XML PATH子句将每个分组中的sale_date和sale_amount字段转换为一个XML字符串，我们使用STUFF函数将这些XML字符串合并为一行并用逗号分隔。

3. 使用自定义函数

如果我们需要在多个查询中使用这个功能，可以考虑创建一个自定义函数，以下是创建和使用自定义函数的示例：

CREATE FUNCTION dbo.CombineStrings (@stringList NVARCHAR(MAX), @delimiter NVARCHAR(10))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @result NVARCHAR(MAX) = '';
    DECLARE @index INT = 1;
    DECLARE @length INT = LEN(@stringList);
    WHILE @index <= @length BEGIN
        SET @result = @result + LTRIM(RTRIM(SUBSTRING(@stringList, @index, CASE WHEN @index != @length THEN 1 ELSE LEN(@stringList) @index + 1 END))) + @delimiter;
        SET @index = @index + LEN(@delimiter);
    END;
    RETURN RTRIM(LTRIM(@result));
END;

现在，我们可以在查询中使用这个自定义函数来合并字符串：

SELECT product_id, dbo.CombineStrings(CAST(sale_date AS NVARCHAR(MAX)), ', ') AS sale_dates, dbo.CombineStrings(CAST(sale_amount AS NVARCHAR(MAX)), ', ') AS sale_amounts
FROM sales_data;

4. 使用PIVOT操作符（仅适用于具有固定数量的列的情况）

如果数据具有固定数量的列，并且我们希望将每个列的值合并为一行并用逗号分隔，可以使用PIVOT操作符，以下是使用PIVOT操作符的示例：

SELECT * FROM (VALUES (1, '20220101', 100), (1, '20220102', 200), (2, '20220101', 300), (2, '20220102', 400)) AS sales_data (product_id, sale_date, sale_amount)
PIVOT (MAX(sale_amount) FOR sale_date IN ([20220101], [20220102])) AS pvt;

在这个查询中，我们首先使用VALUES子句创建了一个临时表sales_data，我们使用PIVOT操作符将sale_date字段的值转换为列名，并将对应的sale_amount值合并为一行并用逗号分隔。