select a.* from tbname a,
select sp_code,max(col_date) col_date from tbname t group by t.sp_code) b
where a.sp_code=b.sp_code and a.col_date=b.col_date;
select sp_code,max(col_date) col_date from tbname t group by t.sp_code) b
where a.sp_code=b.sp_code and a.col_date=b.col_date;
(select sp_code,max(col_date) col_date from tbname t group by t.sp_code) b
where a.sp_code=b.sp_code and a.col_date=b.col_date;
from tableA A
where A.记录生成时间 = (select max(B.记录生成时间)
from tableA B
where A.商品编号=B.商品编号
)
注意都是tableA,但别名不一样。
列名:ProdId(产品编号),
Price(单价),
Num(数量),
Style(单据类型),--“0”为进货,“1”为出货
OpDate(操作日期),……主键:ProdId
其他索引:Style------------------------------------------
计算各个产品进出累计:
select ProdId,Style,Nvl(Sum(Num),0) All_Num
from MyCard
group by ProdId,Style
order by ProdId,Style;------------------------------------------
计算当前库存:
select ProdId,Sum(All_Num) NowStore
from (
--计算进货
(Select ProdId,Nvl(Sum(Num),0) All_Num
from MyCard
where Style='0')
union all
--计算出货
(Select ProdId,-Nvl(Sum(Num),0) All_Num
from MyCard
where Style='1')
)
select ProdId,Sum(All_Num) NowStore
from (
--计算进货
(Select ProdId,Nvl(Sum(Num),0) All_Num
from MyCard
where Style='0'
group by PridId)
union all
--计算出货
(Select ProdId,-Nvl(Sum(Num),0) All_Num
from MyCard
where Style='1'
group by PridId)
)
group by PridId