求教一个sql语句

[白衣的逆袭]

表二num累加直到数量够表一的total,然后用累加的这个num*price，得到的值放入表一中，求教这个sql应该怎么写

[浮云若水]

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 这样不知道符不符合你的要求

[浮云若水]

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

[卖水果的net]

表一，就一行数据吗？

[白衣的逆袭]

引用 1 楼 wmxcn2000 的回复:

表一，就一行数据吗？

表一表二都是多行，只是举的一个例子。已经用代码读出来，分开取数据计算了，只是看能不能直接用sql搞定

[mayanzs]

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;

[ghx287524027]

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表中名字不重复

[chengccy]

借用楼上数据的分析函数写法

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