问题真难，高手来帮助一下吧！送1000分都可以。

[hejiwang]

表结构如下
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的记录，其它记录都得删除了。

[子陌红尘]

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))

[子陌红尘]

删除操作:
-----------------------------------------------------------------------------------------------------------------
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))

[zhpsam109]

牛人，学习！

[wangzk0206]

佩服．．．．

[xjqqxjqq]

高手阿!