SQL 分组排序取最大
row_number() over(partition by order by )
select c.prodid ,c.KcQty 当前库存,a.最后入库时间,a.最后一次入库数量,b.最后出库时间,b.最后一次出库数量
from RY_Storagecard c left join
(select b.prodid ,c.Qty 最后一次入库数量 ,c.note 入库,c.RecordDate 最后入库时间
from (select ProdID,id ,row_number() over(partition by prodid order by recorddate desc ) a
from Ware_Batch_Bill a
where prodid in (select prodid from RY_Storagecard where kcqty >0) and wareid =‘004’
and BillType_ID =‘1’ and note =‘单据转入-系统’ ) b join Ware_Batch_Bill c on b.id =c.ID
where b.a=1) a on c.prodid =a.ProdID
left join
(select b.prodid ,c.Qty 最后一次出库数量 ,c.note 出库,c.RecordDate 最后出库时间
from (select ProdID,id ,row_number() over(partition by prodid order by recorddate desc ) a
from Ware_Batch_Bill a
where prodid in (select prodid from RY_Storagecard where kcqty >0) and wareid =‘004’
and BillType_ID =‘3’ and note =‘出库-系统’ ) b join Ware_Batch_Bill c on b.id =c.ID
where b.a=1) b on c.prodid =b.ProdID
where kcqty >0