select isnull(a.卡号,b.卡号),a.消费总额,b.充值总额
from (select 卡号,sum(消费额) as 消费总额 from 消费表 group by 卡号) a
full join
(select 卡号,sum(充值额) as 充值总额 from 充值表 group by 卡号) b
on a.卡号=b.卡号
from (select 卡号,sum(消费额) as 消费总额 from 消费表 group by 卡号) a
full join
(select 卡号,sum(充值额) as 充值总额 from 充值表 group by 卡号) b
on a.卡号=b.卡号
from(
select 卡号,消费额,充值额=0 from 消费表
union all
select 卡号,消费额=0,充值额 from 充值表
)a group by 卡号
a.消费总额,b.充值总额 from
(select 卡号,sum(消费额) as 消费总额 from 消费表 group by 卡号) a
full join
(select 卡号,sum(充值额) as 充值总额 from 充值表 group by 卡号)b
on a.卡号=b.卡号
(select 卡号,充值总额=sum(充值额) from 充值表 group by 卡号) a
left join
(select 卡号,充值总额=sum(消费额) from 充值表 group by 卡号) b
on a.卡号=b.卡号
from (select 卡号,sum(消费额) as 消费总额 from 消费表 group by 卡号) a
join
(select 卡号,sum(充值额) as 充值总额 from 充值表 group by 卡号) b
on a.卡号=b.卡号 order by a.卡号