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
要从里往外写