表二num累加直到数量够表一的total,然后用累加的这个num*price,得到的值放入表一中,求教这个sql应该怎么写
解决方案 »
- toad建立db link后,提示数据库链接××与××相链接,怎么回事?
- SSL_ALG_CLIENT_AUTH_MODE_RSA_SIGN_SERVERSIDE_BS
- oracle数据库中的表因事务处理挂起无法查看,如何处理?
- 各位大虾们(男女不限)问你们个处发起的问题?
- 在Oracle中SQL的时间表达CreateTime>=2004-6-1 00:00:01 AND CreateTime<=2004-6-30 23:59:59 错在哪时里?
- rman三个问题
- 关于sequence得currval和nextval
- 为什么表不可见?
- 强烈建议剔除用户 IShellFolder()
- 如何在SQL PLUS中创建非Oracle数据表?
- 求一个存储过程的最优写法
- 求助:oracle对特定字段查询特别慢
表一表二都是多行,只是举的一个例子。已经用代码读出来,分开取数据计算了,只是看能不能直接用sql搞定
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;
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表中名字不重复
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