在不使用分析功能的情况下实现排名 [英] Implement Rank without using analytic function

问题描述

我想知道是否有一种方法可以在不使用内置函数的情况下实现SQL分析功能.

I am wondering if there is a method to implement SQL analytic functions without using the inbuilt functions.

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;

推荐答案

以下是三个等效表达式:

Here are the three equivalent expressions:

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"的行唯一.

This assumes the existence of an emp_id to make the rows unique for "row_number".

这篇关于在不使用分析功能的情况下实现排名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！