select * from topmanager a where a.Dir001 in (select dir001 from (select Dir001 from topmanager where F0001=a.F0001 and length(Dir001)>0 and length(Dir001)>0 and Dir001 is not NULL order by F0002 desc) where rownum=1)
ERROR 位于第 1 行: ORA-00904: 无效列名 我都说了,直接修改top为rownum的句式我试过,会出错的,大概是order by 那句外加了括号,里面的对a表的引用无效,不知对不对。desc topmanager DA NOT NULL DATE TM NOT NULL CHAR(5) SEQ NOT NULL NUMBER F0001 NOT NULL CHAR(8) F0002 NOT NULL DATE DIR001 VARCHAR2(20) 其他字段用不到,我就不列了!
我根据你所描述的所需结果集,重写了语句如下,你试一下:select a.* from topmanager a, (select F0001, max(F0002) F0002 from topmanager group by F0001) b where a.F0001 = b.F0001 and a.F0002 = b.F0002
受wenzhulz(触丝)的启发,我也改成两表的连接格式,大概可以了!如下,欢迎大家评论。我今天会结帖的,当然分会给wenzhulz(触丝),因为其他的帖我大概都试过,且对有问题的句法我在发帖时就有说明,这些帖就不给分了。select a.* from T0001004 a,(select distinct F0001,Dir001 from (select F0001,F0002,Dir001,seq,rank() over (partition by F0001 order by F0002 desc) ranknum from T0001004 where Dir001 is not null and length(rtrim(Dir001))<>0) where ranknum<=1) b where a.F0001=b.F0001 and a.Dir001=b.Dir001 and a.F0001='01000002' order by a.F0001,a.F0002
ORA-00904: 无效列名
我都说了,直接修改top为rownum的句式我试过,会出错的,大概是order by 那句外加了括号,里面的对a表的引用无效,不知对不对。desc topmanager DA NOT NULL DATE
TM NOT NULL CHAR(5)
SEQ NOT NULL NUMBER
F0001 NOT NULL CHAR(8)
F0002 NOT NULL DATE
DIR001 VARCHAR2(20)
其他字段用不到,我就不列了!
个人一点看法,如有不对,请高人指教!
where a.F0001 = b.F0001 and a.F0002 = b.F0002