sql - 内部查询对Oracle不起作用？

我有2个表table1和table2。我想从两个表中获取数据。查询是

SELECT tb2.tdn
     ,tb2.nrn
     ,tb2.id
     ,tb2.dat
     ,tb2.mcheck
     ,tb2.info
     ,tb2.edrf
     , (SELECT count(*)
        from table1 tb1
        where tb1.id = 'ftam'
        and tb1.tdn =  tb2.tdn
        and (tb1.display = 'Y' OR tb1.display = 'y') ) as history
from (select rownum rnum
             ,table2.*
      FROM  (SELECT *
             FROM table2
             WHERE id = 'ftam'
             and (display = 'Y' OR display = 'y')
             ORDER BY dat DESC ) table2 tb2
     where  rownum <= 50 )
WHERE rnum >  0

显示

SQL Error [907] [42000]: ORA-00907: missing right parenthesis
ORA-00907: missing right parenthesis

查询有什么问题？我已经在MySQL中实现了它，但是当我将查询更改为Oracle时，它显示了错误。

谢谢

最佳答案

为了清楚起见，更改发布布局显示此行是当前的问题:

ORDER BY dat DESC ) table2 tb2

您需要一个别名，而不是两个。所以应该是

ORDER BY dat DESC )  table2

您还需要在外部嵌套查询上放置一个别名:

(select rownum rnum
             ,table2.*
      FROM  (SELECT *
             FROM table2
             WHERE id = 'ftam'
             and (display = 'Y' OR display = 'y')
             ORDER BY dat DESC ) table2
     where  rownum <= 50 ) tb2