SQL中case when应用及Decimal类型数据的运算
来源:互联网 发布:李涛疯狂淘宝是真的吗 编辑:程序博客网 时间:2024/05/12 21:07
-----------------------------------
理财计算
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
- SQL中case when应用及Decimal类型数据的运算
- Oracle-Sql 语句中 Case When 的应用
- sql 中case when
- SQL中case when
- SQL Server和Oracle中case when then 的用法及when like 的使用
- sql中 case when 的用法
- sql case when 中遇到的情况
- SQL语句中case when的使用
- sql 的update中使用case when
- SQL中CASE WHEN THEN的用法
- SQL语句中case when 的使用方法
- sql语句中case when的使用方法
- Sql的case when
- CASE WHEN及基本sql
- SQL Server中 Case When 函数 与 Access中 IIF 函数的应用
- sybase中应用case when
- SQL中when/case实例
- sql 中 case when 语法
- 汇编指令英文全称2
- linux shell 字符串操作详解(获取长度、查找,替换)
- maven package 和 install 区别
- 系统相机调用的那些问题...
- 238. Product of Array Except Self
- SQL中case when应用及Decimal类型数据的运算
- null+string会是什么结果?
- C# 将bit位数据拼装为byte
- 起底三大运营商的前身今生,与后世..
- 【GDOI2016】疯狂动物城 题解
- PERL各个符号代表的意思
- 华为笔试题
- 如何用pdb进行python调试
- xuexi