select p.productNumber as "产品编码",
p.productName as "产品名称",
nvl(d.shippingNumber, 0) - nvl(e.convertReturnSaleNum, 0) ||
(select l.labelName
from selectItem si
left join label l on si.selectItemName = l.id
where si.id = p.sellUnit) as "销售数量",
psub.conversionFactor as "规格",
nvl(d.convertShippingNumber, 0) - nvl(e.convertReturnSaleNum, 0) ||
(select l.labelName
from selectItem si
left join label l on si.selectItemName = l.id
where si.id = p.productUnit) as "重量小计",
nvl(c.subTotal, 0) as "金额小计",
nvl(a.receiveNumber, 0) - nvl(b.returnPurchaseNum, 0) -
nvl(d.shippingNumber, 0) - nvl(e.returnSaleNum, 0) ||
(select l.labelName
from selectItem si
left join label l on si.selectItemName = l.id
where si.id = p.sellUnit) as "即时库存"
from (select psp.productName productName,
sum(psp.receiveNumber) receiveNumber,
sum(psp.receiveNumber * psp.conversionFactor) convertReceiveNumber
from purchaseStorage ps
left join psProduct psp on ps.id = psp.psid
where ps.vendor = '402880e53b36fc35013b36fffe4c005c'
group by psp.productName) a
full join (select prp.productName productName,
sum(prp.returnNumber) returnPurchaseNum,
sum(prp.returnNumber * prp.conversionFactor) convertReturnPurchaseNum
from purchaseReturn pr
left join prProduct prp on pr.id = prp.prid
where pr.vendor = '402880e53b36fc35013b36fffe4c005c'
group by prp.productName) b on a.productName = b.productName
full join (select sop.productName productName, sum(sop.subTotal) subTotal
from saleOrder so
inner join soProduct sop on so.id = sop.soid
where sop.vendor = '402880e53b36fc35013b36fffe4c005c'
group by sop.productName) c on a.productName = c.productName
full join (select slp.productName productName,
sum(slp.shippingNumber) shippingNumber,
sum(slp.shippingNumber * slp.conversionFactor) convertShippingNumber
from saleLibrary sl
left join slProduct slp on slp.slid = sl.id
where sl.status <> '402880ff3b0c4ee5013b0c7658010008'
and slp.vendor = '402880e53b36fc35013b36fffe4c005c'
group by slp.productName) d on a.productName = d.productName
full join (select srp.productName productName,
sum(srp.returnNumber) returnSaleNum,
sum(srp.returnNumber * srp.conversionFactor) convertReturnSaleNum
from srProduct srp
where srp.vendor = '402880e53b36fc35013b36fffe4c005c'
group by srp.productName) e on a.productName = e.productName
left join product p on p.id = a.productName
left join productSub psub on psub.productsubid = p.id
p.productName as "产品名称",
nvl(d.shippingNumber, 0) - nvl(e.convertReturnSaleNum, 0) ||
(select l.labelName
from selectItem si
left join label l on si.selectItemName = l.id
where si.id = p.sellUnit) as "销售数量",
psub.conversionFactor as "规格",
nvl(d.convertShippingNumber, 0) - nvl(e.convertReturnSaleNum, 0) ||
(select l.labelName
from selectItem si
left join label l on si.selectItemName = l.id
where si.id = p.productUnit) as "重量小计",
nvl(c.subTotal, 0) as "金额小计",
nvl(a.receiveNumber, 0) - nvl(b.returnPurchaseNum, 0) -
nvl(d.shippingNumber, 0) - nvl(e.returnSaleNum, 0) ||
(select l.labelName
from selectItem si
left join label l on si.selectItemName = l.id
where si.id = p.sellUnit) as "即时库存"
from (select psp.productName productName,
sum(psp.receiveNumber) receiveNumber,
sum(psp.receiveNumber * psp.conversionFactor) convertReceiveNumber
from purchaseStorage ps
left join psProduct psp on ps.id = psp.psid
where ps.vendor = '402880e53b36fc35013b36fffe4c005c'
group by psp.productName) a
full join (select prp.productName productName,
sum(prp.returnNumber) returnPurchaseNum,
sum(prp.returnNumber * prp.conversionFactor) convertReturnPurchaseNum
from purchaseReturn pr
left join prProduct prp on pr.id = prp.prid
where pr.vendor = '402880e53b36fc35013b36fffe4c005c'
group by prp.productName) b on a.productName = b.productName
full join (select sop.productName productName, sum(sop.subTotal) subTotal
from saleOrder so
inner join soProduct sop on so.id = sop.soid
where sop.vendor = '402880e53b36fc35013b36fffe4c005c'
group by sop.productName) c on a.productName = c.productName
full join (select slp.productName productName,
sum(slp.shippingNumber) shippingNumber,
sum(slp.shippingNumber * slp.conversionFactor) convertShippingNumber
from saleLibrary sl
left join slProduct slp on slp.slid = sl.id
where sl.status <> '402880ff3b0c4ee5013b0c7658010008'
and slp.vendor = '402880e53b36fc35013b36fffe4c005c'
group by slp.productName) d on a.productName = d.productName
full join (select srp.productName productName,
sum(srp.returnNumber) returnSaleNum,
sum(srp.returnNumber * srp.conversionFactor) convertReturnSaleNum
from srProduct srp
where srp.vendor = '402880e53b36fc35013b36fffe4c005c'
group by srp.productName) e on a.productName = e.productName
left join product p on p.id = a.productName
left join productSub psub on psub.productsubid = p.id
这样也可以
select
replance('10KG','KG','')
from dual;
select
replace('10KG','KG','')
from dual;