如何在沒有臨時表的情況下使用SQL游標跳過一行

我在 SQL 中有一個大表，其中包含以下列：

CompanyId 可以重復多次，因為公司會附加到各種電子郵件中。

我的雇主希望我找到屬于特定型別的公司電子郵件。在幾個 IF 陳述句之后，最終結果將列印如下訊息：

“CompanyId”具有 [email protected] 型別的電子郵件

“CompanyId”具有 [email protected] 型別的電子郵件

我的雇主告訴我使用 Cursor 來找到我的解決方案，但是一旦我啟動了我的游標，我需要檢查 CompanyId 以查看該 ID 是否已經回圈并找到了型別。如果 CompanyId 已經通過回圈，我想跳過它。

到目前為止，這是我的代碼

      DECLARE @CoId INT
            ,@Email VARCHAR(255)
            ,@FName varchar(50)
            ,@LName varchar(50)
        Declare @condition bit = 1 
    
    Declare CoCursor CURSOR
    For SELECT E.CompanyID, E.Email, P.First_Name, P.Last_Name 
    From Table_Email as E
    Left Join Table_People as P
    ON E.EmployeeID = P.EmployeeID
    Order by CompanyID, Email
    
    -- Loop through the rows with Cursors for CompanyId and Email
    
    OPEN CoCursor
        Fetch NEXT FROM CoCursor
            INTO @CoId, @Email, @FName, @LName
    
        While @@FETCH_STATUS = 0
            BEGIN
            -- Check to see if CompanyId has been logged before, if not, proceed
                -- Check to see if email matches criteria
                IF @Email LIKE @FName   '[_]'   @LName   '@%'
                    BEGIN
                -- If yes, check next email where CompanyIds match
                    ;WITH EmailTable(CompanyID, Email, First_Name, Last_Name) AS (
                         SELECT E.CompanyID, E.Email, P.First_Name, P.Last_Name 
                         From Table_Emails as E
                         Left Table_People as P
                         ON E.EmployeeID = P.EmployeeID
                         Where E.CompanyID = @CoId 
                         And E.Email LIKE P.First_Name   '[_]'   P.Last_Name   '@%')
                    Select TOP(2) @condition = 0
                    FROM EmailTable         
                    Having COUNT(*) > 1
                    -- If email matches previous email, log Company Id with the email type
                    IF @condition = 0
                    PRINT CONVERT(VARCHAR(50), @CoId)   ' has email like [email protected]' 
                -- If not, go to next Email check                           
                    END
                IF @Email LIKE LEFT(@FName,1)   @LName   '@%'
                -- If yes, check next email where CompanyIds match
                -- If email matches previous email, log Company Id with the email type
        -- If it has been logged move onto next record.
            Fetch NEXT FROM CoCursor
                INTO @CoId, @Email, @FName, @LName
        -- Once the result is logged go to next row 
            END         
    CLOSE CoCursor
    DEALLOCATE CoCursor

我看到的一個解決方案使用臨時表來記錄以前使用過的 ID，但我的雇主不希望我使用臨時表。 如何根據某些條件跳過 MSSQL Cursor 中的一行（迭代）？

謝謝！

uj5u.com熱心網友回復：

游標速度慢且效率低下，很少需要。

這根本不需要游標。一個簡單的過濾連接group by就足夠了

SELECT
  CONCAT(e.CompanyID), ' has email like [email protected]')
FROM Table_Email e
JOIN Table_People p ON p.EmployeeID = e.EmployeeID
WHERE e.Email LIKE p.First_Name   '[_]'   p.Last_Name   '@%'
GROUP BY
  e.CompanyID
ORDER BY
  e.CompanyID;

顯然，出于某種非常奇怪的原因，您的雇主強制要求使用游標，我相信您會告訴他們所有不使用它們的原因。但無論如何，你去吧：

您的原始代碼仍然非常復雜，您可以通過使用上面的代碼作為SELECT游標來簡化它。

注意游標的區域變數的使用，這意味著你不必釋放它

DECLARE @CompanyID int;

DECLARE @crsr CURSOR;
SET @crsr = CURSOR FAST_FORWARD FOR
SELECT
  e.CompanyID
FROM Table_Email e
JOIN Table_People p ON p.EmployeeID = e.EmployeeID
WHERE e.Email LIKE p.First_Name   '[_]'   p.Last_Name   '@%'
GROUP BY
  e.CompanyID
ORDER BY
  e.CompanyID;

OPEN @crsr;

FETCH NEXT FROM @crsr
  INTO @CompanyID;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT CONCAT(e.CompanyID), ' has email like [email protected]');

    FETCH NEXT FROM @crsr
      INTO @CompanyID;
END;

CLOSE @crsr;

uj5u.com熱心網友回復：

我不太喜歡游標，所以我將提出另一種可能的解決方案（我認為這是一個更好的解決方案，盡管您的雇主出于某些奇怪的原因想要一個游標）。

您的樣本資料：

create table Table_Email (
    CompanyID int, 
    EmployeeID int,
    Email varchar(255)
)

create table Table_People (
    CompanyID int, 
    EmployeeID int,
    First_Name varchar(50),
    Last_Name varchar(50),
)

insert into Table_Email values (1, 1, '[email protected]')
insert into Table_Email values (2, 2, '[email protected]')
insert into Table_Email values (1, 3, '[email protected]')

insert into Table_People values (1, 1, 'Jim', 'Halpert')
insert into Table_People values (2, 2, 'Bob', 'Vance')
insert into Table_People values (1, 3, 'Michael', 'Scott')

我提出的查詢：

SELECT CONVERT(VARCHAR(50), CompanyID)   ' has email like [email protected]' FROM 
(
 SELECT E.CompanyID, E.Email, P.First_Name, P.Last_Name 
 From Table_Email as E Left Join Table_People as P 
 ON E.EmployeeID = P.EmployeeID 
 WHERE Email LIKE P.First_Name   '[_]'   P.Last_Name   '@%' 
) AS a
group by CompanyID
order by CompanyID

它回傳：

SQL小提琴：http ://sqlfiddle.com/#!18/4c30e8/1

uj5u.com熱心網友回復：

使游標中的 CompanyId 不同

Declare CoCursor CURSOR
For Select CompanyID, 
            Email, 
            First_Name, 
            Last_Name
FROM (SELECT E.CompanyID, 
            E.Email, 
            P.First_Name, 
            P.Last_Name, 
            ROW_NUMBER() OVER(PARTITION BY E.CompanyID ORDER BY E.Email ASC) as RN
From Table_Emails as E
Left Table_People as P
ON E.EmployeeID = P.EmployeeID) as T
Where RN = 1
Order by CompanyID

標籤：sql sql-server 光标

上一篇：SQL：根據相同的列值更新列值

下一篇：每組所需的最大行數