本文介绍了在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更多推荐
在SQL Server中使用STRING
发布评论