望高手指点复杂的求和分摊的 SQL,将总金额分摊到组内各行
有一个怪问题,用户要的报表真是麻烦,不知道如何用一条 SQL 写出来。
例如,LN001 和 BL003 各有两个抵押品,LN001 和 BL002 共用了同一个抵押品:
#, 用户,融资项目,融资金额, 抵押品,抵押品市值,征用金额, 是否超出额度
1: A , LN001, 2500, CO-1 , 1000 , 1000 , N
2: A , LN001, 2500, CO-2 , 2000 , 1500 , N
3: A , BL002, 600, CO-2, 2000 , 600 , Y
4: B , BL003, 2000, CO-3, 1200 , 1200 , N
5:B ,BL003, 2000, CO-4, 500 , 500 , Y
6: C , BL004, 2000, CO-5, 3000 , 2000 , N
7: C , BL005, 500 , CO-5, 3000 , 500 , N
因为这里面有一个抵押被多个融资项目征用,比如贷款(LN开头的)和贸易融资(BL开头),而一个融资项目也可能会用到多个抵押品。现在用户希望像上面那样去把这个抵押品市值分摊下来,比如上面的例子中:
1. 某个融资项目的金额少于用到的抵押品的市值,那么是否超出额度会标记为 Y。但是像上面第4行,用完 CO-3 的金额时这个抵押品不会被标记为超出额度,但后面一个 CO-4 只有 500 ,不够 BL003 剩下的 2000-1200=800,所以 #5 会被标记为超出额度。
2. #4 和 #5 中的抵押品使用的次序无所谓,用编号排序也行,只要能分派金额就可以。
3. 融资类型可以按 LN001 这样的融资项目的编号排列,依次分派。
4. 除了"征用金额和是否超出额度"两个列之外的其它列都是已知的,我们就假设有这样的表。
我的问题是有没有办法用一条 SQL 写出来计算出 "征用金额" 列的值,就像上面列的一样。
我现在只找到像这样最后贴的两种写法,但都有问题,不知道怎么改它:
比如,第一个 select 写法里面当一个融资只使用一个抵押品的时候是正确的,比如 #6 和 #7 的就正常。
当有两个抵押品同时被两个融资项目使用时,全是错的,高手有没有办法用一条 SQL 写出来?
(要用在 Crystal Report 里面,不知道怎么变通处理)。
set schema DEVV1;
create table "抵押使用表" (
"序号" integer,
"用户" varchar(20),
"融资项目" varchar(20),
"融资金额" decimal(25,5),
"抵押品" varchar(20),
"抵押品市值" decimal(25,5)
);
insert into "抵押使用表" values
(1, 'A', 'LN001', 2500, 'CO-1', 1000),
(2, 'A', 'LN001', 2500, 'CO-2', 2000),
(3, 'A', 'BL002', 600, 'CO-2', 2000),
(4, 'B', 'BL003', 2000, 'CO-3', 1200),
(5, 'B', 'BL003', 2000, 'CO-4', 500),
(6, 'C', 'BL004', 2000, 'CO-5', 3000),
(7, 'C', 'BL005', 500, 'CO-5', 3000);
commit;
select * from 抵押使用表;
select 用户,融资项目,融资金额,抵押品,抵押品市值
, 抵押品使用额
, (case when 抵押品使用额 > 抵押品市值 then 'Y' else 'N' end) as 是否超出额度
from (
select 用户,融资项目,融资金额,抵押品,抵押品市值, sum(融资金额) over (partition by 抵押品 order by 融资项目) 抵押品使用额
from 抵押使用表
) x
order by 抵押品
;
select 用户,融资项目,融资金额,抵押品,抵押品市值
, 抵押品使用额
, (case when 融资金额 > 抵押品市值 then 抵押品市值 else 融资金额 end) 征用金额
, (case when 抵押品使用额 > 抵押品市值 then 'Y' else 'N' end) as 是否超出额度
from (
select 用户,融资项目,融资金额,抵押品,抵押品市值
, sum(融资金额) over (partition by 抵押品,融资项目 order by 融资项目) 抵押品使用额
from 抵押使用表
) x
order by 融资项目,抵押品
;