我想我需要重新表述信息:(我不知道如何为此编写代码)
表名:test1
下面是我正在寻找的例子示例1
初始表名称Ref开始日期结束日期5001 klm123 01/01/2011 31/05/20125001 klm111 01/06/2012 31/12/20125001 klm112 01/01/2013 31/12/20135001 KLM112 01/01/2014 31/12/2014
最终表名称Ref开始日期结束日期5001 klm123 01/01/2011 31/05/20125001 klm111 01/06/2012 31/12/20125001 KLM112 01/01/2013 31/12/2014(记录3和4被压缩成一个给定的开始日期和结束日期,因为Name和Ref在此期间保持不变)
示例2初始表名称Ref开始日期结束日期5001 klm123 01/01/2011 31/05/20125001 klm111 01/06/2012 31/12/20125001 klm112 01/01/2013 31/12/20135001 klm112 01/01/2014 12/12/20145001 klm134 01/01/2015 30/06/20155001 klm133 01/07/2015 31/12/20155002 klm144 01/01/2013 31/12/20135002 KLM155 01/01/2014 12/12/2014
最终表名称Ref开始日期结束日期5001 klm123 01/01/2011 31/05/20125001 klm111 01/06/2012 31/12/20125001 klm112 01/01/2013 31/12/20145001 klm134 01/01/2015 30/06/20155001 klm133 01/07/2015 31/12/20155002 klm144 01/01/2013 31/12/20135002 KLM155 01/01/2014 12/12/2014
希望将"Final table"作为另一个表。那么在PL/SQL中做到这一点的最佳方法是什么呢?
感谢
With CTE as
(Select ID, name, Ref, Start_date, max(End_date)
from test1 group by ID, Name, Ref, Start_date)
Select T1.ID, T1.Name, T1.Ref, T1.Start_date, T1.End_date
From CTE t1
把这个改成insert,我想它会满足你的要求…
我认为您需要基于ID, Name, Ref和开始日期的test1的唯一记录。如果没有唯一值,则需要最大结束日期