在SQL Server中使用STRING_AGG获取唯一值

Question

在SQL Server中使用STRING_AGG获取唯一值

sqlsql-serversql-server-2017string-aggregation

57

以下查询返回以下结果：

SELECT 
    ProjectID, newID.value
FROM 
    [dbo].[Data] WITH(NOLOCK)  
CROSS APPLY 
    STRING_SPLIT([bID],';') AS newID  
WHERE 
    newID.value IN ('O95833', 'Q96NY7-2')

结果：

ProjectID   value
---------------------
2           Q96NY7-2
2           O95833
2           O95833
2           Q96NY7-2
2           O95833
2           Q96NY7-2
4           Q96NY7-2
4           Q96NY7-2

使用 SQL Server 2017 中新添加的 STRING_AGG 函数，就像下面的查询所示，我可以得到以下结果集。

SELECT 
    ProjectID,
    STRING_AGG( newID.value, ',') WITHIN GROUP (ORDER BY newID.value) AS 
NewField
FROM
    [dbo].[Data] WITH(NOLOCK)  
CROSS APPLY 
    STRING_SPLIT([bID],';') AS newID  
WHERE 
    newID.value IN ('O95833', 'Q96NY7-2')  
GROUP BY 
    ProjectID
ORDER BY 
    ProjectID

结果：

ProjectID   NewField
-------------------------------------------------------------
2           O95833,O95833,O95833,Q96NY7-2,Q96NY7-2,Q96NY7-2
4           Q96NY7-2,Q96NY7-2

我希望我的最终输出只包含唯一的元素，如下所示：

ProjectID   NewField
-------------------------------
2           O95833, Q96NY7-2
4           Q96NY7-2

你有任何如何获得这个结果的建议吗？如果需要，请随意修改/重新设计我的查询。

- gkoul

5

所以您有存储为分隔值的数据，现在希望将它们拆分、查找不同的值，最后将它们全部压缩回一个分隔字符串中？真恶心！分隔数据违反了第一范式。这就是为什么您在这里遇到了如此多的困难。在首先拆分数据之后，您将需要使用STUFF和FOR XML，并加入DISTINCT来完成这个任务。 - Sean Lange

SQL Fiddle: http://sqlfiddle.com/#!18/0b959/1 - JohnLBevan

有没有一个简单的例子可以展示如何在我的数据集中使用STUFF和FOR XML与DISTINCT？我无法避免使用STRING_SPLIT，因为原始数据存储为分隔值，正如你所意识到的那样。 - gkoul

2

小心使用NOLOCK提示。https://blogs.sentryone.com/aaronbertrand/bad-habits-nolock-everywhere/ - Sean Lange

8个回答

13

这是我编写的一个函数，用于回答OP标题：欢迎改进！

CREATE OR ALTER FUNCTION [dbo].[fn_DistinctWords]
(
  @String NVARCHAR(MAX)  
)
RETURNS NVARCHAR(MAX)
WITH SCHEMABINDING
AS
BEGIN
  DECLARE @Result NVARCHAR(MAX);
  WITH MY_CTE AS ( SELECT Distinct(value) FROM STRING_SPLIT(@String, ' ')  )
  SELECT @Result = STRING_AGG(value, ' ') FROM MY_CTE
  RETURN @Result
END
GO

使用方法:

SELECT dbo.fn_DistinctWords('One Two      Three Two One');

- ttugates

7

您可以在用于 apply 的子查询中使用 distinct：

SELECT d.ProjectID,
       STRING_AGG(  newID.value, ',') WITHIN GROUP (ORDER BY newID.value) AS 
NewField
FROM [dbo].[Data] d CROSS APPLY
     (select distinct value
      from STRING_SPLIT(d.[bID], ';') AS newID 
     ) newID
WHERE newID.value IN (   'O95833' , 'Q96NY7-2'  ) 
group by projectid;

- Gordon Linoff

如果除了需要拆分和聚合的列之外，您还有多个其他列，则这尤其有用。 - Sander de Jong

4

这是我对@ttugates的改进，使其更加通用：

CREATE OR ALTER FUNCTION [dbo].[fn_DistinctList]
(
  @String NVARCHAR(MAX),
  @Delimiter char(1)
)
RETURNS NVARCHAR(MAX)
WITH SCHEMABINDING
AS
BEGIN
  DECLARE @Result NVARCHAR(MAX);
  WITH MY_CTE AS ( SELECT Distinct(value) FROM STRING_SPLIT(@String, 
@Delimiter)  )
  SELECT @Result = STRING_AGG(value, @Delimiter) FROM MY_CTE
  RETURN @Result
END

- Domagoj Peharda

3

如评论中@SeanLange所指出的，这是一种糟糕的提取数据的方式，但如果您必须这样做，只需按以下方式进行2个单独查询：

SELECT 
    ProjectID
    ,STRING_AGG( val, ',') WITHIN GROUP (ORDER BY val) AS NewField
FROM
(
    SELECT DISTINCT 
        ProjectID
        ,newID.value AS val
    FROM 
        [dbo].[Data] WITH(NOLOCK)  
        CROSS APPLY STRING_SPLIT([bID],';') AS newID  
    WHERE 
        newID.value IN ('O95833' , 'Q96NY7-2') 
) t
GROUP BY
    ProjectID

那应该就可以了。

- John Bustos

2

使用STRING_AGG获取唯一字符串的另一个方法是，在获取逗号分隔的字符串后执行以下三个步骤：

拆分字符串（STRING_SPLIT）
从拆分中选择DISTINCT
再次使用STRING_AGG在单个键上进行分组选择

示例:

(select STRING_AGG(CAST(value as VARCHAR(MAX)), ',') 
        from (SELECT distinct 1 single_key, value 
            FROM STRING_SPLIT(STRING_AGG(CAST(customer_division as VARCHAR(MAX)), ','), ',')) 
                q group by single_key) as customer_division

- gil.fernandes

0

您可以创建一个独特的表视图，用于保存聚合值，这样会更加简单：

Create Table Test (field1 varchar(1), field2 varchar(1));

go

Create View DistinctTest as (Select distinct field1, field2 from test group by field1,field2);

go

insert into Test Select 'A', '1';
insert into Test Select 'A', '2';
insert into Test Select 'A', '2';
insert into Test Select 'A', '2';
insert into Test Select 'D', '1';
insert into Test Select 'D', '1';

select string_agg(field1, ',')  from Test where field2 = '1';  /* duplicates: A,D,D */;

select string_agg(field1, ',')  from DistinctTest where field2 = '1';  /* no duplicates: A,D  */;

- Thomas Riedel

-6

Oracle（自19c版本起）支持listagg（DISTINCT ...，但Microsoft SQL Server可能不支持。

- sasynkamil

5

OP并不是在询问Oracle相关的内容。 - Daniel L. VanDenBosch

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- JohnLBevan · Accepted Answer

在子查询中使用DISTINCT关键字，以便在组合结果之前删除重复项：SQL Fiddle

SELECT 
ProjectID
,STRING_AGG(value, ',') WITHIN GROUP (ORDER BY value) AS 
NewField
from (
    select distinct ProjectId, newId.value 
    FROM [dbo].[Data] WITH(NOLOCK)  
    CROSS APPLY STRING_SPLIT([bID],';') AS newID  
    WHERE newID.value IN (   'O95833' , 'Q96NY7-2'  )  
) x
GROUP BY ProjectID
ORDER BY ProjectID