help me,关于查询，请各位帮忙!!!!!!

[lyq]

有原料入库(ylrk)、原料出库(ylck)两个流水表，现随时查询在(rq1,rq2)两时间段内所有原料(按编号(ylbh)进行区分)的入库件数(rkjs)、出库件数(ckjs)、当前结存件数(jcjs)，显示在一表格内，请教各位高手如何写sql语句，多谢!!!

[lyq]

仍未解决

[skypeople]

把你的表结构放出来，让我们瞧瞧，相信没有问题

[lyq]

ylrk(原料入库)：ylbh(编号)、cd(产地)、rkjs(入库件数)、rq(入库日期)

ylck(原料出库)：ylbh(编号)、cd(产地)、ckjs(出库件数)、rq(出库日期)

随时查询在(rq1,rq2参数)两时间段内所有原料(按编号(ylbh)进行区分)的入库件数(rkjs)、出库件数(ckjs)、当前结存件数(jcjs)

[yxjjx]

帮你up

[lyq]

query1.close;
query1.sql.Clear;

query1.sql.add('Select Sum(A.yljs),');
query1.sql.add('(Select Sum(ckjs) From ylck Where ylbh=A.ylbh),');
query1.sql.add('(Select Sum(yljs) From ylrk Where rq<=:rq2 and ylbh=A.ylbh Group By ylbh)-');
query1.sql.add('(Select Sum(ckjs) From ylck Where rq<=:rq2 and ylbh=A.ylbh Group By ylbh)');
query1.sql.add('From ylrk A');
query1.sql.add('Where (A.rq>=:rq1) and (A.rq<=:rq2) Group By A.ylbh');
query1.Parameters.ParamByName('rq1').value:=rq1.date;
query1.Parameters.ParamByName('rq2').value:=rq2.date;

query1.prepared;
query1.Open;

[newyj]

1。时间字段不明确
2。 当前结存件数(jcjs)是什么？
select rkjs,ckjs from ylrk full join ylck on ylbh
ylrkrq between :rq1 and :rq2

[tikkypeng]

Select Sum(A.rkjs) As 入库件数,
(Select Sum(ckjs) From ylck Where ylbh=A.ylbh) As 出库件数,
(Select Sum(rkjs) From ylrk Where DateTime<=rq2 and ylbh=A.ylbh Group By ylbh)-
(Select Sum(ckjs) From ylck Where DateTime<=rq2 and ylbh=A.ylbh Group By ylbh)
As rq2那天的结存件数
From ylrk A
Where (A.DateTime>=rq1) and (A.DateTime<=rq2)
Group By A.ylbh

[lyq]

有时间字段rq,当前结存件数指：到rq2时间止，某(全部)原料的入库-出库件数。

[zhuoyuesky]

可以使用子查询或建立临时表

[tikkypeng]

Select Sum(A.rkjs) As 入库件数,
(Select Sum(ckjs) From ylck Where ylbh=A.ylbh) As 出库件数,
(Select Sum(rkjs) From ylrk Where rq<=rq2 and ylbh=A.ylbh Group By ylbh)-
(Select Sum(ckjs) From ylck Where rq<=rq2 and ylbh=A.ylbh Group By ylbh)
As rq2那天的结存件数
From ylrk A
Where (A.rq>=rq1) and (A.rq<=rq2)
Group By A.ylbh

[Anitya]

原料入库(ylrk)、原料出库(ylck)两个流水表---之间的关系是？

试试：
select rkjs,ckjs,jcjs from ylrk ，ylck
where ylrk.ylbh=ylck.ylbh
and (between :rq1 and :rq2)
order by ylbh

[lyq]

千两狂死郎：A是什么意思呢。
treky.lee,无关系,还有一结存表(用在出库时判断出库件数是否大于结存件数，结存表为总时间段的结存量，出库量)，现在我想通过查询实现入库\出库\结存.

[lyq]

书写如何折行呢？

[lyq]

请大家回答，多谢了

[tikkypeng]

To;：　lyq(阿飞)　~~~只不过是把表As成为一个别名～～～