177.Nth Highest Salary

image.png

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
DECLARE M INT;
SET M=N-1;
  RETURN (
      # Write your MySQL query statement below.
      SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT M, 1
  );
END

178.Rank Scores

Write a SQL query to rank scores. If there is a tie between two scores, both should have the same ranking. Note that after a tie, the next ranking number should be the next consecutive integer value. In other words, there should be no "holes" between ranks.

image.png

SELECT
  Score,
  (SELECT count(*) FROM (SELECT distinct Score s FROM Scores) tmp WHERE s >= Score) Rank
FROM Scores
ORDER BY Score desc;

在学了window function之后，这个应该还可以用window function解决

select score, dense_rank() over (order by score desc) as rank
from scores

但是不知道为什么leetcode的mysql不能通过，只有ms sql可以用

180.Consecutive Numbers

Write a SQL query to find all numbers that appear at least three times consecutively.

image.png

Select DISTINCT l1.Num as ConsecutiveNums from Logs l1, Logs l2, Logs l3 
where l1.Id=l2.Id-1 and l2.Id=l3.Id-1 
and l1.Num=l2.Num and l2.Num=l3.Num;

184.Department Highest Salary

image.png

windows function 版本
(Select e.name as employee, e.salary as salary,d.name as department ,
dense_rank() over(partition by departmentID order by e.salary desc) as rank
from employee as e
inner join department as d on e.departmentid = d.id) a
where rank = 1;

SELECT d.Name AS Department, e1.Name AS Employee, e1.Salary FROM Employee e1
JOIN Department d ON e1.DepartmentId = d.Id WHERE Salary IN 
(SELECT MAX(Salary) FROM Employee e2 WHERE e1.DepartmentId = e2.DepartmentId);

626.Exchange Seats

Mary is a teacher in a middle school and she has a table seat storing students' names and their corresponding seat ids.
The column id is continuous increment.
Mary wants to change seats for the adjacent students.
Can you write a SQL query to output the result for Mary?

image.png

select
if(id < (select count(*) from seat), if(id mod 2=0, id-1, id+1), if(id mod 2=0, id-1, id)) as id, student
from seat
order by id asc;