表二num累加直到數量夠表一的total,然后用累加的這個num*price，得到的值放入表一中，求教這個sql應該怎么寫
uj5u.com熱心網友回復：
表一，就一行資料嗎？uj5u.com熱心網友回復：
表一表二都是多行，只是舉的一個例子。已經用代碼讀出來，分開取資料計算了，只是看能不能直接用sql搞定
uj5u.com熱心網友回復：
with ba as (select 'A' name,10000 total,0 money from dual),
bb1 as (select 'A' name,1 xh,1000 nm,13 price from dual union all
select 'A' name,2 xh,2000 nm,12 price from dual union all
select 'A' name,3 xh,1000 nm,12.1 price from dual union all
select 'A' name,4 xh,2000 nm,12.2 price from dual union all
select 'A' name,5 xh,6000 nm,12.3 price from dual union all
select 'A' name,6 xh,7000 nm,12.4 price from dual),
bb as (select bb1.*,(select total from ba where name=bb1.name) total from bb1),
aa as (select * from bb
model
dimension by (name,xh)
measures(nm,price,total,0 sm,0 yl)
rules
(sm[name,xh]=nvl(sm[cv(),cv()-1],0)+nm[cv(),cv()],
yl[name,xh]=case when sm[cv(),cv()]>total[cv(),cv()]+nm[cv(),cv()] then null when sm[cv(),cv()]<total[cv(),cv()] then nm[cv(),cv()] else nm[cv(),cv()]-(sm[cv(),cv()]-total[cv(),cv()]) end))
select ba.name,ba.total,(select sum(aa.yl*aa.price) from aa where aa.name=ba.name) money from ba;
uj5u.com熱心網友回復：
update A a set a.money=(
select c.money from (
select a.name,case
when sum(b.num*b.price)>min(a.total) then sum(b.num*b.price) else min(a.money) end as money from A,B where a.name=b.name
group by a.name)c
where a.name=c.name)
這種寫法，要求A表中名字不重復
uj5u.com熱心網友回復：
借用樓上資料的分析函式寫法with ba as (select 'A' name,10000 total,0 money from dual),
bb1 as (select 'A' name,1 xh,1000 nm,13 price from dual union all
select 'A' name,2 xh,2000 nm,12 price from dual union all
select 'A' name,3 xh,1000 nm,12.1 price from dual union all
select 'A' name,4 xh,2000 nm,12.2 price from dual union all
select 'A' name,5 xh,6000 nm,12.3 price from dual union all
select 'A' name,6 xh,7000 nm,12.4 price from dual)
SELECT NAME, TOTAL, SUM((NM - GREATEST(NM_TOL - TOTAL, 0)) * PRICE) AS MONEY
FROM (SELECT T1.NAME, XH,
SUM(NM) OVER(PARTITION BY T.NAME ORDER BY XH) AS NM_TOL, T1.TOTAL,
NM, PRICE
FROM BB1 T, BA T1
WHERE T.NAME = T1.NAME)
WHERE NM_TOL - TOTAL < NM
GROUP BY NAME, TOTAL
uj5u.com熱心網友回復：
select name ,sum(sum) ,sum(money) as money from ( select name,sum(num) as sum,num*price as money from testB group by name,price,num ) a group by name 這樣不知道符不符合你的要求uj5u.com熱心網友回復：
select name ,sum(sum) ,sum(money) as money from ( select name,sum(num) as sum,num*price as money from testB group by name,price,num ) a group by name
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/73365.html
標籤：開發
上一篇：求一個存盤程序的最優寫法
下一篇：幫忙 看一下這個問題