rownum不能这么用。rownum是对得到的结果集进行筛选,而子查询中的max(rownum)为3,满足你的SQL语句的集合只可能有1条记录,而你却要取3条(rownum=3),当然返回空了。select * from (select rownum,*,... from table1 where xh='0001' order by rownum desc) a where rownum<2;一样可以达到你要的结果。
select * from (select a.*,row_number() over(partition by xh order by rownum desc) rm from table1) where rm=1;
(select a.*,row_number() over(partition by xh order by rownum desc) rm from table1)
where rm=1;