递归地更新记录的oldID [英] Update oldID for the records recursively

问题描述

我问了一个非常相似的问题，这里<但是我现在需要更多的提前查询。情况是，我们有大约2万条客户记录。客户可以更新，我们只是为它创建一个新的记录。没有精确追溯到哪个记录实际上被更新。现在我们添加了 old ID 字段，并且要使用完整的旧记录填充它。现在如果它被更新一次，那已经照顾我的旧问题，我可以做到。问题是如果一个记录被更新了3次或更多的时间，我必须找到确切的逻辑如何最老的记录跳到最新的记录。为此，我们没有设置规则，但一般我在客户开始日期（和ID本身引发一些光，记录先来），现在将使用客户开始日期以填充所有记录。我在这里包含了一个测试用例

I asked a very similar question here but I need more advance query now. The situation is, we have about 20,000 records of customers. Customers can renew and we just create a new record for it. There is no exact tracing back to which record was actually renewed. Now we have added the old ID field and I want to populate it with the exact old record. Now if it is renewed one time, that has been taken care of my old question and I can do it. The problem is if a record was renewed 3 or more time, I have to find the exact logic how the oldest record hopped to the newest one. For this we do not have a set rule but generally I am following customer start date (and the ID itself throws some light which record came first) and for now will use customer start date to populate all the records. I have included a test case here

create table #customer (
id int not null primary key identity,
cust_no varchar(12),
meter_no varchar(10),
startdate smalldatetime,
enddate smalldatetime,
oldid int null
)

insert into #customer values('AA111222','1111','2008-01-01', '2008-03-01',null) 
insert into #customer values('AA111222','1111','2009-02-01', '2009-05-01',null) 
insert into #customer values('AA111222','1111','2008-03-01', '2008-12-01',null) 
insert into #customer values('AA111222','1111','2009-05-01', '2009-07-01',null) 
insert into #customer values('AA111222','1111','2009-08-01', '2009-11-01',null) 
insert into #customer values('AA111222','1111','2010-01-01', '2010-04-01',null) 
insert into #customer values('AA111222','1111','2010-07-01', '2011-07-01',null) 
insert into #customer values('AA111222','1111','2011-03-01', '2011-07-01',null) 
insert into #customer values('AA111222','1111','2011-07-01', '2012-07-01',null) 

-- I want this result in the last column

id   cust_no      meter_no   startdate      enddate        oldid           
---- ------------ ---------- -------------- -------------- -------         
1    AA111222     1111       2008-01-01     2008-03-01     base                      
2    AA111222     1111       2009-02-01     2009-05-01     3 
3    AA111222     1111       2008-03-01     2008-12-01     1            
4    AA111222     1111       2009-05-01     2009-07-01     2            
5    AA111222     1111       2009-08-01     2009-11-01     4            
6    AA111222     1111       2010-01-01     2010-04-01     5            
7    AA111222     1111       2010-07-01     2011-07-01     6            
8    AA111222     1111       2011-03-01     2011-07-01     7            
9    AA111222     1111       2011-07-01     2012-07-01     8    

注意，不同的做法是赞赏，所以我可以学习一些东西。到目前为止，我已经看过CTE，Join，Cursor，但是我需要一些时间来做，如果我能做到这一点。

Note, different ways of doing it is appreciated so I can learn something too. So far I have looked at CTE, Join, Cursor but it would take some time for me to do it, if I can do it in the first place.

推荐答案

第二个答案：
可以使用以下语句更新old_id列：

Second Answer: You can update the old_id column with the following statement!

 Update #customer
 SET oldid =
        (Select TOP 1 c_old.id from #customer c_old
          where c_old.enddate <= #customer.startdate
          and c_old.cust_no = #customer.cust_no
          and c_old.meter_no = #customer.meter_no
          and c_old.enddate = 
                           (
                             SELECT max(c.enddate) FROM #customer c
                               where c_old.cust_no = c.cust_no
                               and c_old.meter_no = c.meter_no
                               and #customer.startdate >= c.enddate
                            ) 
          )
  from #customer
 go

这篇关于递归地更新记录的oldID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！