不使用分析函数来实现排名

Question

我想知道是否有一种方法可以在不使用内置函数的情况下实现SQL分析函数。有人可以帮我弄这个吗。谢谢。

SELECT *, 
     ROW_NUMBER() OVER(PARTITION BY dept_id ORDER BY salary DESC) AS rownum, 
     DENSE_RANK() OVER(PARTITION BY dept_id ORDER BY salary DESC) AS denserank, 
     RANK() OVER(PARTITION BY dept_id ORDER BY salary DESC) AS rnk 
    FROM emp; 

Answer 1

您可以使用相关的子查询来做到这一点。

select dept_id,salary, 
(select count(*) from emp e1 where e1.dept_id=e.dept_id and e1.salary>=e.salary) as rnum 
from emp e 

在没有关系的情况下，这很有效。

Answer 2

这里有三个等价表达式：

select emp.*, 
     (select count(*) 
     from emp emp2 
     where emp2.dept_id = emp.dept_id and 
       (emp2.salary > emp.salary or 
       emp2.salary = emp.salary and emp2.emp_id <= emp.emp_id 
      ) 
     ) as "row_number", 
     (select 1 + count(*) 
     from emp emp2 
     where emp2.dept_id = emp.dept_id and 
       emp2.salary > emp.salary 
      ) 
     ) as "rank", 
     (select count(distinct salary) 
     from emp emp2 
     where emp2.dept_id = emp.dept_id and 
       emp2.salary >= emp.salary 
     ) as "dense_rank", 
from emp; 

这是假设一个emp_id的存在，使行唯一的“ROW_NUMBER”。