Oracle select *和rownum

Question

我试图从一个表中选择所有值+在按字母顺序排序后为每一行添加行行。(使用java，这是查询)

Select * FROM
  (Select *, rownum as row_id from
          (Select * FROM emp ORDER BY ename))
                Where row_id>=((?-1)*?)+1 and row_id<=(?*?);

然而我得到了

java.sql.SQLSyntaxErrorException: ORA-00923: FROM keyword not found where expected

我相信这是因为Select *, rownum as row_id from，选择所有字段和添加新字段的正确方法是什么？

Answer 1

当您将* (在Oracle中)与任何其他列一起使用时，您需要对它进行限定。所以，试试这个：

Select e.*
FROM (Select e.*, rownum as row_id
      from (Select *
            FROM emp
            ORDER BY ename
           ) e
     ) e
Where row_id >= ((?-1)*?)+1 and row_id <= (?*?);