SQL中case when应用及Decimal类型数据的运算

-----------------------------------

理财计算

insert into lsyw.finance

select cust_num, cust_cn_nm, acct_belg_org, total, l1, 100*(cast(l1 as double)/total) as l1ra, m1l3, 100*(cast(m1l3 as double)/total) as m1l3ra,
m3l6, 100*(cast(m3l6 as double)/total) as m3l6ra, m6l12, 100*(cast(m6l12 as double)/total) as m6l12ra,
m12, 100*(cast(m12 as double)/total) as m12ra from
(
select a.cust_num, c.Cust_Cn_Nm, a.acct_belg_org, count(*) as total,
sum(case when (days(b.due_dt) - days(b.st_int_dt))/30 <= 1 then 1 else 0 end) as l1,
sum(case when (days(b.due_dt) - days(b.st_int_dt))/30 >1 and (days(b.due_dt) - days(b.st_int_dt))/30 <= 3 then 1 else 0 end) as m1l3,
sum(case when (days(b.due_dt) - days(b.st_int_dt))/30 >3 and (days(b.due_dt) - days(b.st_int_dt))/30 <= 6 then 1 else 0 end) as m3l6,
sum(case when (days(b.due_dt) - days(b.st_int_dt))/30 >6 and (days(b.due_dt) - days(b.st_int_dt))/30 <= 12 then 1 else 0 end) as m6l12,
sum(case when (days(b.due_dt) - days(b.st_int_dt))/30 >12 then 1 else 0 end) as m12
from edw.bhif_chrem_fund_sell_dtl a
left join edw.bhif_chrem_fund_prod b
on a.chrem_encd = b.prod_id
left join edw.bhif_indv_cust_basic_info c
on a.cust_num = c.cust_id
where year(b.st_int_dt) != 0001 and year(b.st_int_dt) != 2099 and year(b.due_dt) != 0001 and year(b.due_dt) != 2099 and year(b.due_dt) != 8999
group by cust_num, cust_cn_nm, acct_belg_org

)

-------------------------------------

工资计算

select 
stdgjjennm as 企业名称, count(*) as 企业人数, sum(stdgjjdwam + stdgjjgram)/count(*) as 平均缴存金额, 
sum(stdgjjdwsc)/count(*) as 单位平均缴存比例,sum(stdgjjgrsc)/count(*) as 个人平均缴存比例, 
sum(stdgjjdwam/stdgjjdwsc)/count(*) as 平均工资,
sum(case when stdgjjdwam/stdgjjdwsc >= 0 and stdgjjdwam/stdgjjdwsc < 2000 then 1 else 0 end) as 工资0到2000元人数,
sum(case when stdgjjdwam/stdgjjdwsc >= 2000 and stdgjjdwam/stdgjjdwsc < 3000 then 1 else 0 end) as 工资2000到3000元人数,
sum(case when stdgjjdwam/stdgjjdwsc >= 3000 and stdgjjdwam/stdgjjdwsc < 4000 then 1 else 0 end) as 工资3000到4000元人数,
sum(case when stdgjjdwam/stdgjjdwsc >= 4000 and stdgjjdwam/stdgjjdwsc < 6000 then 1 else 0 end) as 工资4000到6000元人数,
sum(case when stdgjjdwam/stdgjjdwsc >= 6000 and stdgjjdwam/stdgjjdwsc < 8000 then 1 else 0 end) as 工资6000到8000元人数,
sum(case when stdgjjdwam/stdgjjdwsc >= 8000 and stdgjjdwam/stdgjjdwsc < 10000 then 1 else 0 end) as 工资8000到10000元人数,
sum(case when stdgjjdwam/stdgjjdwsc >= 10000 and stdgjjdwam/stdgjjdwsc < 15000 then 1 else 0 end) as 工资10000到15000元人数,
sum(case when stdgjjdwam/stdgjjdwsc >= 15000 and stdgjjdwam/stdgjjdwsc < 20000 then 1 else 0 end) as 工资15000到20000元人数,
sum(case when stdgjjdwam/stdgjjdwsc >= 20000 then 1 else 0 end) as 工资大于20000元人数

from gjj.gjjzh 

where stdgjjdwsc > 0

group by stdgjjennm