select 
        yearmonth,
        type,
        name,
        last_buy_month  as 上次购进时间,
        sum(A去年购进总金额) as A去年购进总金额,
        sum(B去年购进总金额) as B去年购进总金额,
        sum(C去年购进总金额) as C去年购进总金额,
        sum(D去年购进总金额) as D去年购进总金额,
        sum(A上次购进总金额) as A上次购进总金额,
        sum(B上次购进总金额) as B上次购进总金额,
        sum(C上次购进总金额) as C上次购进总金额,
        sum(D上次购进总金额) as D上次购进总金额
        from (
        select '202206' as yearmonth,
        c.name,
        sum(case when left(yearmonth,4) = left(to_char(to_date('202206','yyyymm') - interval '12 month','yyyymm'),4) and 
        b.category_name = 'OTC-A'
        then a.amount else 0 end ) as A去年购进总金额,
        sum(case when left(yearmonth,4) = left(to_char(to_date('202206','yyyymm') - interval '12 month','yyyymm'),4) and 
        b.category_name = 'OTC-B'
        then a.amount else 0 end ) as B去年购进总金额,
        sum(case when left(yearmonth,4) = left(to_char(to_date('202206','yyyymm') - interval '12 month','yyyymm'),4) and 
        b.category_name = 'OTC-C'
        then a.amount else 0 end ) as C去年购进总金额,
        sum(case when left(yearmonth,4) = left(to_char(to_date('202206','yyyymm') - interval '12 month','yyyymm'),4) and 
        b.category_name = 'OTC-D'
        then a.amount else 0 end ) as D去年购进总金额,
        d.last_buy_month,
        sum(case when yearmonth = d.last_buy_month and b.category_name = 'OTC-A' then a.amount else 0 end ) as A上次购进总金额,
        sum(case when yearmonth = d.last_buy_month and b.category_name = 'OTC-B' then a.amount else 0 end ) as B上次购进总金额,
        sum(case when yearmonth = d.last_buy_month and b.category_name = 'OTC-C' then a.amount else 0 end ) as C上次购进总金额,
        sum(case when yearmonth = d.last_buy_month and b.category_name = 'OTC-D' then a.amount else 0 end ) as D上次购进总金额,
        case when c.level1 in ('3001','3002','6030') then '连锁'
        when a.label @> '{14}' then '总经理关注'
        when a.label @> '{16}' then '大区样板'
        when a.label @> '{15}' then '大区重点'
        when c.level1 in ('3003') then '单点'
        when c.level1 in ('4001','4002','4003','2032','2033','2086','2087') then '诊所'
        when c.level1 in ('1004') then '非协议'
        end as type,a.newdepartmentid
        from flowmonth a 
        join goods b on a.goodscode = b.code
        join client c on a.clientid = c.id
        left join (
            select 
            clientid,
            max(case when yearmonth < to_char(to_date('202206','yyyymm') - interval '2 month','yyyymm') and amount > 0 then yearmonth end ) as last_buy_month
            from flowmonth a
            where sfcx = '1'
            and exists (select clientid from flowmonth where yearmonth = '202206' and sfcx = '1' and clientid = a.clientid and (amount <= 0 or amount is null))
            and exists (select clientid from flowmonth where yearmonth = to_char(to_date('202206','yyyymm') - interval '1 month','yyyymm') and sfcx = '1' and clientid = a.clientid and (amount <= 0 or amount is null))
            and exists (select clientid from flowmonth where yearmonth = to_char(to_date('202206','yyyymm') - interval '2 month','yyyymm') and sfcx = '1' and clientid = a.clientid and (amount <= 0 or amount is null))
            GROUP BY clientid
        ) d on a.clientid = d.clientid
        where sfcx = '1'
        and b.category_name like 'OTC-%'
        and exists (select clientid from flowmonth where yearmonth = '202206' and sfcx = '1' and clientid = a.clientid and (amount <= 0 or amount is null))
        and exists (select clientid from flowmonth where yearmonth = to_char(to_date('202206','yyyymm') - interval '1 month','yyyymm') and sfcx = '1' and clientid = a.clientid and (amount <= 0 or amount is null))
        and exists (select clientid from flowmonth where yearmonth = to_char(to_date('202206','yyyymm') - interval '2 month','yyyymm') and sfcx = '1' and clientid = a.clientid and (amount <= 0 or amount is null))
        and ( c.level1 in ('3001','3002','6030','3003','3003','4001','4002','4003','2032','2033','2086','2087','1004')
        or a.label @> '{14}' or a.label @> '{15}' or a.label @> '{16}')
        GROUP BY c.name,d.last_buy_month,c.level1,a.label,a.newdepartmentid
        ) a 
        join view_person_department_otc d on a.newdepartmentid = d.departmentid
        GROUP BY yearmonth,name,last_buy_month,type;