金額分攤sql

pwz1688發表於2016-07-26
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);

  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);

  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;

  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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章