开发者社区问答正文

使用STRING_AGG选择不同的值

我想选择不同的客户，所以我写了这个查询：

SELECT 
   STRING_AGG( ISNULL(Concat(Year(info.[CreationDate]),'/',Trim('BS-' from info.ProjectN)) , ' '), ' ;')  As 'AllProjectN'
  ,STRING_AGG( ISNULL(part.Designation , ' '), ' ;')  As 'AllDesignation' 
  ,STRING_AGG( ISNULL([GalvaQualityDailyFicheControle].[Quantity] , ' '), ' ;')  As 'AllQuantity'
  ,[GalvaQualityDailyFicheControle].[CreationDate]

  ,(SELECT STRING_AGG( ISNULL(cust.Name , ' '), ' ;')
   WITHIN GROUP (ORDER BY cust.Name)
   FROM (SELECT distinct cust.Name FROM Customer)as x) AS AllCustomer

  FROM [dbo].[GalvaQualityDailyFicheControle]
  Inner Join GalvaQualityDailyProduction prod on prod.id= 
  [GalvaQualityDailyFicheControle].FK_idDailyProduction
  Inner join GalvaParts part on part.id=prod.[FK_idPart]
  Inner join ProjectInfoGalva info on info.id=part.IdProject
  inner Join Customer cust on cust.ID=info.FK_Customer
  Group By cust.Name,[GalvaQualityDailyFicheControle].[CreationDate]

但是当我运行它时，我得到了重复的客户

展开

收起

祖安文状元 2020-01-04 15:21:05 1161 版权

1 条回答

写回答

取消提交回答

祖安文状元

答案1：

SELECT 
    STRING_AGG( ISNULL(Concat(Year(info.[CreationDate]),'/',Trim('BS-' from info.ProjectN)) , ' '), ' ;')  As 'AllProjectN'
    ,STRING_AGG( ISNULL(part.Designation , ' '), ' ;')  As 'AllDesignation' 
    ,STRING_AGG( ISNULL([GalvaQualityDailyFicheControle].[Quantity] , ' '), ' ;')  As 'AllQuantity'
    ,[GalvaQualityDailyFicheControle].[CreationDate]
    ,STRING_AGG( ISNULL(cust.Name , ' '), ' ;') WITHIN GROUP (ORDER BY cust.Name) AS AllCustomer
FROM [dbo].[GalvaQualityDailyFicheControle]
Inner Join GalvaQualityDailyProduction prod on prod.id = [GalvaQualityDailyFicheControle].FK_idDailyProduction
Inner join GalvaParts part on part.id=prod.[FK_idPart]
Inner join ProjectInfoGalva info on info.id=part.IdProject
inner Join Customer cust on cust.ID=info.FK_Customer
Group By [GalvaQualityDailyFicheControle].[CreationDate]