mysql - 创建新临时表的行数

Question

这是我的光标

DECLARE @Row_Id Int;
DECLARE @Customer_Id Varchar(50);
DECLARE @TransId VARCHAR(50);   
DECLARE @TimeStamp DATETIME;

Declare @abcDT As [dbo].[abcDataType]

DECLARE @Result_Table TABLE(Row_Id Int, Customer_Id Varchar(50));   

DECLARE cur CURSOR FOR SELECT * FROM @abcDT
OPEN cur    
FETCH NEXT FROM cur INTO @Row_Id, @Customer_Id

WHILE @@FETCH_STATUS = 0 
BEGIN   
    SET @TransId = null;        
    SELECT TOP(1) @TransId=[Transaction_Id], @TimeStamp=[Time_Stamp] FROM [dbo].[Abc] 
    WHERE  [Customer_Id]=@Customer_Id  ORDER BY [Time_Stamp] DESC 

    IF (@TransId IS NULL) 
    BEGIN   
        Insert Into @Result_Table(Row_Id, Customer_Id) Values(@Row_Id,@Customer_Id);
    END     

FETCH NEXT FROM cur INTO @Row_Id, @Customer_Id
END
CLOSE cur    
DEALLOCATE cur
SELECT * FROM @Result_Table;

如果@Result_Table1计数超过5，我想创建新的@Result_table1。

我的意思是 @Result_table 仅包含 5 条记录(如果它超过创建新的临时表等)。

或者例如创建一个包含 17 条记录的临时表..然后创建 4 个包含 5 + 5 + 5 + 2 记录的临时表..最后返回所有临时表..请帮助我概括这个逻辑。

Answer 1

这非常草率，但我相信它完成了您的要求:

-------------------------------------
-- This code just populates a temp table #big_temp with rows of test data
----------------------------------------

DECLARE @counter    INT

SET @counter = 1

CREATE TABLE #big_temp
(
    row_id  INT,
    cust_id CHAR(6)
)



WHILE @counter < 23
BEGIN

    INSERT INTO #big_temp
    VALUES(@counter, '123456')

    SELECT @counter = @counter + 1

END

--now we will see a table with 22 rows in it.
SELECT * FROM #big_temp

------------------------------------------------------
-- now we will divide the rows from #big_temp into #temp1, temp2, etc. as necessary
-----------------------------------------------------

DECLARE @row_count INT
DECLARE @tbl_count INT

DECLARE @i  INT
DECLARE @j  INT

SELECT @row_count = count(*) FROM #big_temp

SELECT @tbl_count = 1 + @row_count / 5
SELECT @i = @tbl_count

SELECT @j = 0

SELECT @i AS tbl_count
----create all the temp tables
WHILE @i > 0
BEGIN
    --SELECT @i AS I, @j AS J
    --create a new temp table dynamically
    exec ('SELECT row_id, cust_id INTO #tbl' + @i + ' FROM #big_temp WHERE row_id BETWEEN ' + @j + '+1 AND (' + @tbl_count + '-' + @i + '+1)* 5; SELECT * FROM #tbl' + @i)
    SET @j = @j + 5
    SET @i = @i - 1

END

如果 #big_temp 能被 5 整除(20 行、25 行等)，则 @tbl_count 太高了 1 - 您可以添加一行来检查 @row_count % 5 = 0，然后从 @tbl_count 中减去 1