选择oracle的行号和行数

Question

我正在尝试选择分页记录，我需要记录的总数，以便我可以在UI上显示记录和页面的数量。

我使用的查询如下，但它总是返回总计数为1。

WITH cteEmp AS 
(SELECT e.empid, e.empname, d.deptid, d.deptname
FROM hr.Emp e
INNER JOIN hr.dept d ON e.deptid = d.deptid)
Select * from (SELECT row_number() over (order by hr.empid desc) rn, Count(*) totalcount, 
C.empName FROM CTEPO C
LEFT JOIN hr.emphistory ON C.empid=hr.empid
GROUP BY c.empid,hr.empid)  where rn>0 and rn<= 100

Answer 1

你可以试试这个，也许它会为你工作:

(SELECT e.empid, e.empname, d.deptid, d.deptname
FROM hr.Emp e
INNER JOIN hr.dept d ON e.deptid = d.deptid)
Select * from (SELECT row_number() over (order by hr.empid desc) rn,
count(*) OVER (ORDER BY hr.empid desc ) AS totalcount
C.empName FROM CTEPO C
LEFT JOIN hr.emphistory ON C.empid=hr.empid
GROUP BY c.empid,hr.empid)  where rn>0 and rn<= 100