- 第N高的薪水
编写一个 SQL 查询，获取 Employee 表中第 n 高的薪水（Salary）。
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
例如上述 Employee 表，n = 2 时，应返回第二高的薪水 200。如果不存在第 n 高的薪水，那么查询应返回 null。
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200 |
+------------------------+
MySQL 版本
input:
{"headers": {"Employee": ["Id", "Salary"]}, "argument": 2, "rows": {"Employee": [[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 DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET M
);
END
output:
{"headers":["getNthHighestSalary(2)"],"values":[[200]]}
求教MS SQL Server版本。。。
- 分数排名
编写一个 SQL 查询来实现分数排名。如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。
+----+-------+
| Id | Score |
+----+-------+
| 1 | 3.50 |
| 2 | 3.65 |
| 3 | 4.00 |
| 4 | 3.85 |
| 5 | 4.00 |
| 6 | 3.65 |
+----+-------+
例如，根据上述给定的 Scores 表，你的查询应该返回（按分数从高到低排列）：
+-------+------+
| Score | Rank |
+-------+------+
| 4.00 | 1 |
| 4.00 | 1 |
| 3.85 | 2 |
| 3.65 | 3 |
| 3.65 | 3 |
| 3.50 | 4 |
+-------+------+
input:
{"headers": {"Scores": ["Id", "Score"]}, "rows": {"Scores": [[1, 3.50], [2, 3.65], [3, 4.00], [4, 3.85], [5, 4.00], [6, 3.65]]}}
/* Write your T-SQL query statement below */
SELECT Score,(SELECT COUNT(DISTINCT score)
FROM Scores
WHERE score >= s.score
)AS Rank
FROM Scores s
ORDER BY Score DESC
output:
{"headers":["Score","Rank"],"values":[[4.0,1],[4.0,1],[3.85,2],[3.65,3],[3.65,3],[3.5,4]]}
- 连续出现的数字
编写一个 SQL 查询，查找所有至少连续出现三次的数字。
+----+-----+
| Id | Num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+
例如，给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。
+----------------+
| ConsecutiveNums |
+-----------------+
| 1 |
+-----------------+
input:
{"headers": {"Logs": ["Id", "Num"]}, "rows": {"Logs": [[1, 1], [2, 1], [3, 1], [4, 2], [5, 1], [6, 2], [7, 2]]}}
/* Write your T-SQL query statement below */
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
output:
{"headers":["ConsecutiveNums"],"values":[[1]]}
- 部门工资最高的员工
Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
+----+-------+--------+--------------+
Department 表包含公司所有部门的信息。
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
编写一个 SQL 查询，找出每个部门工资最高的员工。例如，根据上述给定的表格，Max 在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资。
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| Sales | Henry | 80000 |
+------------+----------+--------+
input:
{"headers": {"Employee": ["Id", "Name", "Salary", "DepartmentId"], "Department": ["Id", "Name"]}, "rows": {"Employee": [[1, "Joe", 70000, 1], [2, "Henry", 80000, 2], [3, "Sam", 60000, 2], [4, "Max", 90000, 1]], "Department": [[1, "IT"], [2, "Sales"]]}}
/* Write your T-SQL query statement below */
SELECT Department.Name AS Department, Employee.Name AS Employee, Salary
FROM Department INNER JOIN Employee
ON Department.Id = Employee.DepartmentId
AND Employee.Salary = (SELECT max(Salary)
FROM Employee
WHERE DepartmentId=Department.Id
)
output:
{"headers":["Department","Employee","Salary"],"values":[["Sales","Henry",80000],["IT","Max",90000]]}
- 换座位
小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。
其中纵列的 id 是连续递增的
小美想改变相邻俩学生的座位。
你能不能帮她写一个 SQL query 来输出小美想要的结果呢？
示例：
+---------+---------+
| id | student |
+---------+---------+
| 1 | Abbot |
| 2 | Doris |
| 3 | Emerson |
| 4 | Green |
| 5 | Jeames |
+---------+---------+
假如数据输入的是上表，则输出结果如下：
+---------+---------+
| id | student |
+---------+---------+
| 1 | Doris |
| 2 | Abbot |
| 3 | Green |
| 4 | Emerson |
| 5 | Jeames |
+---------+---------+
input:
{"headers": {"seat": ["id","student"]}, "rows": {"seat": [[1,"Abbot"],[2,"Doris"],[3,"Emerson"],[4,"Green"],[5,"Jeames"]]}}
# Write your MySQL query statement below
SELECT
(CASE
WHEN ((id % 2 = 1) AND (id < (SELECT MAX(id) FROM seat))) THEN (id+1)
WHEN (id % 2 = 0) THEN (id-1)
ELSE id
END
) AS id,student
FROM seat
ORDER BY
(CASE
WHEN ((id % 2 = 1) AND (id < (SELECT MAX(id) FROM seat))) THEN (id+1)
WHEN (id % 2 = 0) THEN (id-1)
ELSE id
END
)
output：
{"headers":["id","student"],"values":[[1,"Doris"],[2,"Abbot"],[3,"Green"],[4,"Emerson"],[5,"Jeames"]]}
