你看看这样行不行,前提是假设你的表定义中按请购单号是有序的, DECLARE @MR TABLE ( 请购单号 VARCHAR(21), 请购数量 DECIMAL(10,2), 采购单号 VARCHAR(21), 行 INT, 采购数量 DECIMAL(10,2) )INSERT INTO @MR SELECT 'MR19302',37800.00,'HK-0020853',2,111000.0000 UNION SELECT 'MR19305',35290.00,'HK-0020853',2,111000.0000 UNION SELECT 'MR19299',37800.00,'HK-0020853',2,111000.0000 UNION SELECT 'MR19298',800.00,'HK-0020853',2,111000.0000 UNION SELECT 'MR19299',1660.00,'HK-0021572',2,1600.0000SELECT B.请购单号, B.采购单号, CASE WHEN 请购总量>采购数量 THEN 采购数量 ELSE 请购数量 END AS RESULT FROM ( SELECT --* 采购单号,SUM(请购数量) AS 请购总量,MIN(请购单号) AS 请购单号 FROM @MR WHERE 请购单号>='MR19299' GROUP BY 采购单号) AS A INNER JOIN @MR AS B ON A.请购单号 = B.请购单号 AND A.采购单号=B.采购单号
你看看这样行不行,前提是假设你的表定义中按请购单号是有序的, DECLARE @MR TABLE ( 请购单号 VARCHAR(21), 请购数量 DECIMAL(10,2), 采购单号 VARCHAR(21), 行 INT, 采购数量 DECIMAL(10,2) )INSERT INTO @MR SELECT 'MR19302',37800.00,'HK-0020853',2,111000.0000 UNION SELECT 'MR19305',35290.00,'HK-0020853',2,111000.0000 UNION SELECT 'MR19299',37800.00,'HK-0020853',2,111000.0000 UNION SELECT 'MR19298',800.00,'HK-0020853',2,111000.0000 UNION SELECT 'MR19299',1660.00,'HK-0021572',2,1600.0000SELECT B.请购单号, B.采购单号, CASE WHEN 请购总量>采购数量 THEN 采购数量 ELSE 请购数量 END AS RESULT FROM ( SELECT --* 采购单号,SUM(请购数量) AS 请购总量,MIN(请购单号) AS 请购单号 FROM @MR WHERE 请购单号>='MR19299' GROUP BY 采购单号) AS A INNER JOIN @MR AS B ON A.请购单号 = B.请购单号 AND A.采购单号=B.采购单号
DECLARE @MR TABLE ( 请购单号 VARCHAR(21), 请购数量 DECIMAL(10,2), 采购单号 VARCHAR(21), 行 INT, 采购数量 DECIMAL(10,2) ) INSERT INTO @MR SELECT 'MR19302',37800.00,'HK-0020853',2,111000.0000 insert into @MR SELECT 'MR19305',35290.00,'HK-0020853',2,111000.0000 insert into @MR SELECT 'MR19299',37800.00,'HK-0020853',2,111000.0000 insert into @MR SELECT 'MR19298',800.00,'HK-0020853',2,111000.0000 insert into @MR SELECT 'MR19299',1660.00,'HK-0021572',2,1600.0000 select id =identity(int,1,1),请购单号 qd,请购数量 qsl,采购单号 cd,采购数量 csl into # from @mr select qd 请购单,sum(qsl) 申购量,sum(case when ssl-qsl >0 then qsl else ssl end) 已采购量 from ( select qd,qsl,csl - isnull((select sum(qsl) from # where id <a.id and cd = a.cd),0) ssl from # a ) b group by qd drop table # /* 请购单 申购量 已采购量 --------------------- ---------------------------------------- ---------------------------------------- MR19298 800.00 110.00 MR19299 39460.00 39400.00 MR19302 37800.00 37800.00 MR19305 35290.00 35290.00 */
感谢各位倾情奉献。现我已经找到解决方法,分享如下declare @mrno varchar(30) --参数1 declare @part varchar(30) --参数2declare @txqty numeric(14,4) --返回结果 declare @tempmrnopono TABLE (ID integer, mrno varchar(30), sumqty numeric(14,4), pono varchar(30), poseq integer, acpqty numeric(14,4))insert into @tempmrnopono select l.ID,l.mrno,l.sumqty,n.pono,n.poseq,isnull(dbo.zz_gerfn_poacptxqty(n.pono,n.poseq ) ,0) as acpqty from (select a.pono,a.poseq,sum(a.sumqty) as sumqty from (select * from zz_mrpo_relation where mrno=@mrno and part=@part and status is null) a group by a.pono,a.poseq ) m inner join pur10110 n on m.pono=n.pono and m.poseq=n.poseq inner join zz_mrpo_relation l on m.pono=l.pono and m.poseq=l.poseq order by l.IDSELECT @txqty =SUM(k.uu) from (select case when tt>=0 then h.sumqty else h.sumqty+tt end as uu,m.* from (select a.pono,a.poseq, avg(a.acpqty) as acpqty,sum(a.sumqty) as sumqty,(avg(a.acpqty) -sum(a.sumqty)) AS tt from @tempmrnopono a INNER JOIN (select * from @tempmrnopono where mrno=@mrno) b on a.ID<=b.ID and a.mrno=b.mrno and a.pono=b.pono and a.poseq=b.poseq group by a.pono,a.poseq ) m INNER JOIN (select * from @tempmrnopono where mrno=@mrno) h on m.pono=h.pono and m.poseq=h.poseq ) kreturn @txqty
当结果小于0时,就取采购单采购数量减去前面请购数量剩余的数量。
37910-37800=110 >0即前面37910是采购数量减去前面请购数量剩余的数量
111000-37800=73200
73200-35290=37910 假设采购数量减去前面请购数量剩余的数量为36910
在36910-37800=-890 <0 则为36910
MR19299的结果为 36910+1600=38510
详细说明一下为什么这条记录不及算,其实感觉不用循环,分组统计一下即可。
MR19298 800.00 HK-0020853 2 111000.0000
这条记录为何不参与运算?
case就可以解决了阿
如上HK-0020853 2的采购数量为111000.0000 ,而请购数量为
MR19302 37800.00
MR19305 35290.00
MR19299 37800.00
MR19298 800.00
此时我想知道在这111000里面有多少是属于MR19299中请购的数量。所以必须先减去MR19302的37800 和MR19305的35290,(这几个请购单是有顺序的)此时还剩下37910,而37910大于MR19299的请购数量,所以归属到MR19299中的数量为37800,如上HK-0021572 2 的采购数量为1600.0000,而请够数量为
MR19299 1660.00
此时这1600中归属到MR19299中的请购数量最多也只能为1600了,而不可能是1660。而此时我是想知道MR19299中的请购数量最终有多少转化为采购数量,
即为37800+1600=39400。我想我说明白了吧
DECLARE @MR TABLE
(
请购单号 VARCHAR(21),
请购数量 DECIMAL(10,2),
采购单号 VARCHAR(21),
行 INT,
采购数量 DECIMAL(10,2)
)INSERT INTO @MR
SELECT 'MR19302',37800.00,'HK-0020853',2,111000.0000
UNION
SELECT 'MR19305',35290.00,'HK-0020853',2,111000.0000
UNION
SELECT 'MR19299',37800.00,'HK-0020853',2,111000.0000
UNION
SELECT 'MR19298',800.00,'HK-0020853',2,111000.0000
UNION
SELECT 'MR19299',1660.00,'HK-0021572',2,1600.0000SELECT
B.请购单号,
B.采购单号,
CASE WHEN 请购总量>采购数量 THEN 采购数量
ELSE 请购数量 END AS RESULT
FROM
(
SELECT --*
采购单号,SUM(请购数量) AS 请购总量,MIN(请购单号) AS 请购单号
FROM @MR
WHERE 请购单号>='MR19299'
GROUP BY 采购单号) AS A
INNER JOIN @MR AS B ON A.请购单号 = B.请购单号 AND A.采购单号=B.采购单号
DECLARE @MR TABLE
(
请购单号 VARCHAR(21),
请购数量 DECIMAL(10,2),
采购单号 VARCHAR(21),
行 INT,
采购数量 DECIMAL(10,2)
)INSERT INTO @MR
SELECT 'MR19302',37800.00,'HK-0020853',2,111000.0000
UNION
SELECT 'MR19305',35290.00,'HK-0020853',2,111000.0000
UNION
SELECT 'MR19299',37800.00,'HK-0020853',2,111000.0000
UNION
SELECT 'MR19298',800.00,'HK-0020853',2,111000.0000
UNION
SELECT 'MR19299',1660.00,'HK-0021572',2,1600.0000SELECT
B.请购单号,
B.采购单号,
CASE WHEN 请购总量>采购数量 THEN 采购数量
ELSE 请购数量 END AS RESULT
FROM
(
SELECT --*
采购单号,SUM(请购数量) AS 请购总量,MIN(请购单号) AS 请购单号
FROM @MR
WHERE 请购单号>='MR19299'
GROUP BY 采购单号) AS A
INNER JOIN @MR AS B ON A.请购单号 = B.请购单号 AND A.采购单号=B.采购单号
(
请购单号 VARCHAR(21),
请购数量 DECIMAL(10,2),
采购单号 VARCHAR(21),
行 INT,
采购数量 DECIMAL(10,2)
) INSERT INTO @MR SELECT 'MR19302',37800.00,'HK-0020853',2,111000.0000
insert into @MR SELECT 'MR19305',35290.00,'HK-0020853',2,111000.0000
insert into @MR SELECT 'MR19299',37800.00,'HK-0020853',2,111000.0000
insert into @MR SELECT 'MR19298',800.00,'HK-0020853',2,111000.0000
insert into @MR SELECT 'MR19299',1660.00,'HK-0021572',2,1600.0000 select id =identity(int,1,1),请购单号 qd,请购数量 qsl,采购单号 cd,采购数量 csl into # from @mr
select qd 请购单,sum(qsl) 申购量,sum(case when ssl-qsl >0 then qsl else ssl end) 已采购量 from (
select qd,qsl,csl - isnull((select sum(qsl) from # where id <a.id and cd = a.cd),0) ssl from # a
) b group by qd
drop table #
/*
请购单 申购量 已采购量
--------------------- ---------------------------------------- ----------------------------------------
MR19298 800.00 110.00
MR19299 39460.00 39400.00
MR19302 37800.00 37800.00
MR19305 35290.00 35290.00
*/
declare @part varchar(30) --参数2declare @txqty numeric(14,4) --返回结果
declare @tempmrnopono TABLE
(ID integer,
mrno varchar(30),
sumqty numeric(14,4),
pono varchar(30),
poseq integer,
acpqty numeric(14,4))insert into @tempmrnopono
select l.ID,l.mrno,l.sumqty,n.pono,n.poseq,isnull(dbo.zz_gerfn_poacptxqty(n.pono,n.poseq ) ,0) as acpqty
from
(select a.pono,a.poseq,sum(a.sumqty) as sumqty from
(select * from zz_mrpo_relation where mrno=@mrno and part=@part and status is null) a
group by a.pono,a.poseq ) m
inner join pur10110 n
on m.pono=n.pono and m.poseq=n.poseq
inner join zz_mrpo_relation l
on m.pono=l.pono and m.poseq=l.poseq order by l.IDSELECT @txqty =SUM(k.uu)
from (select case when tt>=0 then h.sumqty else h.sumqty+tt end as uu,m.* from
(select a.pono,a.poseq,
avg(a.acpqty) as acpqty,sum(a.sumqty) as sumqty,(avg(a.acpqty) -sum(a.sumqty)) AS tt
from @tempmrnopono a
INNER JOIN (select * from @tempmrnopono where mrno=@mrno) b
on a.ID<=b.ID and a.mrno=b.mrno and a.pono=b.pono and a.poseq=b.poseq
group by a.pono,a.poseq )
m
INNER JOIN (select * from @tempmrnopono where mrno=@mrno) h
on m.pono=h.pono and m.poseq=h.poseq
) kreturn @txqty