SQL：根据消费类型不同统计人次

SQL：根据消费类型不同统计人次

--按照不同消费类型,统计不同类型的总金额和玩家(重复消费算1人次)
select distinct reason,sum(c) as '金额', count(unick) as '人数' from (
select unick,reason,sum(cost) as c from rp_usercost group by unick,reason ) t
group by reason
order by reason


--按照不同消费类型,统计不同类型的金额和玩家(玩家相同消费项目,只计算第一条,累计金额也只有第一条)
select distinct reason,sum(c) as cost, count(unick) as amount from (
select distinct reason,unick,ROW_NUMBER() over (partition by reason,unick  order by unick) as num,sum(cost) as c from rp_usercost group by reason,unick
) t where t.num=1
group by reason 
order by reason