2451
社区成员
- 全部
数据库多表查询的问题
就是把两个表的同材料的数量累加 再相减，用存储过程实现如下：（小于某个日期的，日期可以通过参数传递）
declare @date datetime
select clbhid,sum(innumber) as innumber into #temptable1 from storage where indate<@date
select clbhid,sum(outnumber) as outnumber into #temptable2 from outstock where outnumber<@date
select clbhid,innumber-outnumber from #temptable1 left join #temptable2 on #temptable1.clbhid=#temptable2.clbhid
但现在paradox数据库不支持存储过程，可以用视图，请问我该怎样实现上面的功能?
declare @date datetime
select clbhid,sum(innumber) as innumber into #temptable1 from storage where indate<@date
select clbhid,sum(outnumber) as outnumber into #temptable2 from outstock where outnumber<@date
select clbhid,innumber-outnumber from #temptable1 left join #temptable2 on #temptable1.clbhid=#temptable2.clbhid
但现在paradox数据库不支持存储过程，可以用视图，请问我该怎样实现上面的功能?
...全文
当前发帖距今超过3年，不再开放新的回复
发表回复
pgz_007 2008-06-20
楼上的，storage，outstock 表每个数量和编号都对应一个日期，你在累加(sum)过程中多个日期就变成了一个日期。
所以最好是能先单表控制，再去连接。所以估计你那样是不行的。
所以最好是能先单表控制，再去连接。所以估计你那样是不行的。
梅青松 2008-06-20
没有环境
你试试连接行不行
select a.clbhid,sum(a.innumber) as innumber - sum(b.outnumber) as outnumber
from storage a, outstock b
where a.clbhid = b.clbhid and a.indate <@date
and b.outnumber <@date
group by a.clbhid
你试试连接行不行
select a.clbhid,sum(a.innumber) as innumber - sum(b.outnumber) as outnumber
from storage a, outstock b
where a.clbhid = b.clbhid and a.indate <@date
and b.outnumber <@date
group by a.clbhid
pgz_007 2008-06-20
"storageview.sql" 视图：
select clbhid,sum(innumber) as innumber,indate from storage group by clbhid,indate order by clbhid
"outstockview.sql" 视图：
select clbhid,sum(outnumber) as outnumber,outdate from outstock group by clbhid,outdate order by clbhid
但把这样两个视图连接再相减是不行的，因为 数量累加过程中 多个日期只变成一个了，但又不能设置变量，麻烦
select clbhid,sum(innumber) as innumber,indate from storage group by clbhid,indate order by clbhid
"outstockview.sql" 视图：
select clbhid,sum(outnumber) as outnumber,outdate from outstock group by clbhid,outdate order by clbhid
但把这样两个视图连接再相减是不行的，因为 数量累加过程中 多个日期只变成一个了，但又不能设置变量，麻烦
