如何解决游标工作仅在SQL服务器中第一次

Question

我在一个过程中声明了一个游标，并在其中做了一个循环。 但是当我执行过程游标时仅在第一次发生这种情况时发生，我希望每次执行过程时都会发生。

DECLARE SUP_CUR CURSOR SCROLL DYNAMIC  FOR SELECT * FROM @saleSup
DECLARE @SUP_TEMP AS INT
OPEN SUP_CUR
WHILE @@FETCH_STATUS =0 
BEGIN
    FETCH NEXT FROM SUP_CUR INTO @SUP_TEMP
    SELECT COUNT (DISTINCT saleRepId),userId   FROM @tabletemp WHERE userId = @SUP_TEMP GROUP BY userId 
END 
CLOSE SUP_CUR;
DEALLOCATE SUP_CUR; 

Answer 1

将 FETCH

放在循环末尾旁边并添加第一个 FETCH

DECLARE SUP_CUR CURSOR SCROLL DYNAMIC  FOR SELECT * FROM @saleSup
DECLARE @SUP_TEMP AS INT
OPEN SUP_CUR
FETCH NEXT FROM SUP_CUR INTO @SUP_TEMP
WHILE @@FETCH_STATUS =0 
BEGIN
    SELECT COUNT (DISTINCT saleRepId),userId   FROM @tabletemp WHERE userId = @SUP_TEMP GROUP BY userId 
    FETCH NEXT FROM SUP_CUR INTO @SUP_TEMP
END 
CLOSE SUP_CUR;
DEALLOCATE SUP_CUR; 

Answer 2

像

这样更改cursor。您需要在 while 循环开始之前添加 fetch 以获取first row，并在while loop结束时添加 FETCH NEXT 以获取next row

DECLARE @SUP_TEMP AS INT
DECLARE SUP_CUR CURSOR SCROLL DYNAMIC FOR
  SELECT *
  FROM   @saleSup
OPEN SUP_CUR
-- Perform the first fetch.
FETCH NEXT FROM SUP_CUR INTO @SUP_TEMP
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
  BEGIN
      SELECT Count (DISTINCT saleRepId),
             userId
      FROM   @tabletemp
      WHERE  userId = @SUP_TEMP
      GROUP  BY userId
      -- This is executed as long as the previous fetch succeeds.
      FETCH NEXT FROM SUP_CUR INTO @SUP_TEMP
  END
CLOSE SUP_CUR;
DEALLOCATE SUP_CUR; 

Answer 3

现在我知道它有什么问题了。 我只是把

FETCH NEXT FROM SUP_CUR INTO @SUP_TEMP

在开始循环之前，并在循环中将 FETCH 移动到下面的选择语句旁边，并且可以正常工作

Answer 4

我认为您不需要使用光标（无论如何它们都是不好的做法），请尝试：

SELECT COUNT (DISTINCT saleRepId),userId   
FROM @tabletemp 
GROUP BY userId