如何在聚合字段中获取重复记录

debugcn 发表于 Dev

康斯坦蒂

我准备了一个查询：

SELECT
building_custom."Location Code",
building_custom."RUD Site Name",
string_agg(concat_ws('-',subnet.range_begin,subnet.range_end),',')
FROM building
INNER JOIN building_custom ON building.building_pk=building_custom.building_fk
INNER JOIN device ON building.building_pk=device.building_fk
INNER JOIN ipaddress ON device.device_pk=ipaddress.device_fk
INNER JOIN subnet ON ipaddress.subnet_fk=subnet.subnet_pk
GROUP BY
building_custom."Location Code",
building_custom."RUD Site Name"

我得到的结果是：

DBA;BUENOS AIRES-ARENALES;141.167.161.17-141.167.161.18,10.120.10.1-10.120.10.30,10.120.11.1-10.120.11.254,141.167.161.25-141.167.161.30,10.120.16.1-10.120.16.254,10.120.10.1-10.120.10.30,10.120.16.1-10.120.16.254,10.120.11.1-10.120.11.254,0.0.0.1-255.255.255.254,141.167.161.25-141.167.161.30,141.167.161.25-141.167.161.30,10.120.11.1-10.120.11.254,10.120.16.1-10.120.16.254,141.167.161.21-141.167.161.22,0.0.0.1-255.255.255.254,10.120.10.1-10.120.10.30

最后一个字段（子网范围）包含重复值。

目标是获得如下结果：

DBA;BUENOS AIRES-ARENALES;0.0.0.1-255.255.255.254,10.120.10.1-10.120.10.30,10.120.11.1-10.120.11.254,10.120.16.1-10.120.16.254,141.167.161.17-141.167.161.18,141.167.161.21-141.167.161.22,141.167.161.25-141.167.161.30

起初我试图添加

DISTINCT ON (view_subnet_v1.range_begin)

但是后来我需要在 GROUP BY 中添加它，结果更糟：

DBA;BUENOS AIRES-ARENALES;0.0.0.1-255.255.255.254,0.0.0.1-255.255.255.254
DBA;BUENOS AIRES-ARENALES;10.120.10.1-10.120.10.30,10.120.10.1-10.120.10.30,10.120.10.1-10.120.10.30
DBA;BUENOS AIRES-ARENALES;10.120.11.1-10.120.11.254,10.120.11.1-10.120.11.254,10.120.11.1-10.120.11.254
DBA;BUENOS AIRES-ARENALES;10.120.16.1-10.120.16.254,10.120.16.1-10.120.16.254,10.120.16.1-10.120.16.254
DBA;BUENOS AIRES-ARENALES;141.167.161.17-141.167.161.18
DBA;BUENOS AIRES-ARENALES;141.167.161.21-141.167.161.22
DBA;BUENOS AIRES-ARENALES;141.167.161.25-141.167.161.30,141.167.161.25-141.167.161.30,141.167.161.25-141.167.161.30

漂白

您可以尝试在内部查询中使用 distinct ：

 SELECT
    "Location Code",
    "RUD Site Name" ,
    string_agg(concat_ws('-',range_begin,range_end),',') from 
    (SELECT distinct 
    building_custom."Location Code",
    building_custom."RUD Site Name",
    subnet.range_begin
    ,subnet.range_end
    FROM building
    INNER JOIN building_custom ON building.building_pk=building_custom.building_fk
    INNER JOIN device ON building.building_pk=device.building_fk
    INNER JOIN ipaddress ON device.device_pk=ipaddress.device_fk
    INNER JOIN subnet ON ipaddress.subnet_fk=subnet.subnet_pk
) inner_q
    GROUP BY
    "Location Code",
    "RUD Site Name"

另外，如果你想有只可能使用最广泛的子网min，max而group by不是distinct在你的内心查询

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。