如何去除SQL中string_agg()函数返回的重复值？

原存储过程代码 #

declare @unit  int
declare  @department int
declare @section int
declare @block int
declare @empId int
declare  @fromdate date
declare @todate date

if @unit = 0 set @unit = null
if @department = 0 set @department = null
if @section = 0 set @section = null
if @block = 0 set @block = null
if @empId = 0 set @empId = null
set @fromdate = '01/apr/2023'
set @todate = '20/apr/2023'

begin       
  SELECT       
      b2_department_info.department, b2_section_info.section,b2_block_info.block,b2_emp_basic.emp_cardno,b2_emp_basic.emp_name,      
      string_agg(day(b2_floor_production_list.prod_date),',') as ProdDate,b2_style_info.style,b2_process_info.process_name,
      sum(b2_floor_production_list.quantity) as Quantity,
      b2_company_info.company_name,b2_building_info.building_name, @fromdate as fromdate, @todate as todate
  FROM            
      b2_floor_production_list INNER JOIN
      b2_process_info ON b2_floor_production_list.process = b2_process_info.process_id INNER JOIN
      b2_style_info ON b2_process_info.style = b2_style_info.style_id INNER JOIN
      b2_emp_basic ON b2_floor_production_list.emp_id = b2_emp_basic.emp_id INNER JOIN
      b2_department_info ON b2_emp_basic.department = b2_department_info.deptId INNER JOIN
      b2_section_info ON b2_emp_basic.section = b2_section_info.section_id INNER JOIN
      b2_block_info ON b2_emp_basic.block = b2_block_info.blockId INNER JOIN
      b2_designation_info ON b2_emp_basic.designation = b2_designation_info.desigId INNER JOIN
      b2_building_info ON b2_emp_basic.unit = b2_building_info.building_id inner join
      b2_company_info on b2_building_info.company=b2_company_info.company_id                      
  where (b2_floor_production_list.prod_date >= CONVERT(date,@fromdate))
  and (b2_floor_production_list.prod_date <= CONVERT(date,@todate))
  and (b2_floor_production_list.emp_id = @empId or @empId is null)
  and (b2_building_info.building_id = @unit or @unit is null)
  and (b2_department_info.deptId = @department or @department is null)
  and (b2_floor_production_list.section = @section or @section is null)
  and (b2_block_info.blockId = @block or @block is null)
  group by b2_department_info.department, b2_section_info.section,b2_block_info.block,b2_emp_basic.emp_cardno,b2_emp_basic.emp_name,b2_style_info.style,b2_process_info.process_name,b2_company_info.company_name,b2_building_info.building_name
end

问题说明 #

执行上述存储过程时，ProdDate字段通过string_agg(day(b2_floor_production_list.prod_date),',')生成的日期存在重复值，尝试使用string_agg(distinct day(b2_floor_production_list.prod_date),',')去重但无效。

解决方案 #

方案1：先对生产记录按分组维度去重，再聚合 #

问题根源是b2_floor_production_list中存在同一员工、样式、工序、日期下的多条记录，直接聚合会导致日期重复。通过CTE先获取每个分组维度下的唯一日期和对应数量总和，再关联其他表查询：

declare @unit  int
declare  @department int
declare @section int
declare @block int
declare @empId int
declare  @fromdate date
declare @todate date

if @unit = 0 set @unit = null
if @department = 0 set @department = null
if @section = 0 set @section = null
if @block = 0 set @block = null
if @empId = 0 set @empId = null
set @fromdate = '01/apr/2023'
set @todate = '20/apr/2023'

begin       
  WITH ProductionSummary AS (
      SELECT 
          emp_id,
          process,
          section,
          prod_date,
          SUM(quantity) AS daily_quantity
      FROM b2_floor_production_list
      WHERE prod_date >= CONVERT(date,@fromdate)
        AND prod_date <= CONVERT(date,@todate)
        AND (emp_id = @empId OR @empId IS NULL)
        AND (section = @section OR @section IS NULL)
      GROUP BY emp_id, process, section, prod_date
  )
  SELECT       
      d.department, 
      s.section,
      bl.block,
      eb.emp_cardno,
      eb.emp_name,      
      STRING_AGG(day(ps.prod_date), ',') AS ProdDate,
      st.style,
      p.process_name,
      SUM(ps.daily_quantity) AS Quantity,
      c.company_name,
      b.building_name, 
      @fromdate AS fromdate, 
      @todate AS todate
  FROM ProductionSummary ps
  INNER JOIN b2_process_info p ON ps.process = p.process_id 
  INNER JOIN b2_style_info st ON p.style = st.style_id 
  INNER JOIN b2_emp_basic eb ON ps.emp_id = eb.emp_id 
  INNER JOIN b2_department_info d ON eb.department = d.deptId 
  INNER JOIN b2_section_info s ON eb.section = s.section_id 
  INNER JOIN b2_block_info bl ON eb.block = bl.blockId 
  INNER JOIN b2_designation_info dg ON eb.designation = dg.desigId 
  INNER JOIN b2_building_info b ON eb.unit = b.building_id 
  INNER JOIN b2_company_info c ON b.company = c.company_id                      
  WHERE (b.building_id = @unit OR @unit IS NULL)
    AND (d.deptId = @department OR @department IS NULL)
    AND (bl.blockId = @block OR @block IS NULL)
  GROUP BY d.department, s.section, bl.block, eb.emp_cardno, eb.emp_name, st.style, p.process_name, c.company_name, b.building_name
end

方案2：针对低版本SQL Server的去重处理 #

如果你的SQL Server版本低于2017（不支持STRING_AGG(DISTINCT...)），可以通过两个CTE分别处理唯一日期和数量总和，再关联查询：

declare @unit  int
declare  @department int
declare @section int
declare @block int
declare @empId int
declare  @fromdate date
declare @todate date

if @unit = 0 set @unit = null
if @department = 0 set @department = null
if @section = 0 set @section = null
if @block = 0 set @block = null
if @empId = 0 set @empId = null
set @fromdate = '01/apr/2023'
set @todate = '20/apr/2023'

begin       
  WITH UniqueProdDates AS (
      SELECT DISTINCT
          eb.emp_cardno,
          eb.emp_name,
          d.department,
          s.section,
          bl.block,
          st.style,
          p.process_name,
          c.company_name,
          b.building_name,
          day(fpl.prod_date) AS prod_day
      FROM b2_floor_production_list fpl
      INNER JOIN b2_process_info p ON fpl.process = p.process_id 
      INNER JOIN b2_style_info st ON p.style = st.style_id 
      INNER JOIN b2_emp_basic eb ON fpl.emp_id = eb.emp_id 
      INNER JOIN b2_department_info d ON eb.department = d.deptId 
      INNER JOIN b2_section_info s ON eb.section = s.section_id 
      INNER JOIN b2_block_info bl ON eb.block = bl.blockId 
      INNER JOIN b2_building_info b ON eb.unit = b.building_id 
      INNER JOIN b2_company_info c ON b.company = c.company_id                      
      WHERE fpl.prod_date >= CONVERT(date,@fromdate)
        AND fpl.prod_date <= CONVERT(date,@todate)
        AND (fpl.emp_id = @empId OR @empId IS NULL)
        AND (b.building_id = @unit OR @unit IS NULL)
        AND (d.deptId = @department OR @department IS NULL)
        AND (fpl.section = @section OR @section IS NULL)
        AND (bl.blockId = @block OR @block IS NULL)
  ),
  QuantitySummary AS (
      SELECT 
          eb.emp_cardno,
          eb.emp_name,
          d.department,
          s.section,
          bl.block,
          st.style,
          p.process_name,
          c.company_name,
          b.building_name,
          SUM(fpl.quantity) AS Quantity
      FROM b2_floor_production_list fpl
      INNER JOIN b2_process_info p ON fpl.process = p.process_id 
      INNER JOIN b2_style_info st ON p.style = st.style_id 
      INNER JOIN b2_emp_basic eb ON fpl.emp_id = eb.emp_id 
      INNER JOIN b2_department_info d ON eb.department = d.deptId 
      INNER JOIN b2_section_info s ON eb.section = s.section_id 
      INNER JOIN b2_block_info bl ON eb.block = bl.blockId 
      INNER JOIN b2_building_info b ON eb.unit = b.building_id 
      INNER JOIN b2_company_info c ON b.company = c.company_id                      
      WHERE fpl.prod_date >= CONVERT(date,@fromdate)
        AND fpl.prod_date <= CONVERT(date,@todate)
        AND (fpl.emp_id = @empId OR @empId IS NULL)
        AND (b.building_id = @unit OR @unit IS NULL)
        AND (d.deptId = @department OR @department IS NULL)
        AND (fpl.section = @section OR @section IS NULL)
        AND (bl.blockId = @block OR @block IS NULL)
      GROUP BY eb.emp_cardno, eb.emp_name, d.department, s.section, bl.block, st.style, p.process_name, c.company_name, b.building_name
  )
  SELECT 
      upd.department,
      upd.section,
      upd.block,
      upd.emp_cardno,
      upd.emp_name,
      STRING_AGG(upd.prod_day, ',') AS ProdDate,
      upd.style,
      upd.process_name,
      qs.Quantity,
      upd.company_name,
      upd.building_name,
      @fromdate AS fromdate,
      @todate AS todate
  FROM UniqueProdDates upd
  INNER JOIN QuantitySummary qs ON upd.emp_cardno = qs.emp_cardno 
                              AND upd.style = qs.style 
                              AND upd.process_name = qs.process_name
  GROUP BY upd.department, upd.section, upd.block, upd.emp_cardno, upd.emp_name, upd.style, upd.process_name, qs.Quantity, upd.company_name, upd.building_name
end

补充说明 #

若使用SQL Server 2017及以上版本，直接使用STRING_AGG(DISTINCT day(b2_floor_production_list.prod_date), ',')理论上有效；若仍无效，说明关联表导致了重复行，此时优先采用方案1，从主表数据层面去重，能彻底解决重复问题。

内容的提问来源于stack exchange，提问作者Nur Hossain Sujon