我来说下问题:
ck
(
ckbm varchar2(100) primary key,
cksl number--出库数量
)
hk
(
hkbm varchar2(100) primary key,--主键
ckbm varchar2(100) references ck(ckbm),--引用ck的ckbm
hksl number--回库数量
)
我来说明下:ck是出库表地,hk是回库表,ck与hk是一对多的业务背景是:出去吧,用不完了,就要加到回库表,并且能加多次。问题: 查找hkbm为'hk01'的hksl和剩余的可以回库的数量比方说cksl 为100 ,第一次回库10,第二次回库20,第三次回库30,我要查10的,结果就是:
------------
hkbm ckbm hksl ss
hk01 ck01 10 80
------------
ss:100-20-30
我就是不会求ss了
求教大虾们!
ck
(
ckbm varchar2(100) primary key,
cksl number--出库数量
)
hk
(
hkbm varchar2(100) primary key,--主键
ckbm varchar2(100) references ck(ckbm),--引用ck的ckbm
hksl number--回库数量
)
我来说明下:ck是出库表地,hk是回库表,ck与hk是一对多的业务背景是:出去吧,用不完了,就要加到回库表,并且能加多次。问题: 查找hkbm为'hk01'的hksl和剩余的可以回库的数量比方说cksl 为100 ,第一次回库10,第二次回库20,第三次回库30,我要查10的,结果就是:
------------
hkbm ckbm hksl ss
hk01 ck01 10 80
------------
ss:100-20-30
我就是不会求ss了
求教大虾们!
(
ckbm varchar2(100) primary key,
cksl number--出库数量
);create table hk
(
hkbm varchar2(100) primary key,--主键
ckbm varchar2(100) references ck(ckbm),--引用ck的ckbm
hksl number--回库数量
);
--ck
insert into ck values('ck01',100);
insert into ck values('ck02',200);
insert into ck values('ck03',300);
commit;
--hk
insert into hk values('hk01','ck01',10);
insert into hk values('hk02','ck01',20);
insert into hk values('hk03','ck01',30);
insert into hk values('hk04','ck02',10);
insert into hk values('hk05','ck02',20);
insert into hk values('hk06','ck03',30);
commit;select
max(temp.ckbm) as ckbm,
max(temp.hkbm) as hkbm,
max(temp.cksl) as cksl,
max(temp.hksl) as hksl,
sum(temp2.hksl) as ss from
(select ck.ckbm,hk.hkbm,ck.cksl,hk.hksl from ck left join hk on ck.ckbm=hk.ckbm where hk.hkbm='hk01' ) temp
inner join hk temp2 on temp.ckbm =temp2.ckbm
where temp2.hkbm<>'hk01'
ck01 hk01 100 10 50
with ck as
(select 'ck01' ckbm, 100 cksl
from dual
union all
select 'hk02' ckbm, 100 cksl from dual),
hk as
(select 'hk01' hkbm, 'ck01' ckbm, 10 hksl
from dual
union all
select 'hk01' hkbm, 'ck01' ckbm, 20 hksl
from dual
union all
select 'hk01' hkbm, 'ck01' ckbm, 30 hksl
from dual
)select h.hkbm, h.ckbm, h.hksl,(c.cksl - h2.sumHksl + h.hksl) ss
from hk h, ck c,(
select sum(h1.hksl) sumHksl
from hk h1 group by h1.ckbm) h2
where h.hkbm = 'hk01' and h.ckbm = c.ckbm;不知道是不是楼主的意思
SELECT HK.HKBM,CKBM,HKSL,NVL(V.SS,0) AS SS
FROM HK,
(SELECT SUM(HKSL) AS SS,CKBM FROM HK
WHERE HKSL<>10 GROUP BY CKBM)V
WHERE HK.CKBM=V.CKBM(+)