Question

帮个忙，Oracle问题

有一个表1，字段：lev,loginid
表2， 字段：userid,username,coins（消费金额）,coinlev（消费等级）
表1的loginid和表2的username 相同

计算lev为1的时候，coinlev 1-10级 的消费人数
lev为2的时候，coinlev 1-10级 的消费人数

我试了下，无法让数据唯一性
有图片

以下是我的程序：
select lev,coinlev, coins,counts from(
with a as
(select login_id,lev from tmpdata.sdo_level),
b as
(select UDUSERID,UDUSERNAME,coins,max(COINLEV) coinlev from tmpdata.SDO_COIN
group by UDUSERID,UDUSERNAME,coins
)
select lev,b.coinlev,coins,count(b.UDUSERID) counts from a,b
where a.login_id=b.UDUSERNAME
group by lev,b.coinlev,coins
order by lev,coinlev
)

Answer 1

不明白为什么要用with as

select count(userid) from b where username in (
select loginid from a where lev=1) and coinlev between 1 and 10
union
select count(userid) from b where username in (
select loginid from a where lev=2) and coinlev between 1 and 10