select * from tablename a where (select sum(期初数量)+sum(入库数量)-sum(出库数量) as 库存数量 from tablename where 物资编号=a.物资编号 and 日期<=a.日期)<0以上语句是假设你的tablename有 物资编号 期初数量 入库数量 出库数量 日期 等字段,可能你的有些字段是统计出来的,但你没有写明。 还有,你的有些地方是空的,如果是null,那就要加isnull.select * from tablename a where (select isnull(sum(isnull(期初数量,0)),0)+isnull(sum(isnull(入库数量,0)),0)-isnull(sum(isnull(出库数量,0)),0) as 库存数量 from tablename where 物资编号=a.物资编号 and 日期<=a.日期)<0
declare @a int,@b int ,@c int,@d int,@e int, @f datetime create table #tmp (物资编号 int, 期初数量 int, 入库数量 int, 出库数量 int, 库存数量 int, 日期 datetime)declare my_cursor cursor for select 物资编号 ,期初数量,入库数量,出库数量 ,日期 from test_x order by 物资编号 , 日期open my_cursor fetch next from my_cursor into @a,@b,@c,@d,@f while @@fetch_status = 0 begin select @e=sum(期初数量+入库数量-出库数量) from test_x where 物资编号=@a and 日期 <=@f if @e <0 begin insert into #tmp values (@a,@b,@c,@d,@e,@f) end fetch next from my_cursor into @a,@b,@c,@d,@f endclose my_cursor deallocate my_cursor select * from #tmp
CREATE TABLE stock ( id NUMBER, soh NUMBER, inbound_qty NUMBER, outbound_qty NUMBER, upd_date DATE ) / select x.id,x.upd_date, sum(nvl(soh,0)) + sum(nvl(inbound_qty,0)) - sum(nvl(outbound_qty,0)) from (select distinct id, upd_date from stock) x, stock y where x.id = y.id and x.upd_date >= y.upd_date group by x.id, x.upd_date having sum(nvl(soh,0)) + sum(nvl(inbound_qty,0)) - sum(nvl(outbound_qty,0)) < 0 /**注:nvl(para1,para2)是Oracle的函数,如果是别的数据库,需要转换
/*物资编号 期初数量 入库数量 出库数量 库存数量 日期 001 20 20 2001-01-01 001 10 30 2001-01-02 001 10 20 2001-01-03 002 30 30 2001-01-01 002 40 -10 2001-01-02 002 10 0 2001-01-03 就是怎么把库存数量小于0的那条记录给显示出来。 其中库存数量是动态统计的,并不是录入的,也就是说库存数量是要用sql给临时统计出来的 */Create table #tmp (物資編號 varchar(3),期初數量 int,入庫數量 int,出庫數量 int,庫存數量 int,日期 datetime) insert into #tmp values ('001',20,NULL,NULL,NULL,'2001-01-01') insert into #tmp values ('001',NULL,10,NULL,NULL,'2001-01-02') insert into #tmp values ('001',NULL,NULL,10,NULL,'2001-01-03') insert into #tmp values ('002',30,NULL,NULL,NULL,'2001-01-01') insert into #tmp values ('002',NULL,NULL,40,NULL,'2001-01-02') insert into #tmp values ('002',NULL,10,NULL,NULL,'2001-01-03') GODeclare @tmpVal int Declare @WZ varchar(3) Declare abc CURSOR FOR SELECT DISTINCT 物資編號 From #tmp Open abc FETCH NEXT From abc INTO @WZ WHILE @@FETCH_STATUS=0 Begin UPDATE #tmp SET @tmpval=庫存數量=ISNULL(@tmpval,0)+ISNULL(期初數量,0)+ISNULL(入庫數量,0)-ISNULL(出庫數量,0) WHERE 物資編號=@WZ FETCH NEXT FROM abc INTO @WZ SET @tmpVal=0 End CLOSE abc Deallocate abc SELECT * From #tmp WHERE 庫存數量<0 Drop table #tmp
其实这个问题肯定是要进行期初、期末的递进更新的(库存数可设计为计算字段),因此必须用游标遍历表。处理这类库存问题,如果要动态监控库存的话,觉得用以下的表结构效果会更好: ----------------------------------------------------------------- 物资编号 数量 入/出库 日期 id amt flag dt 001 20 1 2001-01-01 001 10 1 2001-01-02 001 10 0 2001-01-03 002 30 1 2001-01-01 002 40 0 2001-01-02 002 10 1 2001-01-03 ----------------------------------------------------------------- select id,sum(case flag when 1 then amt else -amt end) as 库存 group by id order by id
假如把表结构改成net_steven的格式, 这个语句该怎么写。
to net_steven: 我前面已经建议过了。现在也决定修改成这种结构。但是这个循环游历该怎么写,要保证速度。说说数据也不多,就1万多条数据,但速度很慢,要两分钟。
to 老衲: 对,流程上确实有问题,假如都是先入后出,也就不存在此问题。我同事说,由于入库的滞后,现在又只有这样。目前也不需要确定流水号,只要知道哪一天就好了。 我现在已建议她改成net_steven的结构,我也觉得应该那样。
经我一调试,杨帆破浪的语句惊人。效率非常高。不用游历就能找到这些记录了。只要几秒中的时间就全都出来了。 select * from tablename a where (select isnull(sum(isnull(期初数量,0)),0)+isnull(sum(isnull(入库数量,0)),0)-isnull(sum(isnull(出库数量,0)),0) as 库存数量 from tablename where 物资编号=a.物资编号 and 日期<=a.日期)<0
他用一个表的自身连接进行比较得出。 to rwq_: 我给同事建议和net_steven差不多,反正期初数量就是最早的那条记录,加flag为2也可以,或者不加也能得出哪些是期初的。觉得防在一个表里可能还是更好些。分成两个表,在一个表里的查询由于分成了两个表,可能速度上会加快。但当涉及到两表关联的时候,又会打折扣。权衡以下,应该分,还是不分呢?
where (select sum(期初数量)+sum(入库数量)-sum(出库数量) as 库存数量 from tablename where 物资编号=a.物资编号 and 日期<=a.日期)<0以上语句是假设你的tablename有 物资编号 期初数量 入库数量 出库数量 日期 等字段,可能你的有些字段是统计出来的,但你没有写明。
还有,你的有些地方是空的,如果是null,那就要加isnull.select * from tablename a
where (select isnull(sum(isnull(期初数量,0)),0)+isnull(sum(isnull(入库数量,0)),0)-isnull(sum(isnull(出库数量,0)),0) as 库存数量 from tablename where 物资编号=a.物资编号 and 日期<=a.日期)<0
create table #tmp (物资编号 int, 期初数量 int, 入库数量 int,
出库数量 int, 库存数量 int, 日期 datetime)declare my_cursor cursor for
select 物资编号 ,期初数量,入库数量,出库数量 ,日期 from test_x
order by 物资编号 , 日期open my_cursor
fetch next from my_cursor
into @a,@b,@c,@d,@f
while @@fetch_status = 0
begin
select @e=sum(期初数量+入库数量-出库数量) from test_x where 物资编号=@a and
日期 <=@f
if @e <0
begin
insert into #tmp values (@a,@b,@c,@d,@e,@f)
end
fetch next from my_cursor
into @a,@b,@c,@d,@f
endclose my_cursor
deallocate my_cursor
select * from #tmp
(
id NUMBER,
soh NUMBER,
inbound_qty NUMBER,
outbound_qty NUMBER,
upd_date DATE
)
/
select x.id,x.upd_date, sum(nvl(soh,0)) + sum(nvl(inbound_qty,0)) - sum(nvl(outbound_qty,0))
from (select distinct id, upd_date from stock) x, stock y
where x.id = y.id
and x.upd_date >= y.upd_date
group by x.id, x.upd_date
having sum(nvl(soh,0)) + sum(nvl(inbound_qty,0)) - sum(nvl(outbound_qty,0)) < 0
/**注:nvl(para1,para2)是Oracle的函数,如果是别的数据库,需要转换
001 20 20 2001-01-01
001 10 30 2001-01-02
001 10 20 2001-01-03
002 30 30 2001-01-01
002 40 -10 2001-01-02
002 10 0 2001-01-03
就是怎么把库存数量小于0的那条记录给显示出来。 其中库存数量是动态统计的,并不是录入的,也就是说库存数量是要用sql给临时统计出来的
*/Create table #tmp (物資編號 varchar(3),期初數量 int,入庫數量 int,出庫數量 int,庫存數量 int,日期 datetime)
insert into #tmp values ('001',20,NULL,NULL,NULL,'2001-01-01')
insert into #tmp values ('001',NULL,10,NULL,NULL,'2001-01-02')
insert into #tmp values ('001',NULL,NULL,10,NULL,'2001-01-03')
insert into #tmp values ('002',30,NULL,NULL,NULL,'2001-01-01')
insert into #tmp values ('002',NULL,NULL,40,NULL,'2001-01-02')
insert into #tmp values ('002',NULL,10,NULL,NULL,'2001-01-03')
GODeclare @tmpVal int
Declare @WZ varchar(3)
Declare abc CURSOR FOR SELECT DISTINCT 物資編號 From #tmp
Open abc
FETCH NEXT From abc INTO @WZ
WHILE @@FETCH_STATUS=0
Begin
UPDATE #tmp SET @tmpval=庫存數量=ISNULL(@tmpval,0)+ISNULL(期初數量,0)+ISNULL(入庫數量,0)-ISNULL(出庫數量,0) WHERE 物資編號=@WZ
FETCH NEXT FROM abc INTO @WZ
SET @tmpVal=0
End
CLOSE abc
Deallocate abc
SELECT * From #tmp WHERE 庫存數量<0
Drop table #tmp
-----------------------------------------------------------------
物资编号 数量 入/出库 日期
id amt flag dt
001 20 1 2001-01-01
001 10 1 2001-01-02
001 10 0 2001-01-03
002 30 1 2001-01-01
002 40 0 2001-01-02
002 10 1 2001-01-03
-----------------------------------------------------------------
select id,sum(case flag when 1 then amt else -amt end) as 库存
group by id order by id
这个语句该怎么写。
我前面已经建议过了。现在也决定修改成这种结构。但是这个循环游历该怎么写,要保证速度。说说数据也不多,就1万多条数据,但速度很慢,要两分钟。
对,流程上确实有问题,假如都是先入后出,也就不存在此问题。我同事说,由于入库的滞后,现在又只有这样。目前也不需要确定流水号,只要知道哪一天就好了。
我现在已建议她改成net_steven的结构,我也觉得应该那样。
表一:t1
物资编号(MatID) 期初数量(InitNumber) 表二:t2
物资编号(MatID) 数量(MatNumber) 入/出库(Flag) 日期(UpdDate)
select * from tablename a
where (select isnull(sum(isnull(期初数量,0)),0)+isnull(sum(isnull(入库数量,0)),0)-isnull(sum(isnull(出库数量,0)),0) as 库存数量 from tablename where 物资编号=a.物资编号 and 日期<=a.日期)<0
to rwq_:
我给同事建议和net_steven差不多,反正期初数量就是最早的那条记录,加flag为2也可以,或者不加也能得出哪些是期初的。觉得防在一个表里可能还是更好些。分成两个表,在一个表里的查询由于分成了两个表,可能速度上会加快。但当涉及到两表关联的时候,又会打折扣。权衡以下,应该分,还是不分呢?
001 2001-01-01 0(入) 2 1.3
001 2001-01-02 0 1 1.2
001 2001-01-02 1 2 d
001 2001-01-03 0 3 1
001 2001-01-04 1 4 e
其中d=(2*1.3+1*1.2)/3,e=(2*1.3+1*1.2+3*1-2*d)/4这个怎么实现