金额分摊sql

先设定俩表
--表T1为钢水总金额表
create table T1
(
  LH           VARCHAR2(4) not null,  --钢水炉号
  P             NUMBER(10,2) not null,  --钢水总金额
  GSL         NUMBER(8,2)              --总钢水量
);
alter table T1
  add constraint T1 primary key (LH));
数据如下：
LH                P                      GSL
0001        100000.00           100.11
0002        50000.00           80.99
--表T2为浇铸后信息表
create table T2
(
  LH        VARCHAR2(4) not null,  --钢水炉号
  P          NUMBER(10,2),  --钢水金额
  GSL       NUMBER(8,2)              --钢水量
);
数据如下：
LH          GSL              P
0001        50.00        
0001        40.00        
0001        10.11        
0002        30.00        
0002        25.00        
0002        25.99  
根据表T1的数据来计算表T2的P字段，计算公式为：每一行的T2.P = T1.P * T2.GSL /T1.GSL 但是要注意这样计算后的结果相加与表T1的金额数会有差值的问题。

解决方案，代码如下：

点击(此处)折叠或打开

1. create table T1
   
2. (
   
3.   LH VARCHAR2(4) not null, --钢水炉号
   
4.   P NUMBER(10,2) not null, --钢水总金额
   
5.   GSL NUMBER(8,2) --总钢水量
   
6. );
   
7. INSERT INTO t1 VALUES('0001',100000.00,100.11);
   
8. INSERT INTO t1 VALUES('0002',50000.00,80.99);
   
9. 
   
10. create table T2
    
11. (
    
12.   LH VARCHAR2(4) not null, --钢水炉号
    
13.   P NUMBER(10,2), --钢水金额
    
14.   GSL NUMBER(8,2) --钢水量
    
15. );
16. INSERT INTO t2(lh,gsl) VALUES('0001',50.00);
    
17. INSERT INTO t2(lh,gsl) VALUES('0001',40.00);
    
18. INSERT INTO t2(lh,gsl) VALUES('0001',10.11);
    
19. INSERT INTO t2(lh,gsl) VALUES('0002',30.00);
    
20. INSERT INTO t2(lh,gsl) VALUES('0002',25.00);
    
21. INSERT INTO t2(lh,gsl) VALUES('0002',25.99);
    
22. 
    
23. SELECT t2.lh
    
24.       ,t2.gsl
    
25.       ,TRUNC(t1.p * t2.gsl / t1.gsl,2) -- 舍弃小数点2位之后
    
26.        +(CASE WHEN ROW_NUMBER() OVER(PARTITION BY t2.lh ORDER BY t2.gsl DESC) --- 按顺序排列，前面的摊到0.01, 后面的就没有了
    
27.                    <=(t1.p - SUM(TRUNC(t1.p * t2.gsl / t1.gsl,2)) OVER(PARTITION BY t2.lh))*100
    
28.               THEN 0.01
    
29.               ELSE 0
    
30.          END) AS t2_p
    
31.   FROM t1, t2
    
32. WHERE t1.lh = t2.lh;
33. 
    
34. --查询结果如下
35.     LH GSL T2_P
    1 0001 50.00 49945.07
    2 0001 40.00 39956.04
    3 0001 10.11 10098.89
    4 0002 30.00 18520.81
    5 0002 25.99 16045.19
    6 0002 25.00 15434
    
    
    
    
    

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21251711/viewspace-2122546/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21251711/viewspace-2122546/