在SQL Server中使用STRING

本文介绍了在SQL Server中使用STRING_AGG获取唯一值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

以下查询返回如下所示的结果:

The following query returns the results shown below:

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

使用新添加的STRING_AGG函数(在SQL Server 2017中)，如以下查询所示，我可以在下面获取结果集.

Using the newly added STRING_AGG function (in SQL Server 2017) as it is shown in the following query I am able to get the result-set below.

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

我希望最终输出中仅包含以下独特元素:

I would like my final output to have only unique elements as below:

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

关于如何获得此结果的任何建议?如果需要，请随时从头开始进行优化/重新设计.

Any suggestions about how to get this result? Please feel free to refine/redesign from scratch my query if needed.

推荐答案

在合并结果之前，在子查询中使用DISTINCT关键字删除重复项: SQL小提琴

Use the DISTINCT keyword in a subquery to remove duplicates before combining the results: 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