如果不用dense_rank() over() 如何实现分组排序

1.在不用dense_rank() over() 的情况下实现，按EmployeeID分组，Income降序排序

数据表如下：

1.使用dense_rank() 函数实现效果如下：

2. 不使用dense_rank()

①使用自连接，只保留a 表的income小于等于b 表的income ，使得在后面按employeeid,a.income分组去重b.income,是累减的，即最小的a.income会对应所有的b.income,最大的a.income 只有一个b.income

2. 去重统计b.income 的数量

3.最后以原表为主表，左连接t 表实现效果

附代码：

CREATE TABLE Salary (  
    EmployeeID char(6) NOT NULL,  
    Income float NOT NULL)

INSERT INTO Salary (EmployeeID, InCome) VALUES  
('000001', 2100.8),  
('000001', 2100.8),  
('000001', 2123.8), 
('000001', 2125.8), 
('000001', 2125.8),   
('102201', 2569.88),  
('102201', 2568.88),  
('102201', 2567.88);

select EmployeeID,
InCome,
DENSE_RANK() over (partition by EmployeeID order by InCome desc) point_rank
from Salary;
	
with t as (		
SELECT
    a.EmployeeID,
    a.InCome,
    COUNT(DISTINCT b.InCome) AS point_rank	
FROM
    Salary a
JOIN
    Salary b ON a.EmployeeID=b.EmployeeID and a.InCome <= b.InCome
GROUP BY
    a.EmployeeID, a.InCome
ORDER BY
    a.EmployeeID) 
select a.EmployeeID,a.InCome,t.point_rank FROM  
    Salary a left join t on a.EmployeeID=t.EmployeeID and a.InCome=t.InCome 	
		order by a.EmployeeID,point_rank