select
a.*
from
表 a
where
not exists(select 1
from 表
where
glh_ID<>a.glh_ID
and
decode(xc,'Y',4,'M',3,'W',2,'D',1)>decode(a.xc,'Y',4,'M',3,'W',2,'D',1)
or
(decode(xc,'Y',4,'M',3,'W',2,'D',1)=decode(a.xc,'Y',4,'M',3,'W',2,'D',1) and begin_dt>a.begin_dt))
and
not exists(select 1
from 表
where
glh_ID= a.glh_ID
and
decode(xc,'Y',4,'M',3,'W',2,'D',1)>decode(a.xc,'Y',4,'M',3,'W',2,'D',1)
or
(decode(xc,'Y',4,'M',3,'W',2,'D',1)=decode(a.xc,'Y',4,'M',3,'W',2,'D',1) and begin_dt>a.begin_dt))
a.*
from
表 a
where
not exists(select 1
from 表
where
glh_ID<>a.glh_ID
and
decode(xc,'Y',4,'M',3,'W',2,'D',1)>decode(a.xc,'Y',4,'M',3,'W',2,'D',1)
or
(decode(xc,'Y',4,'M',3,'W',2,'D',1)=decode(a.xc,'Y',4,'M',3,'W',2,'D',1) and begin_dt>a.begin_dt))
and
not exists(select 1
from 表
where
glh_ID= a.glh_ID
and
decode(xc,'Y',4,'M',3,'W',2,'D',1)>decode(a.xc,'Y',4,'M',3,'W',2,'D',1)
or
(decode(xc,'Y',4,'M',3,'W',2,'D',1)=decode(a.xc,'Y',4,'M',3,'W',2,'D',1) and begin_dt>a.begin_dt))
-----------------------------------------------------------------------------------------------------------------
delete from 表 a
where
exists(select 1
from 表
where
glh_ID<>a.glh_ID
and
decode(xc,'Y',4,'M',3,'W',2,'D',1)>decode(a.xc,'Y',4,'M',3,'W',2,'D',1)
or
(decode(xc,'Y',4,'M',3,'W',2,'D',1)=decode(a.xc,'Y',4,'M',3,'W',2,'D',1) and begin_dt>a.begin_dt))
or
exists(select 1
from 表
where
glh_ID= a.glh_ID
and
decode(xc,'Y',4,'M',3,'W',2,'D',1)>decode(a.xc,'Y',4,'M',3,'W',2,'D',1)
or
(decode(xc,'Y',4,'M',3,'W',2,'D',1)=decode(a.xc,'Y',4,'M',3,'W',2,'D',1) and begin_dt>a.begin_dt))
(case xc when '4' then 'Y'
when '3' then 'M'
when '2' then 'W'
when '1' then 'D'
else ' '
end) as xc , begin_dt, end_dt, JG
from (
select rownum,glh_ID, xc, begin_dt
from
(
select glh_ID, xc, begin_dt
(select glh_ID, max(xc) as xc
from
(
select glh_ID, (case xc when 'Y' then '4'
when 'M' then '3'
when 'W' then '2'
when 'D' then '1'
else '0'
end) as xc,
max(begin_dt) as begin_dt
from tablename
group by glh_ID, xc
)
group by glh_ID
) temp1,
(
select ID, EVT_OBJECT, glh_ID, (case xc when 'Y' then '4'
when 'M' then '3'
when 'W' then '2'
when 'D' then '1'
else '0'
end) as xc , begin_dt, end_dt, JG
from tablename
) temp2
where temp1.glh_ID=temp2.glh_ID and temp1.xc=temp2.xc
)
order by xc desc, begin_dt desc
)
where rownum=1
我的參考代碼如下:
--創建測試表和數據
SQL>Create table Test_Tbl(ID int,EVT_OBJECT varchar2(10),glh_ID varchar2(5),xc char(1),begin_dt date,end_dt date,JG int);
SQL> begin
insert into test_tbl values(1,'1000.001','AAAA','Y',to_date('2005-10-11','YYYY-MM-DD HH24:MI:SS'),to_date('2005-11-11','YYYY-MM-DD HH24:MI:SS'),1);
insert into test_tbl values(2,'1000.001','AAAA','M',to_date('2005-10-10','YYYY-MM-DD HH24:MI:SS'),to_date('2005-11-10','YYYY-MM-DD HH24:MI:SS'),1);
insert into test_tbl values(3,'1000.001','AAAA','W',to_date('2005-10-11','YYYY-MM-DD HH24:MI:SS'),to_date('2005-11-11','YYYY-MM-DD HH24:MI:SS'),7);
insert into test_tbl values(4,'1000.001','BBBB','Y',to_date('2005-10-10','YYYY-MM-DD HH24:MI:SS'),to_date('2005-11-10','YYYY-MM-DD HH24:MI:SS'),1);
end;--顯示插入後的數據
SQL>select * from Test_Tbl; ID EVT_OBJECT GLH_ID XC BEGIN_DT END_DT JG
---------- ---------- ----- -- -------------- -------------- ----------
1 1000.001 AAAA Y 11-10月-05 11-11月-05 1
2 1000.001 AAAA M 10-10月-05 10-11月-05 1
3 1000.001 AAAA W 11-10月-05 11-11月-05 7
5 1000.001 BBBB Y 10-10月-05 10-11月-05 1--大家可以看看上面的記錄,依樓主的要求,刪除重復記錄後得到的結果應該是:
ID EVT_OBJECT GLH_ID XC BEGIN_DT END_DT JG
1 1000.001 AAAA Y 11-10月-05 11-11月-05 1--現在我使用下面的語句進行刪除,看最後結果是否符合要求
--使用刪除語句
SQL>delete from Test_tbl t1
Where
NOT EXISTS(
select 1
from
(
select EVT_OBJECT,xc,begin_dt,rowid,
row_number() over(Partition by EVT_OBJECT ORDER BY decode(xc,'Y',4,'M',3,'W',2,'D',1) DESC) rn1
,row_number() over(Partition by EVT_OBJECT,xc ORDER BY begin_dt DESC) rn2
from Test_tbl
) t2
where t2.rn1=1 and t2.rn2=1
and t1.EVT_OBJECT=t2.EVT_OBJECT and t1.xc=t2.xc and t1.begin_dt=t2.begin_dt
and t1.rowid=t2.rowid
);
--再查看結果
SQL>select * from test_tbl; ID EVT_OBJECT GLH_I X BEGIN_DT END_DT JG
---------- ---------- ----- - -------------- -------------- ----------
1 1000.001 AAAA Y 11-10月-05 11-11月-05 1
--從上結果可以看出,則好符合要求了
Where
NOT EXISTS(
select 1
from
(
select EVT_OBJECT,xc,begin_dt,rowid,
row_number() over(Partition by EVT_OBJECT ORDER BY decode(xc,'Y',4,'M',3,'W',2,'D',1) DESC) rn1
,row_number() over(Partition by EVT_OBJECT,xc ORDER BY begin_dt DESC) rn2
from Test_tbl
) t2
where t2.rn1=1 and t2.rn2=1
and t1.rowid=t2.rowid
);
ID EVT_OBJECT glh_ID xc begin_dt end_dt JG
1 1000.001 AAAA Y 2005-10-11 2005-11-11 1
2 1000.001 AAAA M 2005-10-10 2005-11-10 1
3 1000.001 AAAA W 2005-10-11 2005-11-11 7
4 1000.001 BBBB Y 2005-10-10 2005-11-10 1去掉重复记录,条件如下:
1,如果glh_id相同,取xc=Y(Y>M>W>D)记录,如果xc相同,取begin_dt大的记录,
2,如果glh_id不相同,比较xc的大小(Y>M>W>D),如果xc相同,取begin_dt大的记录。
3,按条件上面记录最后留下ID=1的记录,其它记录都得删除了。
==================================================
按照楼主所说条件,1和2应该合并起来这么说:按照(xc,begin_dt)排列取较大者。而3却又没提EVT_OBJECT的事情//这么些是不是看着简单一些呢
delete table t1
where (ID)not in(select id from(select ID from table where id=t1.id order by xc DESC,begin_dt DESC) where rownum=1)
where (ID)not in(select id from(select ID from table where EVT_OBJECT=t1.EVT_OBJECT order by xc DESC,begin_dt DESC) where rownum=1)