业务大概是4个资产,查询对应的其他自定义属性字段,但是统计出来就不是4行数据了,后面参数只有一行为空,如图:
实现将为空的列整合成一列,效果如图:
这是最后统计出来的结果,附SQL:
SELECT a.asset_uuid,
a.asset_name, -- 资产信息
CASE
WHEN STRING_AGG(xrc_1.col1, '') IS NOT NULL THEN STRING_AGG(xrc_1.col1, '')
ELSE STRING_AGG(xrc_2.col1, '') END AS "xrc",
CASE
WHEN STRING_AGG(fsw_1.col1, '') IS NOT NULL THEN STRING_AGG(fsw_1.col1, '')
ELSE STRING_AGG(fsw_2.col1, '') END AS "fsw", -- 应用系统
b.product,
c.vendor_name,
STRING_AGG(DISTINCT m.name, '') AS name,
STRING_AGG(DISTINCT m.phone, '') AS phone,
STRING_AGG(DISTINCT m.email, '') AS email
FROM internal_asset.cbb_asset_info a
LEFT JOIN internal_asset.cbb_extension_value AS ex ON a.asset_uuid = ex.asset_uuid
LEFT JOIN internal_asset.tbl_component_xrc AS xrc_1 ON ex.extension_index = xrc_1.id AND ex.register_id = 10000112
LEFT JOIN internal_asset.tbl_component_xrc AS xrc_2 ON ex.extension_index = xrc_2.id AND ex.register_id = 10000114
LEFT JOIN internal_asset.tbl_component_fsw AS fsw_1 ON ex.extension_index = fsw_1.id AND ex.register_id = 10000106
LEFT JOIN internal_asset.tbl_component_fsw AS fsw_2 ON ex.extension_index = fsw_2.id AND ex.register_id = 10000104
LEFT JOIN internal_app_tvm.assetvuls b ON a.asset_uuid = b.asset_uuid
LEFT JOIN internal_app_vulnmgr.vuln_vendor c ON c.id = b.product
LEFT JOIN internal_asset.tbl_manager m ON m.id = ex.extension_index AND ex.register_id = 13
GROUP BY a.asset_uuid, a.asset_name, b.product, c.vendor_name