postgresql - 子查询结果中的模式匹配

Question

我有三个表，即 ordrer 、 campaign_company 和 kontaktperson

我有以下查询，子查询有一个 selger 别名，我想对其进行模式匹配 -

select 
    (select ordredato from ordrer where kundenr = campaign_company.kunder_id  order by ordredato desc limit 1) 
        as last_contract, 
    (select 
        (select fornavn || ' ' || etternavn from kontaktperson where ordrer.kpkode=kontaktperson.kpkode) 
        from ordrer 
        where kundenr = campaign_company.kunder_id 
     order by ordredato desc limit 1) 
        as selger  
from ordrer 
     left join campaign_company on campaign_company.sale = ordrenr 
// i tried with having to 
where 
    selger like '%string%'

但是报错selger column not found

Answer 1

您需要包装您的查询:

SELECT * FROM (<your select>) AS a WHERE selger like '%string%'

或者您可以修改查询:

SELECT * FROM ( SELECT DISTINCT
    FIRST(o2.ordredato) OVER (ORDER BY o2.ordredato) AS last_contract,
    FIRST(k.fornavn || ' ' || k.etternavn) OVER (ORDER BY o2.ordredato)as selger
FROM 
    order AS o
    LEFT JOIN campaign_company ASD cc ON (cc.sale = o.ordrenr) 
    LEFT JOIN order AS o2 ON (o2.kundenr = cc.kunder_id)
    LEFT JOIN kontaktperson AS k ON (o2.kpkode = k.kpkode)
) AS a
WHERE selger like '%string%'