需求:需要遍历一组物料,找到每个物料今年采购过的供应商信息 并做后续查询。
--游标遍历获取每个物料的对应供应商,以键值对形式保存在临时表#temp2中
IF object_id('tempdb..#temp2') is not null
DROP TABLE #temp2
CREATE TABLE #temp2
(
FitemID int,
FSupplierID int
)
-- 声明变量
DECLARE @FitemID AS INT
DECLARE @FSupplierID AS INT
-- 声明外层游标
DECLARE Find_Supplier CURSOR FAST_FORWARD FOR
SELECT TOP 20 FItemID
FROM #temp1
ORDER BY FAmountsum desc
OPEN Find_Supplier;
-- 取第一个物料
FETCH NEXT FROM Find_Supplier INTO @FitemID;
WHILE @@FETCH_STATUS=0
BEGIN
-- 设置内层游标
DECLARE Insert_Supplier CURSOR FAST_FORWARD FOR
SELECT DISTINCT A.FSupplyID FROM ICPurchase A
left join ICPurchaseEntry B on A.FInterID =B.FInterID
left join t_supplier C on A.FSupplyID = C.FItemID
where B.