如何避免在STRING_AGG函数中重复

问题描述

我的查询如下:

select
    u.Id,
    STRING_AGG(sf.Naziv, ', ') as 'Ustrojstvena jedinica',
    ISNULL(CONVERT(varchar(200), (STRING_AGG(TRIM(p.Naziv), ', ')), 121), '')
    as 'Partner',

from Ugovor as u

        left join VezaUgovorPartner as vup
            on vup.UgovorId = u.Id AND vup.IsDeleted = 'false'
        left join [TEST_MaticniPodaci2].dbo.Partner as p
            on p.PartnerID = vup.PartnerId
        left join [dbo].[VezaUgovorUstrojstvenaJedinica] as vuu
            on vuu.UgovorId = u.Id
        left join [TEST_MaticniPodaci2].hcphs.SifZavod as sf
            on sf.Id = vuu.UstrojstvenaJedinicaId
        left join [dbo].[SifVrstaUgovora] as vu
            on u.VrstaUgovoraId = vu.Id

  group by u.Id, sf.Naziv

我的问题是我可以拥有更多纳粹文物，也只能拥有一个纳粹文物，因此我必须检查是否有一个，然后仅显示一个结果，是否有两个或更多个，以显示更多结果.但是现在的问题是，当我只有一个纳粹(sf.Naziv)时，查询返回两个同名的纳粹(sf.Naziv)，因为在第一个STRING_AGG中，我有关于p.Naziv的更多记录.

My problem is that I can have more sf.Naziv and also only one sf.Naziv so I have to check if there is one and then show only one result and if there is two or more to show more results. But for now the problem is when I have only one sf.Naziv, query returns two sf.Naziv with the same name because in first STRING_AGG i have more records about p.Naziv.

我不知道如何在 STRING_AGG 函数中实现 DISTINCT

I have no idea how to implement DISTINCT into STRING_AGG function

欢迎使用其他任何解决方案，但我认为它应该可以与DISTINCT函数一起使用.

推荐答案

貌似不起作用，所以您应该做的是将整个查询放在子查询中，删除其中的重复项，然后执行STRING_AGG没有重复的数据.

It looks like distinct won't work, so what you should do is put your whole query in a subquery, remove the duplicates there, then do STRING_AGG on the data that has no duplicates.

SELECT STRING_AGG(data)
FROM (
   SELECT DISTINCT FROM ...
)

这篇关于如何避免在STRING_AGG函数中重复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！