leetcode中等题

1.

-- 第一种 别忘了DISTINCT
SET @limit_n = 4;
SELECT DISTINCT salary  
  FROM Employee e1
 WHERE (
             SELECT COUNT(DISTINCT salary)
               FROM Employee e2
              WHERE e2.salary >= e1.salary) = @limit_n;

--- 第二种

SET @limit_n2 = 4-1;

SELECT salary  
  FROM Employee e1
 ORDER BY salary DESC
 LIMIT @limit_n2,1; # 第4-1+1行，取1行

 2.

SELECT DISTINCT l1.Num AS ConsecutiveNums
  FROM Logs l1
  JOIN Logs l2
    ON l1.id = l2.id-1
   AND l1.Num = l2.Num
  JOIN Logs l3
    ON l1.id = l3.id-2
   AND l2.Num = l3.Num;

/*
SELECT DISTINCT l1.Num AS ConsecutiveNums
  FROM Logs l1
  JOIN Logs l2
    ON l1.id = l2.id-1
  JOIN Logs l3
    ON l1.id = l3.id-2
 WHERE l1.Num = l2.Num
   AND l2.Num = l3.Num;
   */

别忘了DISTINCT

其实我一开始写的l2.id = l3.id -1不知道为什么通不过

改成l1.id = l3.id-2就行了 很迷？？

3.

/*
SELECT d.Name AS Department,
       e.Name AS Employee,
       e.salary AS salary
  FROM Employee AS e
  JOIN Department AS d
    ON e.DepartmentId = d.id
 WHERE e.salary IN (SELECT MAX(salary)
                     FROM Employee 
                    GROUP BY DepartmentId);
  */                  
-- 有bug上面的，因为你最后子句选择了是每个部门的最高工资，但万一两个最高部门工资一样，
-- 那我返回肯定重复啊
SELECT d.Name AS Department,
       e.Name AS Employee,
       e.salary AS salary
  FROM Employee AS e
  JOIN Department AS d
    ON e.DepartmentId = d.id
 WHERE e.salary IN (SELECT MAX(salary)
                     FROM Employee e2
                    WHERE  e.DepartmentId = e2.DepartmentId);