请哪位高手帮写个游标解决一下问题a表 p_id amount in_amount (a表有三个字段 表示品名p_id ,缺货数量amount ,入库数量 in_amount 缺货登记表)
00001 5 0
00002 2 0
00002 3 0
00002 2 0
00003 3 0
00003 2 0
b表 p_id in_amount (b表有两个字段 表示品名p_id , 入库数量in_amount 入库表 )
00001 6
00002 5
00003 2需要得到一个一下结果 更新a表的in_amount字段 ,时间段我们暂时不考虑,要求把B表的入库数量按顺序匹配A表缺货数量,不足为零,作用是对缺货登记的执行情况的考核用,能否帮写个游标解决结果: a表 p_id amount in_amount
00001 5 5
00002 2 2
00002 3 3
00002 2 1
00003 3 2
00003 2 0
00001 5 0
00002 2 0
00002 3 0
00002 2 0
00003 3 0
00003 2 0
b表 p_id in_amount (b表有两个字段 表示品名p_id , 入库数量in_amount 入库表 )
00001 6
00002 5
00003 2需要得到一个一下结果 更新a表的in_amount字段 ,时间段我们暂时不考虑,要求把B表的入库数量按顺序匹配A表缺货数量,不足为零,作用是对缺货登记的执行情况的考核用,能否帮写个游标解决结果: a表 p_id amount in_amount
00001 5 5
00002 2 2
00002 3 3
00002 2 1
00003 3 2
00003 2 0
INSERT @TA
SELECT '00001', 5, 0 UNION ALL
SELECT '00002', 2, 0 UNION ALL
SELECT '00002', 3, 0 UNION ALL
SELECT '00002', 2, 0 UNION ALL
SELECT '00003', 3, 0 UNION ALL
SELECT '00003', 2, 0DECLARE @TB TABLE([p_id] VARCHAR(5), [in_amount] INT)
INSERT @TB
SELECT '00001', 6 UNION ALL
SELECT '00002', 5 UNION ALL
SELECT '00003', 2;WITH CTE AS
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY p_id ORDER BY p_id) AS SEQ FROM @TA
)
,CTE2 AS
(
SELECT *, [amount] AS IN_AMT FROM CTE WHERE SEQ=1
UNION ALL
SELECT CTE.*,CTE.[amount]+CTE2.IN_AMT FROM CTE, CTE2 WHERE CTE.p_id=CTE2.p_id AND CTE.SEQ=CTE2.SEQ+1
)
SELECT CTE2.p_id,amount,
CASE WHEN IN_AMT<=T.in_amount
THEN amount
ELSE CASE WHEN IN_AMT-amount>=T.in_amount THEN 0 ELSE T.in_amount END
END AS in_amount
FROM CTE2 JOIN @TB AS T ON CTE2.p_id=T.p_id
ORDER BY CTE2.p_id,SEQ
/*
p_id amount in_amount
----- ----------- -----------
00001 5 5
00002 2 2
00002 3 3
00002 2 0
00003 3 2
00003 2 0
*/
在关键字 'WITH' 附近有语法错误。
服务器: 消息 195,级别 15,状态 1,行 18
'ROW_NUMBER' 不是可以识别的 函数名。不过谢谢你,可能可以借鉴一下
-- 需要有时间作为排序的标准,也是提取数据先后的基准
DECLARE @A TABLE([p_id] VARCHAR(5),[dt] datetime, [amount] INT, [in_amount] INT)
INSERT @A
SELECT '00001', '2010-3-1', 5, 0 UNION ALL
SELECT '00002', '2010-3-2', 2, 0 UNION ALL
SELECT '00002', '2010-3-3',3, 0 UNION ALL
SELECT '00002', '2010-3-4',2, 0 UNION ALL
SELECT '00003', '2010-3-5',3, 0 UNION ALL
SELECT '00003', '2010-3-6',2, 0DECLARE @B TABLE([p_id] VARCHAR(5), [in_amount] INT)
INSERT @B
SELECT '00001', 6 UNION ALL
SELECT '00002', 5 UNION ALL
SELECT '00003', 2select t1.p_id,t1.dt,t1.amount,
(case when t2.in_amount-t1.cp>=0 then t1.amount
when t2.in_amount>t1.cp-t1.amount then t2.in_amount-t1.cp+t1.amount
else 0 end) in_amount
from
(select p_id,dt,amount,in_amount,
(select SUM(amount) from @A where p_id=t.p_id and dt<=t.dt) cp
from @A t) t1
inner join
@B t2
on t1.p_id=t2.p_id;
/*
00001 2010-03-01 00:00:00.000 5 5
00002 2010-03-02 00:00:00.000 2 2
00002 2010-03-03 00:00:00.000 3 3
00002 2010-03-04 00:00:00.000 2 0
00003 2010-03-05 00:00:00.000 3 2
00003 2010-03-06 00:00:00.000 2 0
*/