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

Question

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

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

Answer 2

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