本文主要是介绍mysql 排序序号sql,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
![image](/images/baidian.png)
输入:
drop table if exists `salaries` ;
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,72527,'2001-12-01','9999-01-01');
输出:
10001|88958|1
10002|72527|2
10004|72527|2
10003|43311|3
答案
-- 成绩一样,排名看 over()里面的,不存在并列的情况
SELECT s.emp_no,s.salary,ROW_NUMBER() over(ORDER BY s.salary DESC,s.emp_no desc) FROM salaries s;
-- 前四名有两个并列第二,就没有第三名。1,2,2,4
SELECT s.emp_no,s.salary,IFNULL(temp.ranking,0)+1 FROM salaries s LEFT JOIN
(SELECT s1.emp_no,count(s2.salary) ranking FROM salaries s1,salaries s2 WHERE s1.salary <s2.salary GROUP BY s1.emp_no) temp
ON s.emp_no = temp.emp_no
ORDER BY IFNULL(temp.ranking,1)+1 asc
;
-- 前四名有两个并列第二,就没有第四名。1,2,2,3
SELECT s.emp_no,s.salary,temp.ranking FROM salaries s INNER JOIN -- 简单关联查询排序得到结果
(
SELECT t.salary,ROW_NUMBER() over(ORDER BY t.salary desc) ranking -- 以去重的成绩结果进行排名
FROM
(SELECT DISTINCT s.salary FROM salaries s) t -- 成绩去重
) temp
ON s.salary = temp.salary
ORDER BY temp.ranking ASC,s.emp_no ASC
这篇关于mysql 排序序号sql的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!