请将此光标转换为CTE

Question

我正在用Cursor从表中获取Objectid。我需要同样的使用CTE或任何其他方式。

DECLARE  @TagID int,@ObjectID int,@ObjectTypeID int ,@UserAccountID int
DECLARE curs CURSOR FOR
  SELECT distinct TagID,ObjectID,ObjectTypeID,UserAccountID  FROM   taglink  WHERE  TagID in(1000528,1000482)      
    AND ObjectTypeID =5  and UserAccountID=1036144
OPEN curs
FETCH next FROM curs INTO @TagID, @ObjectId, @ObjectTypeID, @UserAccountID
CREATE TABLE #tagids  (TaglinkId INT,     TagId     INT ,ModifiedDate datetime )
WHILE @@FETCH_STATUS = 0
  BEGIN
      INSERT INTO #tagids(TaglinkId,TagId,ModifiedDate)
      SELECT TaglinkId,TagId,ModifiedDate      FROM   taglink      WHERE  UserAccountID = @UserAccountID   AND ObjectTypeID = @ObjectTypeID AND ObjectId = @ObjectId 
            and TagID not in (1000528,1000483,1000482) 
         and    TagID not in (select  TagId from #tagids)
            order by ModifiedDate desc
      FETCH next FROM curs INTO @TagID, @ObjectId, @ObjectTypeID, @UserAccountID
  END
SELECT * FROM   #tagids ORDER BY ModifiedDate DESC
DROP TABLE #tagids
CLOSE curs
DEALLOCATE curs   

Answer 1

您可以使用JOIN消除cursor。试试这个：

;WITH CTE AS(
    SELECT DISTINCT 
        TagId,
        ObjectID,
        ObjectTypeID,
        UserAccountID
    FROM taglink
    WHERE
        TagID IN(1000528,1000482)
        AND ObjectTypeID = 5
        AND UserAccountID = 1036144
)
INSERT INTO #tagids(TagLinkId, TagId, ModifiedDate)
SELECT
    tl.TagLinkId,
    tl.TagId,
    tl.ModifiedDate
FROM tagLink tl
INNER JOIN CTE c
    ON c.UserAccountID = tl.UserAccountID
    AND c.ObjectTypeID = tl.ObjectTypeID
    AND c.ObjectID = tl.ObjectID
WHERE
    tl.TagID NOT IN(1000528,1000483,1000482)
    AND NOT EXISTS(
        SELECT TagId FROM #tagids WHERE TagId = tl.TagId
    )
ORDER BY tl.ModifiedDate DESC