现在我想写一个sql查出来的结果是如果defined_code,
rtng_srno列值相同,且count>1就当第二列的efctv_from_date赋到新的一列efctv_end_date里面
最后一列的efctv_end_date赋null值。各位大侠有好的解决方案吗?
rtng_srno列值相同,且count>1就当第二列的efctv_from_date赋到新的一列efctv_end_date里面
最后一列的efctv_end_date赋null值。各位大侠有好的解决方案吗?
update t
set
t.efctv_end_date=t.efctv_from_date,
t.efctv_end_date=null
from tab t
where t.defined_code=t.rtng_srno and exists
(select 1 from tab t2
where t2.defined_code=t.defined_code and t2.rtng_srno=t.rtng_srno
group by t2.defined_code,t2.rtng_srno
having count(*)>1
)
原表显示是:
defined_code rtng_srno efctv_from_date
100000024 300000033 2010-09-08
100000024 300000033 2010-09-10100000025 300000034 2010-09-01
100000025 300000035 2010-09-02100000026 300000036 2010-09-08
100000026 300000036 2010-09-11
100000026 300000036 2010-09-12
100000026 300000037 2010-10-08 sql查询出的是
defined_code rtng_srno efctv_from_date efctv_from_date
100000024 300000033 2010-09-08 2010-09-10
100000024 300000033 2010-09-10 NULL100000025 300000034 2010-09-01 NULL
100000025 300000035 2010-09-02 NULL100000026 300000036 2010-09-08 2010-09-11
100000026 300000036 2010-09-11 2010-09-12
100000026 300000036 2010-09-12 NULL
100000026 300000037 2010-10-08 NULL