UC知道sql 多表查询优化
来源：百度知道 编辑：UC知道 时间：2024/06/16 22:28:20
有三个 一个进出仓物品名称表 wp_name(物品名称) ; 一个进仓表 zl(重量),sl(数量),jc_time（进仓时间）; 还有一个出仓表 zl(重量),sl(数量),cc_time(出仓时间)
rs1.open "select *," &_
"(select sum(zl) from [bcp_jc] where DATEDIFF(mm,jc_time,'"&yy&"-"&mm&"-1')>0 and ids=[bcp_list].id ) as alljczl," &_
"(select sum(zl) from [bcp_cc] where DATEDIFF(mm,cc_time,'"&yy&"-"&mm&"-1')>0 and ids=[bcp_list].id ) as allcczl," &_
"(select sum(sl) from [bcp_jc] where DATEDIFF(mm,jc_time,'"&yy&"-"&mm&"-1')>0 and ids=[bcp_list].id ) as alljcsl," &_
"(select sum(sl) from [bcp_cc] where DATEDIFF(mm,cc_time,'"&yy&"-"&mm&"-1')>0 and ids=[bcp_list].id ) as allccsl," &_
"(select sum(zl) from [bcp_jc] where DATEDIFF(mm,jc_time,'"&yy&"-"&mm&"-1')=0 and ids=[bcp_list].id ) as by_alljczl," &_
&
rs1.open "select *," &_
"(select sum(zl) from [bcp_jc] where DATEDIFF(mm,jc_time,'"&yy&"-"&mm&"-1')>0 and ids=[bcp_list].id ) as alljczl," &_
"(select sum(zl) from [bcp_cc] where DATEDIFF(mm,cc_time,'"&yy&"-"&mm&"-1')>0 and ids=[bcp_list].id ) as allcczl," &_
"(select sum(sl) from [bcp_jc] where DATEDIFF(mm,jc_time,'"&yy&"-"&mm&"-1')>0 and ids=[bcp_list].id ) as alljcsl," &_
"(select sum(sl) from [bcp_cc] where DATEDIFF(mm,cc_time,'"&yy&"-"&mm&"-1')>0 and ids=[bcp_list].id ) as allccsl," &_
"(select sum(zl) from [bcp_jc] where DATEDIFF(mm,jc_time,'"&yy&"-"&mm&"-1')=0 and ids=[bcp_list].id ) as by_alljczl," &_
&
不知道你用的是什么数据库，如果是MSSQL或者oracle的话，你可以先在数据库里面建一个存储过程，表与表之间连接，最好用join相关的语句，然后在程序里面有条件得调用。
当然，你得注意你的索引，看看是不是在关键查询字段上建立了索引。
看得有点累,写个存储过程吧,
ACCESS里也可以写个视图,然后加参数即可的
能直接截语句吗，看着这么多标点符号，晕得很