select 仓库CD,JANCD,sum(a.数量)
from 入出库 a inner join
(select 仓库CD,JANCD,max(确认日) as 最后确认日
from 入出库 where 类别='在库'
group by 仓库CD,JANCD) b
on a.仓库CD=b.仓库CD and a.JANCD=b.JANCD and a.确认日=B.最后确认日
where a.类别='在库'
and 确认日>'00000000'
and 数量<>0
group by a.仓库CD,a.JANCD
from 入出库 a inner join
(select 仓库CD,JANCD,max(确认日) as 最后确认日
from 入出库 where 类别='在库'
group by 仓库CD,JANCD) b
on a.仓库CD=b.仓库CD and a.JANCD=b.JANCD and a.确认日=B.最后确认日
where a.类别='在库'
and 确认日>'00000000'
and 数量<>0
group by a.仓库CD,a.JANCD
where not exists(select 1 from 入出库 b
where a.仓库CD=b.仓库CD and a.JANCD=b.JANCD and a.确认日<b.确认日
) and 类别='在库'
and 确认日>'00000000'
group by a.仓库CD,a.JANCD
where 类别='在库'
and 确认日>'00000000'
and 数量<>0
group by 仓库CD,JANCD这样取出来的数据和你现在写的有什么区别?没在数据库里试,空想的。
(select top 1 * from
(select 仓库CD,JANCD,max(确认日) as 最后确认日 from 入出库 where 类别='在库' group by 仓库CD,JANCD) b where a.仓库CD=b.仓库CD and a.JANCD=b.JANCD and a.确认日=B.最后确认日)
where a.类别='在库' and 确认日>'00000000' and 数量<>0
group by a.仓库CD,a.JANCD感觉差距不大,可能我水平太臭啊,忘其他高手回答
用分析函数代替内连接,效率可能会更快的。select b.仓库CD, b.JANCD, sum(b.数量)
from(
select 仓库CD, JANCD, 确认日,数量,
row_number() over(order by 确认日 desc) flag
from 入出库 a
where 数量 <> 0 and 类别 = '在库'
)b where b.flag=1 --这里选出最后确认日的数据,然后分组
group by b.仓库CD, b.JANCD
⇒DENSE_RANK() OVER(partition by 仓库CD,JANCD,ORDER BY 确认日 DESC)
修改了以后执行结果一样了,应该没有问题吧
select b.仓库CD,b.JANCD,sum(to_number(substr(b.最后确认日_数量,9)))
from
(select 仓库CD,JANCD,max(to_char(确认日,'yyyymmdd')||to_char(a.数量)) as 最后确认日_数量
from 入出库
where 类别='在库'
and 确认日>'00000000'
and 数量<>0
group by 仓库CD,JANCD
) b
;
干嘛不用 and 确认日 is not null ?
如果 确认日 是 varchar2 ,则to_char(确认日,'yyyymmdd')可省了,就更快了
select 仓库CD,JANCD,sum(a.数量) 数量
from 入出库 a
where exists(select 1 from 入出库 where a.仓库CD=仓库CD and a.JANCD=JANCD
and a.确认日>最后确认日) and 确认日>'00000000' and 类别='在库'
group by 仓库CD,JANCD
order by sum(a.数量)