sql 相关3

1.Nth Highest Salary

Write a SQL query to get the nth highest salary from the Employee table.

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  DECLARE M INT;
  SET M=N-1;
  RETURN (
      # Write your MySQL query statement below.
      #select Max(e1.Salary) from Employee e1 where N-1 = (select count(*) from Employee e2 where e1.Salary < e2.Salary) 
      #SELECT MAX(Salary) FROM Employee E1 WHERE M = (SELECT COUNT(DISTINCT(E2.Salary)) FROM Employee E2 WHERE E2.Salary > E1.Salary)
      SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT M, 1
  );
END

2. 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.

+----+-------+
| Id | Score |
+----+-------+
| 1  | 3.50  |
| 2  | 3.65  |
| 3  | 4.00  |
| 4  | 3.85  |
| 5  | 4.00  |
| 6  | 3.65  |
+----+-------+
For example, given the above Scores table, your query should 
generate the following report (order by highest score):

+-------+------+
| Score | Rank |
+-------+------+
| 4.00  | 1    |
| 4.00  | 1    |
| 3.85  | 2    |
| 3.65  | 3    |
| 3.65  | 3    |
| 3.50  | 4    |
+-------+------+

#笛卡尔积,挺复杂
select Scores.Score , count(Ranking.Score) as Rank from 
Scores,(select distinct Score from Scores) Ranking  
where Scores.Score <= Ranking.Score 
group by Scores.Score,Scores.Id 
order by Scores.Score DESC

3.找出连续出现的字段Consecutive Numbers

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

+----+-----+
| Id | Num |
+----+-----+
| 1  |  1  |
| 2  |  1  |
| 3  |  1  |
| 4  |  2  |
| 5  |  1  |
| 6  |  2  |
| 7  |  2  |
+----+-----+

-- SELECT DISTINCT L1.Num as ConsecutiveNums
-- FROM Logs L1, Logs L2, Logs L3
-- WHERE (L1.Id = L2.Id + 1 AND L1.Num = L2.Num) AND
--   (L1.Id = L3.Id + 2 AND L1.Num = L3.Num)
  

SELECT DISTINCT Num
FROM (
  SELECT Num, 
    CASE 
      WHEN @prev = Num THEN @count := @count + 1
      WHEN (@prev := Num) IS NOT NULL THEN @count := 1
    END CNT
  FROM Logs, (SELECT @prev := NULL) X
  ORDER BY Id
) AS A
WHERE A.CNT >= 3

4.Department Highest Salary

每个组中最大的值

The Employee table holds all employees. Every employee has an 
Id, a salary, and there is also a column for the department Id.

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
+----+-------+--------+--------------+
The Department table holds all departments of the company.

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+
Write a SQL query to find employees who have the highest salary in 
each of the departments. For the above tables, Max has the highest salary 
in the IT department and Henry has the highest salary in the Sales department.

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| Sales      | Henry    | 80000  |
+------------+----------+--------+

SELECT Department,Employee,Salary
FROM (select Department.Name as Department ,Employee.name as Employee ,Salary,DepartmentId from Employee,Department  where Employee.DepartmentId = Department.Id ) e
WHERE e.Salary IN
  (SELECT max(Salary) AS Salary
   From Employee where e.DepartmentId  = Employee.DepartmentId
   GROUP BY DepartmentId )