求教SQL语句 or fastreport解决 (100分)

  • 主题发起人 blackgun
  • 开始时间
B

blackgun

Unregistered / Unconfirmed
GUEST, unregistred user!
我现在有这样的纪录
STOCK表
进仓日期 货物ID 进货数量

SHIPMENT表
出仓日期 货物ID 出货数量

我想让他打印出一批货物从进到出掉的列表
进货数量 出货数量 库存数量
100 100
50 50
10 40

这样应该怎样写?如果SQL无法解决的话,在FASTREPORT的报表中解决也好[:D]
 
select a.JHSL,b.CHSL,(a.JHSL-b.CHSL,)as 'SYSL'
From STOCK a,SHIPMENT b
where a.ID=B.ID
 
Select 货物ID﹐进货数量
From stock
Union all
Select 出货数量
From shipment
Order by 货物ID
 
select STOCK.进货数量,SHIPMENT.出货数量 from STOCK,SHIPMENT
where STOCK.货物ID = SHIPMENT.货物ID;

然后在客户端加一个计算域, 库存数量= 进货数量-出货数量
把要计算的数据放在客户端,减轻服务器的负担明白了吗?
 
基本表的主键是什么,一天可能进出同样的货吗?
 
to yvtong:
货物id
可以进出同样的货
to maming
我去试一下,不过有一个时间的因素,不知道能不能成功
to chj
你这样恐怕不行呀

我主要解决的是库存问题
 
select a.进货数量 ,b.出货数量,a.进货数量-b.出货数量 as 库存数量
from
(select 进仓日期,货物ID, 进货数量 from STOCK) a,
(select 出仓日期,出货数量,货物ID from SHIPMENT) b
where
a.货物ID=b.货物ID
//如果要加入时间段就在后面继续
 
看来我的问题写得不够详细,让各位有所误会。
我想要得是一种货物从进到出的一个列表。
我现在想要在这个列表上添加库存。日期是一定要的
日期 进仓数量 出仓数量 库存数量
2002-7-5 100 100
2002-7-15 50 50
2002-8-13 10 40

关键在于这个库存数量如何解决?
其他的我已经解决了
 
对于这两个表,xiaoywh说的maming不是完全一样吗?
不过这个前提是a.id和b.id都必须是唯一的 ,表中能满足吗?!
 
那上述那个SQL中不就是解决了库存吗?
 
一个货物可以有几次进仓和几次出仓
to xiaoywh
可以麻烦你加一下时间的语句吗
 
要ID不满足唯一就加入Group by id

select a.进仓日期, b.出仓日期,a.进货数量 ,b.出货数量,a.进货数量-b.出货数量 as 库存数量
from
(select 进仓日期,货物ID, sum(进货数量) as 进货数量 from STOCK Group by 货物ID) a
(select 出仓日期,货物ID, sum(出货数量) as 出货数量 from SHIPMENT STOCK Group by 货物ID) b
where
a.货物ID=b.货物ID
 
to xiaoywh
首先,感谢你的回答
但是你看一下我的第二条记录
照你这样的写法,库存应该为负数了吧
 
那你日期的原则是什么,如:
对A货 进货日期分别是
2002-08-01 100
2002-08-05 233
出贷日期
2002-09-02 150
2002-09-10 150
那么我的货存是不是=总进货-总出货吗
我查:2002-8 至200-10的贷物流水帐
你想让日期显示是分条取(那贷存亦是分条取,还是二者取其一依据又是什么
 
最好给些数据,和输出结果
 
你用的是什么数据库???
 
oracle
每一个日期都是分开的。
就相当于一个流水账。
例子的话,我那个例子已经比较清楚了
 
OK 解决了!不过你要建两个测试表:
测试表:
dtIn (入库表
id : char(1)
inDate : date
inValue: number(4)

value:
1 2002-8-1 100
1 2002-8-10 300
1 2002-8-12 300

dtOut 出库表
id : char(1)
outdate: date
outValue: number(4)

value:
1 2002-8-3 50
1 2002-8-10 150
1 2002-8-13 100
1 2002-8-14 200

测试时货号固定为 id='1'

结果:
2002-08-02 100 0 100
2002-08-03 0 50 50
2002-08-10 300 150 200
2002-08-12 300 0 500
2002-08-13 0 100 400
2002-08-14 0 200 200

select a.mdate,a.invalue,a.outvalue,b.value
from
(
select rownum as num,a.mdate,a.invalue,a.outvalue
from
(
SELECT mdate, SUM(invalue) AS invalue, SUM(outvalue) AS outvalue, SUM(invalue)
- SUM(outvalue) AS TempCount
FROM (SELECT id,outDate AS mdate, 0 AS invalue, SUM(outValue) AS outvalue
FROM dtOut
where id='1'
GROUP BY outDate,id
UNION
SELECT id,indate AS mdate, SUM(inValue) AS idvalue, 0 AS outvalue
FROM dtIN
where id='1'
GROUP BY indate,id) a
GROUP BY mdate
) a
) a,
(
select rownum as num,a.mdate,a.invalue,a.outvalue,
(
select sum(invalue)-sum(outvalue) as value
from
(
select mdate,invalue,outvalue,rownum as num
from
(
SELECT mdate, SUM(invalue) AS invalue, SUM(outvalue) AS outvalue, SUM(invalue)
- SUM(outvalue) AS TempCount
FROM (SELECT id,outDate AS mdate, 0 AS invalue, SUM(outValue) AS outvalue
FROM dtOut
where id='1'
GROUP BY outDate,id
UNION
SELECT id,indate AS mdate, SUM(inValue) AS idvalue, 0 AS outvalue
FROM dtIN
where id='1'
GROUP BY indate,id) a
GROUP BY mdate
)
) b
where b.num<=c.num
) as value
from
(
SELECT mdate, SUM(invalue) AS invalue, SUM(outvalue) AS outvalue, SUM(invalue)
- SUM(outvalue) AS TempCount
FROM (SELECT id,outDate AS mdate, 0 AS invalue, SUM(outValue) AS outvalue
FROM dtOut
where id='1'
GROUP BY outDate,id
UNION
SELECT id,indate AS mdate, SUM(inValue) AS idvalue, 0 AS outvalue
FROM dtIN
where id='1'
GROUP BY indate,id) a
GROUP BY mdate
) a,
(
select rownum as num
from
(
SELECT mdate
FROM (SELECT id,outDate AS mdate, 0 AS invalue, SUM(outValue) AS outvalue
FROM dtOut
where id='1'
GROUP BY outDate,id
UNION
SELECT id,indate AS mdate, SUM(inValue) AS idvalue, 0 AS outvalue
FROM dtIN
where id='1'
GROUP BY indate,id) a
GROUP BY mdate
) a

) c
where rownum=c.num



) b
where a.num=b.num
 
谢谢,非常感谢。
100分是你的了
 
顶部