题目:对所有员工的当前(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编程没找到更多的资料,所以只能就题论题了,不建议在不熟悉的情况下使用。可以作为了解。