还有批号 select * from #入库情况 a where 入库时间 = (select max(入库时间) where 商品代码 = a.商品代码 and 批号 = a.批号)
create table #入库情况(入库时间 char(10),商品代码 char(6),批号 char(2),入库数量 decimal,库存数量 decimal,单据号 char (18)) insert #入库情况 select '20050420','159720','1','59','7','RK2005040000471' union all select '20050603','159720','1','57','7','RK2005060000054' union all select '20060120','280581','2','80','12','RK2006010000426' union all select '20060223','280581','2','56','12','RK2006020000668'select * from #入库情况 a where not exists(select 1 from #入库情况 where 商品代码=a.商品代码 and 批号=a.批号 and 入库时间<a.入库时间)
select * from #入库情况 a where 单据号 in ( select top 1 单据号 from #入库情况 b where a.商品代码=b.商品代码 and a.批号=b.批号 order by convert(datetime,left(入库时间,4)+'-'+SUBSTRING (入库时间,5,2)+'-'+right(rtrim(入库时间),2)) desc)
select *
from #入库情况 a
where 入库时间 = (select max(入库时间) where 商品代码 = a.商品代码 and 批号 = a.批号)
insert #入库情况
select '20050420','159720','1','59','7','RK2005040000471' union all
select '20050603','159720','1','57','7','RK2005060000054' union all
select '20060120','280581','2','80','12','RK2006010000426' union all
select '20060223','280581','2','56','12','RK2006020000668'select * from #入库情况 a where not exists(select 1 from #入库情况 where 商品代码=a.商品代码 and 批号=a.批号 and 入库时间<a.入库时间)
where 单据号 in (
select top 1 单据号
from #入库情况 b where a.商品代码=b.商品代码 and a.批号=b.批号 order by convert(datetime,left(入库时间,4)+'-'+SUBSTRING (入库时间,5,2)+'-'+right(rtrim(入库时间),2)) desc)