合并数据库中重复的时间记录 [英] Merge duplicate temporal records in database
本文介绍了合并数据库中重复的时间记录的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个时态数据库表,其中一些数据是重复的.
I have a temporal database table where some of the data is duplicated.
EmployeeId StartDate EndDate Column1 Column2
1000 2009/05/01 2010/04/30 X Y
1000 2010/05/01 2011/04/30 X Y
1000 2011/05/01 2012/04/30 X X
1000 2012/05/01 2013/04/30 X Y
1000 2013/05/01 NULL X X
如上所示,有些行是冗余的,可以合并为一行,而不会违反数据有效性.我想尽可能合并这些行,结果应该是这样的
As seen above there are some rows which are redundant and can be merged to form a single row without violating the data validity. I want to merge such rows wherever possible and the result should look like this
EmployeeId StartDate EndDate Column1 Column2
1000 2009/05/01 2011/04/30 X Y
1000 2011/05/01 2012/04/30 X X
1000 2012/05/01 2013/04/30 X Y
1000 2013/05/01 NULL X X
如何实现?
推荐答案
试试这个,如果你能确保所有的开始日期和结束日期都是连续的:
Try this if you can ensure that all the start date and end date are continuous :
with t1 as --tag first row with 1 in a continuous time series
(
select t1.*, case when t1.column1=t2.column1 and t1.column2=t2.column2
then 0 else 1 end as tag
from your_table t1
left join your_table t2
on t1.EmployeeId= t2.EmployeeId and dateadd(day,-1,t1.StartDate)= t2.EndDate
)
select t1.EmployeeId, t1.StartDate,
case when min(T2.StartDate) is null then null
else dateadd(day,-1,min(T2.StartDate)) end as EndDate,
t1.Column1, t1.Column2
from (select t1.* from t1 where tag=1 ) as t1 -- to get StartDate
left join (select t1.* from t1 where tag=1 ) as t2 -- to get a new EndDate
on t1.EmployeeId= t2.EmployeeId and t1.StartDate < t2.StartDate
group by t1.EmployeeId, t1.StartDate, t1.Column1, t1.Column2
这篇关于合并数据库中重复的时间记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文