时间戳

首页 > 解决方案 > 使用 STRING_AGG 选择不同的值

sql-server - 使用 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]

但是当我运行它时，我得到了重复的客户 在此处输入图像描述

我怎样才能获得一个独特的客户？

标签： sql-server

解决方案

答案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]

答案 2：

With CustomersName as (
    SELECT
        [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]
)
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]
  ,CustomersName.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 CustomersName on CustomersName.ID=info.FK_Customer
Group By CustomersName.AllCustomer, [GalvaQualityDailyFicheControle].[CreationDate]

推荐阅读