LeetCode 刷提记录-SQL

177. Nth Highest Salary

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  DECLARE m INT; #声明局部变量
  SET m = n-1; #赋值，因为LIMIT 后面的参数不能是一个表达式
  RETURN (
      # Write your MySQL query statement below.
      SELECT DISTINCT salary 
      FROM employee
      ORDER BY salary DESC
      LIMIT m,1
  );
END

因为这个题目需要完成的只是定义函数，所以可以不用IFNULL()或者COALESCE()来判断返回值是不是非空，如果返回空值，在调用函数时再解决

178. Rank Scores

# Write your MySQL query statement below
SELECT c.score AS score, r AS rank 
FROM scores c JOIN (SELECT a.score AS score, @i:=@i+1 AS r
                    FROM (SELECT DISTINCT score FROM scores) AS a,(SELECT @i:=0) AS b
                    ORDER BY a.score DESC) AS t 
              ON c.score = t.score
ORDER BY c.score DESC;

思路是先把去重后的成绩排序生成新表t，然后将原始表c 和新表t 根据对应成绩联接，则相同的成绩就会有相同的排序

注意生成排序的方法是定义了一个自增的变量，然后用select 赋值为0（select 赋值时要用 :=）

其他方法：（比较常见的方法）

# Write your MySQL query statement below
SELECT a.score AS score, COUNT(DISTINCT b.score) AS rank
FROM scores a,scores b
WHERE a.score<=b.score
GROUP BY a.id,a.score
ORDER BY a.score DESC

重点是a.score<=b.score这里，通过计算去重后的大于等于a.score的数据的个数来给a.score排序

180. Consecutive Numbers

SELECT c.num AS ConsecutiveNums
FROM logs a JOIN logs b ON b.id = (a.id+1) AND b.num = a.num
JOIN logs c ON c.id = (b.id+1) AND c.num = b.num
GROUP BY c.num

注意方法

184. Department Highest Salary

SELECT d.name AS department, e.name AS employee, e.salary AS salary
FROM employee e JOIN department d ON e.departmentid = d.id
WHERE e.salary IN (SELECT MAX(a.salary)
                   FROM employee a 
                   WHERE a.departmentid = e.departmentid
                   )

注意不能直接查找 d.name,e.name,max(salary).......group by d.name，因为group by 只返回分组后的一条数据，如果这样做，会默认取查询到的第一个e.name，而不是max(salary) 对应的 e.name

185. Department Top Three Salaries

# Write your MySQL query statement below
SELECT d.name AS department, e.name AS employee, e.salary AS salary
FROM employee e JOIN department d ON e.departmentid = d.id
WHERE e.salary >=COALESCE ((SELECT DISTINCT a.salary
                  FROM employee a
                 WHERE e.departmentid = a.departmentid
                  ORDER BY a.salary DESC
                  LIMIT 2,1),0) # MySQL中 IN 和 LIMIT 不能连用
ORDER BY d.id,e.salary DESC

思路是找出每个部门排第三的薪水对应的记录，然后查找>=这个薪水的记录

注意MySQL中，LIMIT和IN 不能连用，但是和比较运算符可以一块用，此外在一些问题中还可以用建个临时表的方法来解决LIMIT和IN不能连用的问题