--构建测试数据 create table MOCTA(TA001 varchar(10),TA002 varchar(10),TA006 varchar(10),TA034 varchar(10),TA035 varchar(10),TA015 int,TA013 varchar(10)) insert into MOCTA select '513','120900527','A01','desk','200*100*20',100,'Y' create table MOCTC(TC002 int,TC003 varchar(20),TC001 int ) insert into MOCTC select 1,'2012-09-13',1 union select 2,'2012-09-15',2 union select 3,'2012-09-18',3 create table MOCTE(TE011 varchar(10),TE012 varchar(10),TE005 int,TE019 varchar(10),TE001 int,TE002 int) insert into MOCTE select '513','120900527',30,'Y',1,1 union select '513','120900527',50,'Y',2,2 union select '513','120900527',20,'Y',3,3 create table MOCTI(TI013 varchar(10),TI014 varchar(10),TI007 int,TI037 varchar(10)) insert into MOCTI select '513','120900527',35,'Y' --解决方案 ;with CET1 as(SELECT a.TA001+'-'+a.TA002 [单别/单号], a.TA006 品号, a.TA034 品名, a.TA035 规格, a.TA015 预计产量, ISNULL(CONVERT(varchar(100),CAST(d.TC003 AS datetime),23),'') 发外日期, ISNULL(b.TE005 ,0) 发外数量, ISNULL(c.TI007 ,0) 进货数量 FROM MOCTA a LEFT JOIN MOCTE b ON b.TE011=a.TA001 AND b.TE012=a.TA002 AND b.TE019='Y' LEFT JOIN MOCTI c ON c.TI013=a.TA001 AND c.TI014=a.TA002 AND c.TI037='Y' left join MOCTC d on d.TC001=b.TE001 AND d.TC002=b.TE002 WHERE a.TA013='Y' AND a.TA001 IN('513','522') AND a.TA002='120900527' --ORDER BY a.TA001+a.TA002,发外日期 asc )select [单别/单号],品号,品名,规格,预计产量,发外日期,发外数量, case when 进货数量 > 发外数量sum then 发外数量 else case when 进货数量 > 发外数量sum - 发外数量 then 进货数量 - 发外数量sum + 发外数量 else 0 end end 进货数量 from CET1 a cross apply (select sum(发外数量) 发外数量sum from CET1 b where a.[单别/单号]= b.[单别/单号] and a.品号 = b.品号 and b.发外日期 <= a.发外日期)b ORDER BY [单别/单号],发外日期 /* 单别/单号 品号 品名 规格 预计产量 发外日期 发外数量 进货数量 --------------------- ---------- ---------- ---------- ----------- -------------------------------------------- 513-120900527 A01 desk 200*100*20 100 2012-09-13 30 30 513-120900527 A01 desk 200*100*20 100 2012-09-15 50 5 513-120900527 A01 desk 200*100*20 100 2012-09-18 20 0(3 行受影响)*/
单别/单号 品号 品名 规格 预计产量 发外日期 发外数量 进货数量
----------------------------------------------------------------------------------------------------
513-120900527 A01 desk 200*100*20 100 2012-09-13 30 35
513-120900527 A01 desk 200*100*20 100 2012-09-13 50 20
进货的次数和数量都不要紧,我的要求是把所有的进货数量先满足最早的那次发外,像你说的可以这么显示:单别/单号 品号 品名 规格 预计产量 发外日期 发外数量 进货数量
----------------------------------------------------------------------------------------------------
513-120900527 A01 desk 200*100*20 100 2012-09-13 30 30
513-120900527 A01 desk 200*100*20 100 2012-09-15 50 25
513-120900527 A01 desk 200*100*20 100 2012-09-18 20 0
--构建测试数据
create table MOCTA(TA001 varchar(10),TA002 varchar(10),TA006 varchar(10),TA034 varchar(10),TA035 varchar(10),TA015 int,TA013 varchar(10))
insert into MOCTA select '513','120900527','A01','desk','200*100*20',100,'Y'
create table MOCTC(TC002 int,TC003 varchar(20),TC001 int )
insert into MOCTC select 1,'2012-09-13',1
union select 2,'2012-09-15',2
union select 3,'2012-09-18',3
create table MOCTE(TE011 varchar(10),TE012 varchar(10),TE005 int,TE019 varchar(10),TE001 int,TE002 int)
insert into MOCTE select '513','120900527',30,'Y',1,1
union select '513','120900527',50,'Y',2,2
union select '513','120900527',20,'Y',3,3
create table MOCTI(TI013 varchar(10),TI014 varchar(10),TI007 int,TI037 varchar(10))
insert into MOCTI select '513','120900527',35,'Y'
--解决方案
;with CET1 as(SELECT
a.TA001+'-'+a.TA002 [单别/单号],
a.TA006 品号,
a.TA034 品名,
a.TA035 规格,
a.TA015 预计产量,
ISNULL(CONVERT(varchar(100),CAST(d.TC003 AS datetime),23),'') 发外日期,
ISNULL(b.TE005 ,0) 发外数量,
ISNULL(c.TI007 ,0) 进货数量
FROM MOCTA a
LEFT JOIN MOCTE b ON b.TE011=a.TA001 AND b.TE012=a.TA002 AND b.TE019='Y'
LEFT JOIN MOCTI c ON c.TI013=a.TA001 AND c.TI014=a.TA002 AND c.TI037='Y'
left join MOCTC d on d.TC001=b.TE001 AND d.TC002=b.TE002
WHERE a.TA013='Y' AND a.TA001 IN('513','522') AND a.TA002='120900527'
--ORDER BY a.TA001+a.TA002,发外日期 asc
)select [单别/单号],品号,品名,规格,预计产量,发外日期,发外数量,
case when 进货数量 > 发外数量sum then 发外数量
else case when 进货数量 > 发外数量sum - 发外数量 then 进货数量 - 发外数量sum + 发外数量
else 0 end
end 进货数量
from CET1 a cross apply
(select sum(发外数量) 发外数量sum from CET1 b where a.[单别/单号]= b.[单别/单号] and a.品号 = b.品号 and b.发外日期 <= a.发外日期)b
ORDER BY [单别/单号],发外日期
/*
单别/单号 品号 品名 规格 预计产量 发外日期 发外数量 进货数量
--------------------- ---------- ---------- ---------- ----------- --------------------------------------------
513-120900527 A01 desk 200*100*20 100 2012-09-13 30 30
513-120900527 A01 desk 200*100*20 100 2012-09-15 50 5
513-120900527 A01 desk 200*100*20 100 2012-09-18 20 0(3 行受影响)*/
现在的问题是:如果只有一张进货单就没问题,如果多次进货的话就出问题了。如:9.17号有一张进货单,数量是50PCS,再来查询就出现以下问题了/*
单别/单号 品号 品名 规格 预计产量 发外日期 发外数量 进货数量
--------------------- ---------- ---------- ---------- ----------- --------------------------------------------
513-120900527 A01 desk 200*100*20 100 2012-09-13 30 20
513-120900527 A01 desk 200*100*20 100 2012-09-15 30 5
513-120900527 A01 desk 200*100*20 100 2012-09-18 50 0
513-120900527 A01 desk 200*100*20 100 2012-09-18 50 0
513-120900527 A01 desk 200*100*20 100 2012-09-18 20 0
513-120900527 A01 desk 200*100*20 100 2012-09-18 20 0*/
不知道各位能否看懂,如果有不清楚的还请加Q:497930813