无重复的聚合

Question

我在这里要说的是：

表1: AOC_Model

AOC_ID int (Primary Key)
Model varchar(50)

表2: AOC_Chipset

AOC_CHIPSET_ID int (Primary Key)
CONTROLLER_ID int
CHIPSET_ID int
AOC_ID int

表3:控制器

CONTROLLER_ID int (Primary Key)
CONTROLLER varchar(10)

表4:芯片组

CONTROLLER_ID int (Primary Key)
CHIPSET_ID int (Primary Key)
CHIPSET varchar(50)

表5: Notes_Chipset

NOTES_CHIPSET_ID int (Primary Key)
CONTROLLER_ID int
CHIPSET_ID int
DATE date
NOTES varchar(800)

首先，我有一个芯片组表，它通过Controller_ID连接到控制器，然后我有AOC_Chipset，它实际上是控制器和芯片组之间的连接表。AOC_Chipset通过Controller_ID和Chipset_id连接到芯片组，然后是Notes_Chipset，它也通过Controller_ID和Chipset_id连接到芯片组，最后是AOC_Model，它通过AOC_ID连接到AOC_Chipset

我在这里有很多对很多的关系。我可以将一个或两个控制器分配给同一个AOC_ID，也可以将一个或两个芯片组分配给同一个控制器。我可以将多个Notes分配给同一芯片组。

我在SQL Server 2019中创建了以下查询：

SELECT
    dbo.AOC_CHIPSET.AOC_ID, 
    string_agg(dbo.CONTROLLER.CONTROLLER, ', ') AS vControllers, 
    string_agg(dbo.CHIPSET.CHIPSET, ', ') AS vChipsets, 
    string_agg(dbo.NOTES_CHIPSET.DATE, ', ') AS vDate,
    string_agg(dbo.NOTES_CHIPSET.NOTES, ', ') AS vNotes
FROM
    dbo.AOC_CHIPSET 
INNER JOIN 
    dbo.CHIPSET ON dbo.AOC_CHIPSET.CONTROLLER_ID = dbo.CHIPSET.CONTROLLER_ID 
                AND dbo.AOC_CHIPSET.CHIPSET_ID = dbo.CHIPSET.CHIPSET_ID 
INNER JOIN 
    dbo.CONTROLLER ON dbo.CHIPSET.CONTROLLER_ID = dbo.CONTROLLER.CONTROLLER_ID 
INNER JOIN 
    dbo.NOTES_CHIPSET ON dbo.CHIPSET.CONTROLLER_ID = dbo.NOTES_CHIPSET.CONTROLLER_ID  
                      AND dbo.CHIPSET.CHIPSET_ID = dbo.NOTES_CHIPSET.CHIPSET_ID 
                      AND dbo.CONTROLLER.CONTROLLER_ID = dbo.NOTES_CHIPSET.CONTROLLER_ID
GROUP BY 
    dbo.AOC_CHIPSET.AOC_ID

问题是结果包含重复项

​

​

我知道我可以使用DISTINCT，但我不知道如何将它与string_agg结合使用

Answer 1

我将连接替换为直接放置在选择列表中的子选择。这允许我为每个检索到的属性选择不同的值。事实上，每个字符串总是有2个子选择要创建。内部的有一个SELECT DISTINCT，外部的有一个string_agg。内部子选择使用WHERE子句过滤其行，其中AOC_ID与主SELECT匹配

SELECT
    ac.AOC_ID,
    (SELECT string_agg(CONTROLLER, ', ') FROM
      (SELECT DISTINCT CONTROLLER
       FROM dbo.CONTROLLER co INNER JOIN dbo.AOC_CHIPSET ac1
          ON ac1.CONTROLLER_ID = co.CONTROLLER_ID
       WHERE ac1.AOC_ID = ac.AOC_ID) x) AS vControllers,
    (SELECT string_agg(CHIPSET, ', ') FROM
      (SELECT DISTINCT CHIPSET
       FROM dbo.CHIPSET cs INNER JOIN dbo.AOC_CHIPSET ac2 
          ON ac2.CONTROLLER_ID = cs.CONTROLLER_ID AND ac2.CHIPSET_ID = cs.CHIPSET_ID
       WHERE ac2.AOC_ID = ac.AOC_ID) y) AS vChipsets,
    (SELECT string_agg([DATE], ', ') FROM
      (SELECT DISTINCT [DATE]
       FROM dbo.NOTES_CHIPSET nd INNER JOIN dbo.AOC_CHIPSET ac3
          ON ac3.CONTROLLER_ID = nd.CONTROLLER_ID AND ac3.CHIPSET_ID = nd.CHIPSET_ID
       WHERE ac3.AOC_ID = ac.AOC_ID) z) AS vDate,
    (SELECT string_agg(NOTES, ', ') FROM
      (SELECT DISTINCT NOTES
       FROM dbo.NOTES_CHIPSET nd INNER JOIN dbo.AOC_CHIPSET ac4
          ON ac4.CONTROLLER_ID = nd.CONTROLLER_ID AND ac4.CHIPSET_ID = nd.CHIPSET_ID
       WHERE ac4.AOC_ID = ac.AOC_ID) z) AS vNotes
FROM
    dbo.AOC_CHIPSET ac
GROUP BY
    ac.AOC_ID

子查询中的SELECT DISTINCT可以工作，因为选择列表不包含任何控制器或芯片组id。对于外层的连接，这是不可能的，因为它们需要这些ids。

Answer 2

我不完全理解这个查询的目的/用法，如果这对我没有帮助，请原谅。但我不认为DISTINCT对您有多大用处，因为您的AOC_ID在重复数据的每一行中都是唯一的值。如果去掉了AOC_ID，那么就可以使用DISTINCT，而不必担心数据会出现两次。但是，如果在这个查询中不能去掉AOC_ID，那么我和您一样被难住了。

Answer 3

SELECT MAX(AOC_ID) as AOC_ID, vControllers, vChipsets, vDate, vNotes FROM
(
 SELECT
    dbo.AOC_CHIPSET.AOC_ID, 
    string_agg(dbo.CONTROLLER.CONTROLLER, ', ') AS vControllers, 
    string_agg(dbo.CHIPSET.CHIPSET, ', ') AS vChipsets, 
    string_agg(dbo.NOTES_CHIPSET.DATE, ', ') AS vDate,
    string_agg(dbo.NOTES_CHIPSET.NOTES, ', ') AS vNotes
FROM
    dbo.AOC_CHIPSET 
INNER JOIN 
    dbo.CHIPSET ON dbo.AOC_CHIPSET.CONTROLLER_ID = dbo.CHIPSET.CONTROLLER_ID 
                AND dbo.AOC_CHIPSET.CHIPSET_ID = dbo.CHIPSET.CHIPSET_ID 
INNER JOIN 
    dbo.CONTROLLER ON dbo.CHIPSET.CONTROLLER_ID = dbo.CONTROLLER.CONTROLLER_ID 
INNER JOIN 
    dbo.NOTES_CHIPSET ON dbo.CHIPSET.CONTROLLER_ID = dbo.NOTES_CHIPSET.CONTROLLER_ID  
                      AND dbo.CHIPSET.CHIPSET_ID = dbo.NOTES_CHIPSET.CHIPSET_ID 
                      AND dbo.CONTROLLER.CONTROLLER_ID = dbo.NOTES_CHIPSET.CONTROLLER_ID
GROUP BY 
    dbo.AOC_CHIPSET.AOC_ID
) R
GROUP BY vControllers, vChipsets, vDate, vNotes