我自己写个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
我自己写了个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
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);
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;
test1是机构费用表
现在想把费用prem1拆分到test表中,拆分规则按照test表中细分机构perm(保费)的占比来拆分,但是占比最小的要等其他想拆分完,再用总的减去已经拆分的剩余的归给他
,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
,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
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);
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;