SELECT

t.id,
t.brand_name,
t.cat_no,
t.en_name,
t.cn_name,
t.cas,
t.catalog_array,
case when 'tanmo' = any(tmp.brand_arr) then '有'
else '无' end "坛墨官网是否有",
case when 'anpel' = any(tmp.brand_arr) then '有'
else '无' end "安谱官网是否有",
-- TODO 海岸鸿蒙 中国,中检所
tmp.brand_arr,
匹配规则

FROM tmp."202212危险品匹配" t

LEFT JOIN (
    SELECT
    t.id,
    ARRAY_AGG(distinct prd.brand) brand_arr,
    array_agg(distinct prd.cat_no) cat_no_arr,
    array_agg(distinct case
    when t.cas != 'N/A' and t.cas = prd.cas then 'cas'
    when t.cn_name = prd.chs_name then '中文名'
    when t.en_name = prd.en_name then '英文名'
    else null end) "匹配规则"

    FROM tmp."202212危险品匹配" t

    INNER JOIN rawdata prd
    ON prd.brand != 'cato'
    AND (
        t.en_name = prd.en_name
        OR t.cn_name = prd.chs_name
        OR (t.cas != 'N/A' and t.cas = prd.cas)
    )
    GROUP BY t.id

) tmp
ON tmp.id = t.id