sql:关于ORA-00918: 未明确定义列的错误。求解答。

WHERE rownum <= 20 ) WHERE my_rownum >0)

报ORA-00918:

错误应该在这里，你在( SELECT * FROM ( SELECT A.*, rownum as my_rownum FROM 这里定义了my_rownum,但是实际用的时候你却用的是rownum，在最外层的sql中没有定义my_rownum结果在where中却使用了my_rownum

select rownum as rownum,t.*

from (select a.xm,sum(b.grjf) jf

from SZ_HD_XSCYQK b ,zx_xs_jbxx a

where b.xsdm=a.xsdm and b.xn='"+xn+"' and b.xq='"+xq+"' and b.xxdm='"+ssdwdm+"'

group by xsdm

order by jf desc) t

where rownum<11

不过,觉得应该这样写吧

select ROW_NUMBER()OVER(ORDER BY t.xm) as rownum,t.*

from (select a.xm,sum(b.grjf) jf

from SZ_HD_XSCYQK b ,zx_xs_jbxx a

where b.xsdm=a.xsdm and b.xn='"+xn+"' and b.xq='"+xq+"' and b.xxdm='"+ssdwdm+"'

group by xsdm

order by jf desc) t

where rownum<11

---