mysql 排序序号sql+斐波那契统计

输入：

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

统计salary的累计和running_total

答案

-- 使用 sum( ) over() 处理
SELECT
	s.emp_no,
	s.salary,
	SUM(s.salary) OVER (ORDER BY s.emp_no) running_total
FROM
	salaries s
WHERE
	s.to_date = '9999-01-01'
GROUP BY
	s.emp_no;
-- 使用子查询
SELECT
	s.emp_no,
	s.salary,
	(SELECT sum(salary) FROM salaries s1 WHERE s1.emp_no <= s.emp_no AND s1.to_date = '9999-01-01') running_total 
FROM
	salaries s
WHERE
	s.to_date = '9999-01-01'
GROUP BY
	s.emp_no;