CREATE TABLE #temp(...)DECLARE cur CURSOR FOR select stockcd from bistk01t where status=1 and stockcd IN('B340','B310R')OPEN cur DECLARE @id varchar(10) FETCH NEXT FROM cur INTO @id WHILE(@@FETCH_STATUS = 0) BEGIN INSERT INTO #temp select a.itemcd, b.stockcd ,a.jqty '收付账数量', b.stockqty '库存数量', b.qcqty '待检数量(qc)' , a.jqty - b.stockqty '差异' from ufn_mmmr033('','',@id,'','','20150122','20150122','0') a left join insti01t b (nolock) on a.itemcd=b.itemcd and b.stockcd=@id where kind=9 FETCH NEXT FROM cur INTO @id END CLOSE cur DEALLOCATE cur SELECT * FROM #temp DROP TABLE #temp
大神你好: 1)你的方法是可行的,非常感谢你的思路,这个问题困扰我几天了 你的解答对我来说真是雪中送炭,真的非常感动,新的一年祝你家庭幸福,事业有成, 我已经关注你的CSDN的博客了,成为你的一名忠实的粉丝 2)我再将代码修改了一下,如下: CREATE TABLE #temp ( [itemcd] varchar(20), [stockcd] varchar(10), [a] decimal(18, 5), [b] decimal(18, 5), [c] decimal(18, 5), [d] decimal(18, 5), ) DECLARE cur CURSOR FOR select stockcd from bistk01t where status=1 and stockcd IN('B340','B310R')OPEN cur DECLARE @id varchar(10) FETCH NEXT FROM cur INTO @id WHILE(@@FETCH_STATUS = 0) BEGIN INSERT INTO #temp select a.itemcd, b.stockcd ,a.jqty '收付账数量', b.stockqty '库存数量', b.qcqty '待检数量(qc)' , a.jqty - b.stockqty '差异' from ufn_mmmr033('','',@id,'','','20150122','20150122','0') a left join insti01t b (nolock) on a.itemcd=b.itemcd and b.stockcd=@id where kind=9 FETCH NEXT FROM cur INTO @id END CLOSE cur DEALLOCATE cur SELECT * FROM #temp where #temp.d <>0 DROP TABLE #temp ------------------------------------------------------------------------------------------------------
FOR select stockcd from bistk01t where status=1 and stockcd IN('B340','B310R')OPEN cur
DECLARE @id varchar(10)
FETCH NEXT FROM cur INTO @id
WHILE(@@FETCH_STATUS = 0)
BEGIN
INSERT INTO #temp
select a.itemcd, b.stockcd ,a.jqty '收付账数量', b.stockqty '库存数量', b.qcqty '待检数量(qc)'
, a.jqty - b.stockqty '差异' from ufn_mmmr033('','',@id,'','','20150122','20150122','0') a
left join insti01t b (nolock) on a.itemcd=b.itemcd and b.stockcd=@id
where kind=9 FETCH NEXT FROM cur INTO @id
END
CLOSE cur
DEALLOCATE cur SELECT * FROM #temp
DROP TABLE #temp
是不是还要将定义 #temp 字段的类型。
-------------------------------------------------------------------------------------------------
1)你的方法是可行的,非常感谢你的思路,这个问题困扰我几天了
你的解答对我来说真是雪中送炭,真的非常感动,新的一年祝你家庭幸福,事业有成,
我已经关注你的CSDN的博客了,成为你的一名忠实的粉丝
2)我再将代码修改了一下,如下:
CREATE TABLE #temp
(
[itemcd] varchar(20),
[stockcd] varchar(10),
[a] decimal(18, 5),
[b] decimal(18, 5),
[c] decimal(18, 5),
[d] decimal(18, 5),
)
DECLARE cur CURSOR
FOR select stockcd from bistk01t where status=1 and stockcd IN('B340','B310R')OPEN cur
DECLARE @id varchar(10)
FETCH NEXT FROM cur INTO @id
WHILE(@@FETCH_STATUS = 0)
BEGIN
INSERT INTO #temp
select a.itemcd, b.stockcd ,a.jqty '收付账数量', b.stockqty '库存数量', b.qcqty '待检数量(qc)'
, a.jqty - b.stockqty '差异' from ufn_mmmr033('','',@id,'','','20150122','20150122','0') a
left join insti01t b (nolock) on a.itemcd=b.itemcd and b.stockcd=@id
where kind=9 FETCH NEXT FROM cur INTO @id
END
CLOSE cur
DEALLOCATE cur SELECT * FROM #temp where #temp.d <>0
DROP TABLE #temp
------------------------------------------------------------------------------------------------------