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);