请问在SQL里边怎么实现对数据的遍历
比如说表X1(cols1),x2(cols1,cols2)
我现在想得到这样的结果,我需要从X1中提取cols1作为比较条件,X2中的cols1需要与X1中的每一行数据做比较,我现在知道用游标可以实现,但是速度很慢,大家有没有什么其他更好的办法呢?
比如说表X1(cols1),x2(cols1,cols2)
我现在想得到这样的结果,我需要从X1中提取cols1作为比较条件,X2中的cols1需要与X1中的每一行数据做比较,我现在知道用游标可以实现,但是速度很慢,大家有没有什么其他更好的办法呢?
指定一下怎么比较,别让大家猜呀.
select * from tblASET NOCOUNT ON
DECLARE @iNextRowId int,@iCurrentRowId int,@iLoopControl int,@ID int
-- Initialize variables!
SELECT @iLoopControl = 1
SELECT @iNextRowId = MIN(id) FROM tblA
-- Make sure the table has data.
IF ISNULL(@iNextRowId,0) = 0
BEGIN
SELECT 'No data in found in table!'
RETURN
END
-- Retrieve the first row
SELECT @iCurrentRowId = id,@id = ID FROM tblA WHERE id = @iNextRowId
-- start the main processing loop.
WHILE @iLoopControl = 1
BEGIN
-- This is where you perform your detailed row-by-row
-- processing.
-- Reset looping variables.
SELECT 'A'
SELECT @iNextRowId = NULL
-- get the next iRowId
SELECT @iNextRowId = MIN(id)FROM tblA WHERE ID > @iCurrentRowId
-- did we get a valid next row id?
IF ISNULL(@iNextRowId,0) = 0
BEGIN
BREAK
END
-- get the next row.
SELECT @iCurrentRowId = ID,@ID = ID FROM tblA WHERE ID = @iNextRowId
END
RETURN
ST NOCOUNT OFF
是下面这段代码
INSERT INTO #TEMP
SELECT SUM(Quantity) AS BSUM_Quantity,@var_item_key
FROM F_Invoice_Main
WHERE InvoiceNumber_Key IN
(SELECT InvoiceNumber_Key
FROM F_Invoice_Main WHERE ItemNumber_Key =@var_item_key)
说明:这个变量@var_item_key的值就是需要从tb表得到的。需要得到tb表中的每一条数据
然后做等值比较