利用–作程式碼分析

select alias_cardtype,
       id_card_value,
       sum(decode(flag, 'inTotal', total, 0)) inTotal,--tj
       sum(decode(flag, 'outTotal', total, 0)) outTotal,--tj
       sum(decode(flag, 'activeTotal', total, 0)) activeTotal---tj
  from (select alias_cardtype,
               id_card_value,
               count(1) total,
               'inTotal' as flag
          from (select m.card_seq, m.alias_cardtype, m.id_card_value
                  from ecm_card_info_retail m
                  left join ecm_bussnihall_sale a on m.absorbed_id =
                                                     a.absorbed_id
                 where trunc(m.import_date) = trunc(sysdate-6)
                union all
                select n.card_seq, n.alias_cardtype, n.id_card_value
                  from ecm_card_info_wholesale n
                  left join ecm_bussnihall_sale a on n.absorbed_id =
                                                     a.absorbed_id
                 where trunc(n.import_date) = trunc(sysdate-6))
         group by alias_cardtype, id_card_value
        union all--
        select alias_cardtype,
               id_card_value,
               count(1) total,
               'activeTotal' as flag
          from (select m.card_seq, m.alias_cardtype, m.id_card_value--
                  from ecm_card_info_retail m
                  left join ecm_bussnihall_sale a on m.absorbed_id =
                                                     a.absorbed_id
                 where trunc(m.active_date) = trunc(sysdate-6)
                union all
                select n.card_seq, n.alias_cardtype, n.id_card_value
                  from ecm_card_info_wholesale n
                  left join ecm_bussnihall_sale a on n.absorbed_id =
                                                     a.absorbed_id
                 where trunc(n.active_date) = trunc(sysdate-6))
         group by alias_cardtype, id_card_value
        union all--
        select alias_cardtype,
               id_card_value,
               count(1) total,
               'outTotal' as flag
          from (select m.card_seq, m.alias_cardtype, m.id_card_value
                  from ecm_card_info_retail m
                  left join ecm_bussnihall_sale a on m.absorbed_id =
                                                     a.absorbed_id
                 where trunc(a.sale_time) = trunc(sysdate-6)
                union all
                select n.card_seq, n.alias_cardtype, n.id_card_value
                  from ecm_card_info_wholesale n
                  left join ecm_bussnihall_sale a on n.absorbed_id =
                                                     a.absorbed_id
                 where trunc(a.sale_time) = trunc(sysdate-6))
         group by alias_cardtype, id_card_value)
 group by alias_cardtype, id_card_value