子查询例子

select gus.user_name, gus.agent_id, gus.card_total, gus.used_card, count(1)

  from (select agent_info.user_name,

       agent_info.agent_id,

       agent_info.card_total,

       nvl(agent_used_card.used_card, 0) used_card

  from (select gu.user_name, guc.agent_id agent_id, guc.card_total

          from (select guc.agent_id, count(1) card_total

                  from gu.GU_CARDS guc

                 group by guc.agent_id) guc,

               gu_users gu

         where guc.agent_id = gu.user_id(+)) agent_info,

       (select guc.agent_id, count(1) used_card

          from gu.GU_CARDS guc

         where guc.changed_time is not null

         group by guc.agent_id) agent_used_card

 where agent_info.agent_id = agent_used_card.agent_id(+)) gus,

       gu_users guu

 where gus.agent_id = guu.promote_id(+)

 group by agent_id, gus.user_name, gus.card_total, gus.used_card

 

要从里往外写