select DATE_FORMAT(CURDATE(),'%Y%m%d') DateId,elt(interval(curnum,0, 10000,20000,30000,40000,50000, 100000,200000,300000, 500000,1000000,2000000,3000000,5000000,10000000,30000000),
'1W以下','1W-2W','2W-3W','3W-4W','4W-5W','5W-10W', '10W-20W', '20W-30W', '30W-50W', '50W-100W','100W-200W','200W-300W','300W-500W','500W-1000W','1000W-3000W','3000W以上') as coinrange
, count(userid) as usernum,sum(curnum) sumcoin,b.addmoney,b.addmoney_all
from MonitorServer.user_coin_9041 a,
(select sum(case when daynum>=concat(DATE_FORMAT(DATE_ADD(CURDATE(),INTERVAL -1 day),'%Y%m'),'01') then money
else 0 end) addmoney,sum(money) addmoney_all from MonitorServer.user_pay_9041
where daynum<DATE_FORMAT(CURDATE(),'%Y%m%d') and type<>4) b
where updated<>0 and coinid=1 and FROM_UNIXTIME(updated)>=ADDDATE(CURDATE(),INTERVAL -31 day)
and a.userid not in(22091258,22178140,22268579,22144984,22212840,22212784,22209741,
22228179,22257539,22260231,21954080,21683531,22142028,22192965,21643645,21576370,21638291,21437986,21681971,21519260,22145626
,20989476,20989473,22268085,22151310,21703679,22239574,22388084,22387973,22388047,22424079,22425919)
group by elt(interval(curnum,0, 10000,20000,30000,40000,50000, 100000,200000,300000, 500000,1000000,2000000,3000000,5000000,10000000,30000000),
'1W以下','1W-2W','2W-3W','3W-4W','4W-5W','5W-10W', '10W-20W', '20W-30W', '30W-50W', '50W-100W','100W-200W','200W-300W','300W-500W','500W-1000W','1000W-3000W','3000W以上')
order by curnum;
