取多张进货单的最近一次时间的进货价格,用如下语句可以查询整个库存,但速度慢:
select * from BMS_SU_DTLQRY_V A
where not exists
(select 1 from BMS_SU_DTLQRY_V where goodsname=A.goodsname and credate>A.credate)在实现单品查询最近进货价的时候插入语句却出错!还需要根据品名模糊查询!请指教下SQL语句?
select * from BMS_SU_DTLQRY_V A
where not exists
(select 1 from BMS_SU_DTLQRY_V where goodsname=A.goodsname and credate>A.credate)在实现单品查询最近进货价的时候插入语句却出错!还需要根据品名模糊查询!请指教下SQL语句?
按如下方法试试[code=SQL]
create table t
(id number,
a date,
b number);insert into t
(select 1,to_date('2007-01-01','yyyy-mm-dd'),20.00 from dual)
union all
(select 1,to_date('2008-01-01','yyyy-mm-dd'),80.00 from dual)
union all
(select 1,to_date('2009-01-01','yyyy-mm-dd'),90.00 from dual)
union all
(select 2,to_date('2007-02-01','yyyy-mm-dd'),20.00 from dual)
union all
(select 2,to_date('2008-02-01','yyyy-mm-dd'),80.00 from dual)
union all
(select 2,to_date('2009-02-01','yyyy-mm-dd'),90.00 from dual);
select id,a,b
from
(select id,a,b,
row_number()over(partition by id order by id,b desc)r
from t
)
where r=1;
[/code]