23

题目：对所有员工的当前(to_date=‘9999-01-01')薪水按照salary进行按照1-N的排名，相同的salary并列且按照emp_no升序排列

错误示范：虽然在题库中能通过，但是在mysql中无法通过，因为s1.salary不是可聚合项
SELECT s1.emp_no, s1.salary, COUNT(DISTINCT s2.salary) AS rank
FROM salaries AS s1, salaries AS s2
WHERE s1.to_date='9999-01-01'
AND s2.to_date='9999-01-01'
AND s1.salary<=s2.salary
GROUP BY s1.emp_no
ORDER BY rank ASC, s1.emp_no ASC;
注意：这里emp_no是聚合项，但不是主键，只是联合主键之一，所以不能唯一确定salary，所以salary实际上是不可聚合的，所以不能放入SELECT。
补充：如果上面的代码中，我们能在WHERE子句中加入s1.from_date的确定性约束条件(如s1.from_date=‘9999-01-01’)，这样s1.emp_no确定的时候，s1.salary也就确定了。此时就可以认为s1.salary是可聚合的。

错误示范：COUNT()是错误的，因为对s1.salary<=s2.salary，重复的s2.salary应该只算一个
SELECT a.emp_no, a.salary, b.rank
FROM salaries AS a
INNER JOIN(SELECT s1.emp_no, COUNT() AS rank
FROM salaries AS s1, salaries AS s2
WHERE s1.to_date='9999-01-01'
AND s2.to_date='9999-01-01'
AND s1.salary<=s2.salary
GROUP BY s1.emp_no) AS b
ON a.emp_no=b.emp_no
AND a.to_date='9999-01-01'
ORDER BY a.salary DESC, a.emp_no ASC;

方法1：先构建不含salary的rank表，再将rank表和salaries表内接，然后排序得到结果
SELECT a.emp_no, a.salary, b.rank
FROM salaries AS a
INNER JOIN(SELECT s1.emp_no, COUNT(DISTINCT s2.salary) AS rank
FROM salaries AS s1, salaries AS s2
WHERE s1.to_date='9999-01-01'
AND s2.to_date='9999-01-01'
AND s1.salary<=s2.salary
GROUP BY s1.emp_no) AS b
ON a.emp_no=b.emp_no
AND a.to_date='9999-01-01'
ORDER BY a.salary DESC, a.emp_no ASC;

方法2：固定s1的一条记录，利用关联查询的到它的rank
SELECT s1.emp_no, s1.salary,
(SELECT COUNT(DISTINCT s2.salary)
FROM salaries AS s2
WHERE s2.to_date='9999-01-01'
AND s2.salary>=s1.salary
) AS rank
FROM salaries AS s1
WHERE s1.to_date='9999-01-01'
ORDER BY s1.salary DESC, s1.emp_no ASC

方法3：SQL编程，在题库中不能运行，在mysql中可以运行
SELECT emp_no, salary,
@rankno := @rankno + (@pre <> (@pre := salary)) AS rank
FROM salaries, (SELECT @rankno := 0, @pre := -1) AS r
WHERE to_date = '9999-01-01'
ORDER BY salary DESC;
解析：变量使用前要加@，:=表示赋值，(SELECT @rankno := 0, @pre := -1) r是进行初始化，r是推导表的别名，
@rankno代表排名，@pre代表工资，@rankno := @rankno + (@pre <> (@pre := salary)) rank是推导公式，rank是别名
@pre <> (@pre := salary)的执行顺序是：
@pre是上一次的值
@pre:=salary是进行新一次赋值给pre
判断<>，如果左右不想等，则返回1，否则(相等)返回0
SQL编程没找到更多的资料，所以只能就题论题了，不建议在不熟悉的情况下使用。可以作为了解。