用SQL实现排名的几种方式 

 1 DROP TABLE IF EXISTS `salaries`;
 2 CREATE TABLE `salaries`  (
 3   `emp_no` int NOT NULL,
 4   `salary` int NOT NULL,
 5   `from_date` date NOT NULL,
 6   `to_date` date NOT NULL,
 7   PRIMARY KEY (`emp_no`, `from_date`) USING BTREE
 8 ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
 9 
10 -- ----------------------------
11 -- Records of salaries
12 -- ----------------------------
13 INSERT INTO `salaries` VALUES (10001, 88958, '2002-06-22', '9999-01-01');
14 INSERT INTO `salaries` VALUES (10002, 72527, '2001-08-02', '9999-01-01');
15 INSERT INTO `salaries` VALUES (10003, 43311, '2001-12-01', '9999-01-01');
16 INSERT INTO `salaries` VALUES (10004, 72527, '2001-12-01', '9999-01-01');

1.牛客SQL练习（所有员工的薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_no升序排列）

-- SQL编程（不大懂）
    SELECT
    emp_no,
    salary,
    @rankno := @rankno + (
    @pre > ( @pre := salary )) AS t_rank 
FROM
    salaries,
    ( SELECT @rankno := 1, @pre := - 1 ) AS r 
ORDER BY
    salary DESC;
-- 开窗函数(注意对比row_number()函数)
SELECT
    emp_no,
    salary,
    dense_rank() over (ORDER BY salary desc ) t_rank 
FROM
    salaries 
ORDER BY
    t_rank,
    emp_no ASC

-- 自连接
SELECT
    s2.emp_no,
    s2.salary,
    COUNT( DISTINCT s1.salary ) t_rank 
FROM
    salaries s1,
    salaries s2 
WHERE
    s1.salary >= s2.salary 
GROUP BY
    s2.emp_no 
ORDER BY
    t_rank ,
    s2.emp_no ASC;

结果要求：

 

posted @ 2021-03-29 17:49  VVII  阅读(1206)  评论(0)    收藏  举报