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-15'
union all select '1001','一厂','A-1002','电源',500,'2007-04-18','2007-04-20'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',100
union all select '1001','电源','一厂','AL-1002','2007-04-20',500insert 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','电容','二厂','BF-1001','2007-04-20',10
union all select '1002','电容','二厂','BF-1002','2007-04-21',50
union all select '1001','电源','一厂','AF-1004','2007-04-22',50
insert into StockPileInfo
select '1001','电源',500
union all select '1002','电容',40怎么按型号,料名,日期 得到如下查询结果:
订料单号 型号 料名 来料时间 来料数量 发料单号 发料时间 发料数量 库存
ordmcode mdata iptmnum optmcode mdata optmnum stomnum
A-1001 1001 电源 2007-04-12 200 NULL NULL NULL 200
A-1001 1001 电源 NULL NULL AF-1001 2007-04-13 100 100
A-1001 1001 电源 NULL NULL AF-1002 2007-04-15 30 70
A-1001 1001 电源 NULL NULL AF-1003 2007-04-16 20 50
A-1002 1001 电源 2007-04-20 500 NULL NULL NULL 550
A-1002 1001 电源 NULL NULL AF-1004 2007-04-22 50 500
NULL 1001 电源 NULL 700 NULL NULL 200 500
能否修改下面这条语句达到我想实现的结果:
select distinct * from (
select b.ordmcode as '订料单号',a.ipttype as '型号',a.mname as '料名',convert(char(10),a.mdata,120) as '来料时间',
a.iptmnum as '来料数量',NULL as '发料单号',NULL as '发料时间',NULL as '发料数量',
isnull((select sum(isnull(iptmnum,0))+a.iptmnum from inputMateriel y
where y.ipttype=a.ipttype and y.mname=a.mname and a.mdata>y.mdata),a.iptmnum) as '库存'
from InputMateriel a
inner join orderinfo b on a.ipttype=b.ordtype and a.mname=b.orddsmname
----inner join stockpileinfo x on x.stotype=a.ipttype and x.mname=a.mname
union all
select b.ordmcode,c.opttype,c.mname,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
union all
select null,a.ordtype,a.orddsmname,convert(char(10),a.ordsjdata,120),
(select sum(iptmnum) from inputmateriel x where x.ipttype=a.ordtype and x.mname=a.orddsmname),null,
null,
(select sum(optmnum) from outputmateriel y where y.opttype=a.ordtype and y.mname=a.orddsmname),z.stomnum
from orderinfo a
inner join stockpileinfo z on z.stotype=a.ordtype and z.mname=a.orddsmname)t
where t.型号='1001' and t.料名='电源'
and isnull(t.来料时间,t.发料时间) between '2007-04-10' and '2007-04-30'order by 订料单号 desc急啊,非常感谢!
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-15'
union all select '1001','一厂','A-1002','电源',500,'2007-04-18','2007-04-20'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',100
union all select '1001','电源','一厂','AL-1002','2007-04-20',500insert 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','电容','二厂','BF-1001','2007-04-20',10
union all select '1002','电容','二厂','BF-1002','2007-04-21',50
union all select '1001','电源','一厂','AF-1004','2007-04-22',50
insert into StockPileInfo
select '1001','电源',500
union all select '1002','电容',40怎么按型号,料名,日期 得到如下查询结果:
订料单号 型号 料名 来料时间 来料数量 发料单号 发料时间 发料数量 库存
ordmcode mdata iptmnum optmcode mdata optmnum stomnum
A-1001 1001 电源 2007-04-12 200 NULL NULL NULL 200
A-1001 1001 电源 NULL NULL AF-1001 2007-04-13 100 100
A-1001 1001 电源 NULL NULL AF-1002 2007-04-15 30 70
A-1001 1001 电源 NULL NULL AF-1003 2007-04-16 20 50
A-1002 1001 电源 2007-04-20 500 NULL NULL NULL 550
A-1002 1001 电源 NULL NULL AF-1004 2007-04-22 50 500
NULL 1001 电源 NULL 700 NULL NULL 200 500
能否修改下面这条语句达到我想实现的结果:
select distinct * from (
select b.ordmcode as '订料单号',a.ipttype as '型号',a.mname as '料名',convert(char(10),a.mdata,120) as '来料时间',
a.iptmnum as '来料数量',NULL as '发料单号',NULL as '发料时间',NULL as '发料数量',
isnull((select sum(isnull(iptmnum,0))+a.iptmnum from inputMateriel y
where y.ipttype=a.ipttype and y.mname=a.mname and a.mdata>y.mdata),a.iptmnum) as '库存'
from InputMateriel a
inner join orderinfo b on a.ipttype=b.ordtype and a.mname=b.orddsmname
----inner join stockpileinfo x on x.stotype=a.ipttype and x.mname=a.mname
union all
select b.ordmcode,c.opttype,c.mname,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
union all
select null,a.ordtype,a.orddsmname,convert(char(10),a.ordsjdata,120),
(select sum(iptmnum) from inputmateriel x where x.ipttype=a.ordtype and x.mname=a.orddsmname),null,
null,
(select sum(optmnum) from outputmateriel y where y.opttype=a.ordtype and y.mname=a.orddsmname),z.stomnum
from orderinfo a
inner join stockpileinfo z on z.stotype=a.ordtype and z.mname=a.orddsmname)t
where t.型号='1001' and t.料名='电源'
and isnull(t.来料时间,t.发料时间) between '2007-04-10' and '2007-04-30'order by 订料单号 desc急啊,非常感谢!
orderinfo--订料表
inputmateriel----来料表
outputmateriel---发料表
stockpileinfo----库存表
谁帮帮我啊!
具体(型号 料名)在各个表中对应的字段名以下给出:
orderinfo 订料表 ordtype,oorddsmname
inputmateriel 来料表 ipttype,mname
outputmateriel 发料表 opttype,mname
stockpileinfo 库存表 stotype,mname
首先我是订料,订料之后来料,来一笔料:库存数(Stomnum)=原库存数(Stomnum)+来料数(Iptmnum)
发一笔料:库存数(Stomnum)=原库存数(Stomnum)-发料数(Optmnum)(原始数据我已经给出)有心的朋友请帮帮忙,万分感谢...
Select
A.编号,
SUM(Case TYPE When 1 Then C.t1 When 2 Then C.t2 Else 0 End) As 总金额
From
TAB1 A
Inner Join
TAB2 B
On A.编号 = B.编号
Inner Join
TAB3 C
On B.PS = C.PS
Where A.FLAG = 1
Group By
A.编号