Fetch history of records

问题:

I have the table with the two columns RequestId and OldRequestId.

• If I pass a RequestId, it should retrieve me the specific record.

• If the OldRequestId is not null in the retrieved record, it should bring the old request data as well.

• It should go on until the OldRequestId is null.

Can someone help me to write the best possible SQL query for this requirement?

回答1:

You can solve this using a recursive Common Table Expression (CTE):

DECLARE
   @RequestID int = 6;

WITH
   ReqCTE AS
      (
         SELECT
               RequestID,
               OldRequestID
            FROM
               Requests
            WHERE
               RequestID = @RequestID
         UNION ALL SELECT
               R.RequestID,
               R.OldRequestID
            FROM
               ReqCTE C
               INNER JOIN Requests R
                  ON R.RequestID = C.OldRequestID
      )
SELECT
      *
   FROM
      ReqCTE;

回答2:

The answer depends on how deep you want to link your old requests to their replacements. If it is just one level, you can make a simple LEFT JOIN:

SELECT  
  n.RequestID,
  COALESCE(r.RequestID, n.RequestID) AS CurrentRequestID,
  n.OldRequestID
FROM
  Requests n LEFT JOIN 
  Requests r ON n.RequestID = r.OldRequestID
WHERE 6 IN (r.RequestID, n.RequestID) 

As you want to link old requests to the newest you need to do it recursively with CTE:

WITH CurrentRequests AS (
  SELECT
    id,
    RequestID,
    OldRequestID ,
    RequestID as TopRequestID
  FROM
    Requests
  UNION ALL SELECT
    R.id,
    R.RequestID,
    R.OldRequestID, 
    C.TopRequestID as TopRequestID
  FROM
    CurrentRequests C
    INNER JOIN Requests R
      ON R.RequestID = C.OldRequestID
)
SELECT *
FROM
 CurrentRequests 
WHERE TopRequestID  = 6;