求一个拆分的sql写法

u010795648 2014-07-03 03:25:23

现有两张表的数据和结构如图：
test：

test1：

现在想把test1中的prem1的值存到test表的prem1中，按照companycode1的prem占比来计算prem1应该存多少的值，而且因为占比有的时候会出现1/3这种，就要排序，前几项按占比，最后一个按总的prem1减掉已经分出去的，这样prem1最后的加和才会和test1表的中prem1一直。这个sql怎么写小弟刚学oracle 请大神指教。
test和test1表关联条件为test.companycode=test1.companycode;

想要如下结果：

只能给100分问题解决还会加分谢谢

...全文

115 8 打赏收藏转发到动态举报

写回复

用AI写文章

8 条回复

切换为时间正序

请发表友善的回复…

发表回复

u010795648 2014-07-03

打赏
举报

引用 7 楼 wildwave 的回复:

update test t1
   set prem1 =
       (select case
                 when not exists
                  (select 1
                         from test tt
                        where tt.companycode = t1.companycode
                          and tt.companycode1 > t1.companycode1) then
                  t2.prem1 - round((t2.prem - t1.prem) / t2.prem * t2.prem1)
                 else
                  round(t1.prem / t2.prem * t2.prem1)
               end
          from test1 t2
         where t1.companycode = t2.companycode);

谢谢版主，学习了，给了我思路，不然按照以前的写法太蠢了，按着版主的update写法我又写了个查询的sql。


SELECT T1.COMPANYCODE
       ,T1.COMPANYCODE1
       ,T1.PREM
       ,CASE
          WHEN T1.PREM > T2.PREM THEN
           ROUND(T3.PREM1 * T1.PREM / T3.PREM)
          ELSE
           T3.PREM1 - ROUND((T3.PREM - T1.PREM) / T3.PREM * T3.PREM1)
        END AS PREM1
   FROM TEST T1
   LEFT JOIN TEST T2
     ON T1.COMPANYCODE = T2.COMPANYCODE
    AND T1.COMPANYCODE1 = T2.COMPANYCODE1
   LEFT JOIN TEST1 T3
     ON T1.COMPANYCODE = T3.COMPANYCODE;

[/quote]

小灰狼W 2014-07-03

打赏
举报

update test t1
   set prem1 =
       (select case
                 when not exists
                  (select 1
                         from test tt
                        where tt.companycode = t1.companycode
                          and tt.companycode1 > t1.companycode1) then
                  t2.prem1 - round((t2.prem - t1.prem) / t2.prem * t2.prem1)
                 else
                  round(t1.prem / t2.prem * t2.prem1)
               end
          from test1 t2
         where t1.companycode = t2.companycode);

u010795648 2014-07-03

打赏
举报

引用 3 楼 wildwave 的回复:

test表中同一个companycode的prem的和一定等于test1中对应的prem吗

我自己写了个sql 感觉好蠢大神能给优化下么 SELECT B.COMPANYCODE ,A.COMPANYCODE1 ,A.PREM ,FLOOR(B.PREM1 * (A.PREM / B.PREM)) FROM (SELECT A.* ,ROW_NUMBER() OVER(PARTITION BY A.COMPANYCODE ORDER BY A.PREM) AS SN FROM TEST A) A LEFT JOIN TEST1 B ON A.COMPANYCODE = B.COMPANYCODE WHERE A.SN != 1 UNION ALL SELECT B.COMPANYCODE ,A.COMPANYCODE1 ,A.PREM ,B.PREM1 - C.SUMPREM FROM (SELECT A.* ,ROW_NUMBER() OVER(PARTITION BY A.COMPANYCODE ORDER BY A.PREM) AS SN FROM TEST A) A LEFT JOIN TEST1 B ON A.COMPANYCODE = B.COMPANYCODE LEFT JOIN (SELECT B.COMPANYCODE ,A.COMPANYCODE1 ,A.PREM ,SUM(FLOOR(B.PREM1 * (A.PREM / B.PREM))) AS SUMPREM FROM (SELECT A.* ,ROW_NUMBER() OVER(PARTITION BY A.COMPANYCODE ORDER BY A.PREM) AS SN FROM TEST A) A LEFT JOIN TEST1 B ON A.COMPANYCODE = B.COMPANYCODE WHERE A.SN != 1 GROUP BY B.COMPANYCODE ,A.COMPANYCODE1 ,A.PREM) C ON C.COMPANYCODE = A.COMPANYCODE WHERE A.SN = 1

u010795648 2014-07-03

打赏
举报

引用 1 楼 AcHerat 的回复:

这个是 test1 的 perm1 / perm 然后 * test 的perm ？？？

我自己写个sql 感觉好蠢大神能帮着优化下么 SELECT B.COMPANYCODE ,A.COMPANYCODE1 ,A.PREM ,FLOOR(B.PREM1 * (A.PREM / B.PREM)) FROM (SELECT A.* ,ROW_NUMBER() OVER(PARTITION BY A.COMPANYCODE ORDER BY A.PREM) AS SN FROM TEST A) A LEFT JOIN TEST1 B ON A.COMPANYCODE = B.COMPANYCODE WHERE A.SN != 1 UNION ALL SELECT B.COMPANYCODE ,A.COMPANYCODE1 ,A.PREM ,B.PREM1 - C.SUMPREM FROM (SELECT A.* ,ROW_NUMBER() OVER(PARTITION BY A.COMPANYCODE ORDER BY A.PREM) AS SN FROM TEST A) A LEFT JOIN TEST1 B ON A.COMPANYCODE = B.COMPANYCODE LEFT JOIN (SELECT B.COMPANYCODE ,A.COMPANYCODE1 ,A.PREM ,SUM(FLOOR(B.PREM1 * (A.PREM / B.PREM))) AS SUMPREM FROM (SELECT A.* ,ROW_NUMBER() OVER(PARTITION BY A.COMPANYCODE ORDER BY A.PREM) AS SN FROM TEST A) A LEFT JOIN TEST1 B ON A.COMPANYCODE = B.COMPANYCODE WHERE A.SN != 1 GROUP BY B.COMPANYCODE ,A.COMPANYCODE1 ,A.PREM) C ON C.COMPANYCODE = A.COMPANYCODE WHERE A.SN = 1

u010795648 2014-07-03