(急求)多表联合查询,解决问题马上结帖谢谢

Question

我有三个表orderinfo、表InputMateriel、表OutputMateriel、表StockPileInfo
-----------InputMateriel表结构如下:---------------------订料表
类型    供应商名 订料单号 订料名称   数量     要求到料时间 实际到料时间
ordtype proname  ordmcode orddsMname ordNumber   ordSqdata   ordsjdata
-----------InputMateriel表结构如下:---------------------来料表
类型    来料时间 来料单号 料名，供应商，来料数  
Ipttype mdata    Iptmcode mname pname   Iptmnum 
-----------OutputMateriel表结构如下:--------------------发料表
类型    来料时间 来料单号 料名，供应商，来料数  
Opttype mdata    Optmcode mname pname   Optmnum  
-----------StockPileInfo表结构如下:---------------------库存表
类型  料名   库存数
Stotype mname  Stomnum   进料之前此表是空表
-------------------------------------------------------
原始数据
-------------------------------------------------------------------------
select * from OrderInfo
结果：类型 供应商名 订料单号 订料名称  数量   要求到料时间 实际到料时间
      1001 一厂     A-1001   电源      200    2007-04-10   2007-04-12
      1002 二厂     B-1001   电容      100    2007-04-12   2007-04-12 
select * from InputMateriel 
   结果:类型  料名 供应商 来料单号 来料时间   来料数 
        1001  电源  一厂  AL-1001   2007-04-12  200    
        1001  电容  二厂  BL-1001   2007-04-15  100        
select * from OutputMateriel 
   结果:类型  料名 供应商 发料单号 发料时间 　发料数
        1001  电源  一厂  AF-1001   2007-04-13  100
        1001  电源  一厂  AF-1002   2007-04-15  30
        1001  电源  一厂  AF-1003   2007-04-16  20
select * from StockPileInfo （此表实时更新，来一批料现库存+来料数，发一批料现库存-发料数）
结果:类型　料名　现库存  
     1001  电源　50  
     1002  电容　100
-------------------------------------------------------------------------
注释：1、（来料时间=实际到料时间）2、(现在库存=来料数-发料数)
我的四个查询语句
-------------------------------------------------------------------------
1、
select * from orderinfo where ordtype='1001' and orddsMname='电源' and ordsjdata between '2007-04-10' and '2007-04-20'
   结果：类型 供应商名 订料单号 订料名称  数量   要求到料时间 实际到料时间
         1001 一厂     A-1001   电源      200    2007-04-10   2007-04-12
2、
select * from InputMateriel where Ipttype='1001'and mname='电源'and Mdata between '2007-04-10' and '2007-04-20'
   结果:类型  料名 供应商 来料单号 来料时间   来料数 
        1001  电源  一厂  AL-1001  2007-04-12  200    
3、
select * from OutputMateriel where Opttype='1001'and mname='电源'and Mdata between '2007-04-10' and '2007-04-20'
   结果:类型  料名 供应商 发料单号 发料时间 　发料数 
        1001  电源  一厂  AF-1001   2007-04-13  100
        1001  电源  一厂  AF-1002   2007-04-15  30 
        1001  电源  一厂  AF-1003   2007-04-16  20  
4、
select * from StockPileInfo  
结果:类型　料名　现库存  
     1001  电源　50
     1002  电容　100
------------------------------------------------------------------------
怎么按(类型、料名、Mdata)结合三个表查询得到如下结果：按时间排序
订料单号料名 来料单号 来料时间  来料数量 发料单号 发料时间 发料数量 库存
A-1001   电源 AL-1001  2007-04-12 200                                200
A-1001   电源                             AF-1001  2007-04-13 100    100  
A-1001   电源                             AF-1002  2007-04-15  30    70
A-1001   电源                             AF-1003  2007-04-16  20    50

Answer 1

1:你的StockPileInfo表是不是直接偶数据,不需要自己计算?
2:你的表结构有了,但是看不出来表之间怎么关联?

Answer 2

drop table orderinfo,InputMateriel,OutputMateriel,StockPileInfo
go
create table orderinfo(ordtype varchar(10),proname varchar(100),ordmcode varchar(20),orddsmname varchar(255),ordNumber numeric(20,6),ordSqdata datetime,ordsjdata datetime)
create table InputMateriel(ipttype varchar(10),mdata datetime,iptmcode varchar(20),mname varchar(255),pname varchar(100),Iptmnum numeric(20,6))
create table OutputMateriel(opttype varchar(10),mdata datetime,optmcode varchar(20),mname varchar(255),pname varchar(100),optmnum numeric(20,6))
create table StockPileInfo(stotype varchar(10),mname varchar(255),Stomnum numeric(20,6))insert into OrderInfo
select '1001','一厂','A-1001','电源',200,'2007-04-10','2007-04-12'
union all select '1002','二厂','B-1001','电容',100,'2007-04-12','2007-04-12'insert into InputMateriel(ipttype,mname,pname,iptmcode,mdata,iptmnum)
select '1001','电源','一厂','AL-1001','2007-04-12',200
union all select '1002','电容','二厂','BL-1001','2007-04-15',100insert into OutputMateriel(opttype,mname,pname,optmcode,mdata,optmnum)
select '1001','电源','一厂','AF-1001','2007-04-13',100
union all select '1001','电源','一厂','AF-1002','2007-04-15',30
union all select '1001','电源','一厂','AF-1003','2007-04-16',20
union all select '1002','电容','二厂','AF-1004','2007-04-20',10
union all select '1002','电容','二厂','AF-1005','2007-04-21',50
 
insert into StockPileInfo
select '1001','电源',50  
union all select '1002','电容',40select * from (
select b.ordmcode as '订料单号',
a.mname as '料名',
a.iptmcode as '来料单号',
convert(char(10),a.mdata,120) as '来料时间',
a.iptmnum as '来料数量',
NULL as '发料单号',
NULL as '发料时间',
NULL as '发料数量',
a.iptmnum as '库存'
from InputMateriel a
inner join orderinfo b on a.ipttype=b.ordtype and a.mname=b.orddsmname
union all
select b.ordmcode,
c.mname,
NULL,
NULL,
NULL,
c.optmcode,
convert(char(10),c.mdata,120),
c.optmnum,
isnull((select sum(isnull(optmnum,0))+x.Stomnum from OutputMateriel y where y.opttype=c.opttype and y.mname=c.mname and c.mdata<y.mdata),x.Stomnum)
from OutputMateriel c
inner join orderinfo b on c.opttype=b.ordtype and c.mname=b.orddsmname
inner join StockPileInfo x on x.stotype=c.opttype and x.mname=c.mname)t
order by 订料单号,料名,来料单号 desc/*
订料单号 料名 来料单号 来料时间 来料数量 发料单号 发料时间 发料数量 库存
--------------------------------------------------------------------------------------------
A-1001 电源 AL-1001 2007-04-12 200.000000 NULL NULL NULL 200.000000
A-1001 电源 NULL NULL NULL AF-1001 2007-04-13 100.000000 100.000000
A-1001 电源 NULL NULL NULL AF-1002 2007-04-15 30.000000 70.000000
A-1001 电源 NULL NULL NULL AF-1003 2007-04-16 20.000000 50.000000
B-1001 电容 BL-1001 2007-04-15 100.000000 NULL NULL NULL 100.000000
B-1001 电容 NULL NULL NULL AF-1004 2007-04-20 10.000000 90.000000
B-1001 电容 NULL NULL NULL AF-1005 2007-04-21 50.000000 40.000000
*/