仅在使用时显示唯一值

Question

我有以下查询：

with GTS_cte AS
    (SELECT distinct [BusinessTermID], GTS_T = 
    STUFF                   ((SELECT ', ' + dbo.TblField.GTS_table
                             FROM      dbo.TblField
                             WHERE   [BusinessTermID] = Y.[BusinessTermID] AND dbo.TblField.GTS_table <> '' FOR XML PATH('')), 1, 2, '')
     FROM      dbo.Tblfield AS Y
     GROUP BY [BusinessTermID])

    ,
  syn_cte as (
SELECT [BusinessTermID], syns = STUFF
                      ((SELECT ', ' + dbo.TblBusinessSynonym.Synonym
                        FROM      dbo.TblBusinessSynonym
                        WHERE   [BusinessTermID] = x.[BusinessTermID] AND dbo.TblBusinessSynonym.Synonym <> '' FOR XML PATH('')), 1, 2, '')
FROM     dbo.TblBusinessSynonym AS x
GROUP BY [BusinessTermID])


select syn_cte.BusinessTermID, syn_cte.syns, GTS_cte.GTS_T 
from  syn_cte  join 
  GTS_cte on GTS_cte.BusinessTermID = syn_cte.BusinessTermID

它正在正确地连接字段并将它们链接起来，但现在它正在创建重复的字段。我的结果集如下：

​

​

是否有一种方法可以在GTS_T中只显示唯一的值？

谢谢

Answer 1

在您的DISTINCT中使用Sub-Query.

试试这个：

;with GTS_cte AS
    (SELECT [BusinessTermID], GTS_T = 
    STUFF                   ((SELECT DISTINCT ', ' + dbo.TblField.GTS_table
                             FROM      dbo.TblField
                             WHERE   [BusinessTermID] = Y.[BusinessTermID] AND dbo.TblField.GTS_table <> '' FOR XML PATH('')), 1, 2, '')
     FROM      dbo.Tblfield AS Y
     GROUP BY [BusinessTermID])

    ,
  syn_cte as (
SELECT [BusinessTermID], syns = STUFF
                      ((SELECT DISTINCT ', ' + dbo.TblBusinessSynonym.Synonym
                        FROM      dbo.TblBusinessSynonym
                        WHERE   [BusinessTermID] = x.[BusinessTermID] AND dbo.TblBusinessSynonym.Synonym <> '' FOR XML PATH('')), 1, 2, '')
FROM     dbo.TblBusinessSynonym AS x
GROUP BY [BusinessTermID])


select syn_cte.BusinessTermID, syn_cte.syns, GTS_cte.GTS_T 
from  syn_cte  join 
  GTS_cte on GTS_cte.BusinessTermID = syn_cte.BusinessTermID