如何获得SQL中值的最后一个更新值

Question

示例数据我有2列old_store_id，change_new_store_id，并且在某些情况下，更改_new_store_id值也将更新为新值。我如何穿越db(teradata(以获取相应的old_store_id

的最后一个值(change_new_store_id(

让1在1号行中old_store_id = a;change_new_store_id = b

和第5行包含

old_store_id = b;change_new_store_id = c

和其他一些第n行C更改为x etc

如何获得x的最终值？

我可以尝试使用多个自我加入

使用存储过程，但这不是一种有效的方法(出于许多原因(有什么方法可以找到吗？请任何人建议我

Answer 1

这假定"循环"，并使用"自下而上"递归。可以"自上而下"完成非常类似的事情，将种子查询限制在"旧"值不会以"新"值出现在任何地方的行中。

CREATE VOLATILE TABLE #Example (
    Old_Store_ID VARCHAR(8),
    New_Store_ID VARCHAR(8)
)
PRIMARY INDEX(Old_Store_ID)
ON COMMIT PRESERVE ROWS;
INSERT INTO #Example VALUES ('A', 'B');
INSERT INTO #Example VALUES ('D', 'c');
INSERT INTO #Example VALUES ('B', 'F');
INSERT INTO #Example VALUES ('c', 'FF');
INSERT INTO #Example VALUES ('FF', 'GG');
INSERT INTO #Example VALUES ('F', 'X');
WITH RECURSIVE #Traverse(Old_Store_ID,New_Store_ID,Final_ID)
AS
(
--Seed Query - start with only the rows having no further changes
SELECT      Old_Store_ID 
            ,New_Store_ID 
            ,New_Store_ID as Final_ID
  FROM      #Example as This
  WHERE NOT EXISTS (
      SELECT 1 FROM #Example AS Other WHERE This.New_Store_ID = Other.Old_Store_ID
      )
UNION ALL
--Recursive Join
SELECT      NewRow.Old_Store_ID  
            ,NewRow.New_Store_ID
            ,OldRow.Final_ID
  FROM      #Example AS NewRow 
            INNER JOIN #Traverse AS OldRow
            ON NewRow.New_Store_ID = OldRow.Old_Store_ID 
)
SELECT *
  FROM      #Traverse
  ;

Answer 2

递归答案：

CREATE VOLATILE TABLE #SearchList (
    SearchID       CHAR(2),
    ParentSearchID CHAR(2)
)
PRIMARY INDEX(SearchID)
ON COMMIT PRESERVE ROWS;
INSERT INTO #SearchList VALUES ('A', 'B');
INSERT INTO #SearchList VALUES ('D', 'c');
INSERT INTO #SearchList VALUES ('B', 'F');
INSERT INTO #SearchList VALUES ('c', 'FF');
INSERT INTO #SearchList VALUES ('FF', 'GG');
INSERT INTO #SearchList VALUES ('F', 'X');
CREATE VOLATILE TABLE #IntermediateResults( 
    SearchID       CHAR(2),
    ParentSearchID CHAR(2),
    SearchLevel    INTEGER
)
ON COMMIT PRESERVE ROWS;
INSERT INTO #IntermediateResults
WITH RECURSIVE RecursiveParent(SearchID,ParentSearchID,SearchLevel)
AS
(
--Seed Query
SELECT      SearchID 
            ,ParentSearchID 
            ,1
  FROM      #SearchList
UNION ALL
--Recursive Join
SELECT      a.SearchID  
            ,b.ParentSearchID
            ,SearchLevel+1
  FROM      #SearchList a 
            INNER JOIN RecursiveParent b
            ON a.ParentSearchID = b.SearchID 
)
SELECT      SearchID
            ,ParentSearchID
            ,MAX(SearchLevel)
  FROM      RecursiveParent
GROUP BY    SearchID
            ,ParentSearchID
;
SELECT      RESULTS.*
  FROM      #IntermediateResults RESULTS
            INNER JOIN (SELECT      RESULTS_MAX.SearchID
                                    ,MAX(RESULTS_MAX.SearchLevel) MaxSearchLevel
                          FROM      #IntermediateResults RESULTS_MAX
                        GROUP BY    RESULTS_MAX.SearchID
                       ) GROUPED_RESULTS
            ON RESULTS.SearchID = GROUPED_RESULTS.SearchID
            AND RESULTS.SearchLevel = GROUPED_RESULTS.MaxSearchLevel
ORDER BY     RESULTS.SearchID    ASC
            ,RESULTS.SearchLevel ASC
;

输出：

SearchID  ParentSearchID  SearchLevel
--------  --------------  -----------
A         X               3
B         X               2
c         GG              2
D         GG              3
F         X               1
FF        GG              1