CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT BEGIN SET N = N - 1; RETURN ( select Salary from Employee GROUP BY Salary ORDER BY Salary desc LIMIT N,1 ); END
+----------+ | Employee | +----------+ | Joe | +----------+
通过
解答：通过自连接 关联表两次查询 组装出员工表每个员工对应的经理信息 即可通过条件判断取出结果
1 2
select e1.Name Employee from Employee e1,Employee e2 where e1.ManagerId=e2.Id and e1.Salary>e2.Salary
部门工资最高的员工
Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。
1 2 3 4 5 6 7 8
+----+-------+--------+--------------+ | Id | Name | Salary | DepartmentId | +----+-------+--------+--------------+ | 1 | Joe | 70000 | 1 | | 2 | Henry | 80000 | 2 | | 3 | Sam | 60000 | 2 | | 4 | Max | 90000 | 1 | +----+-------+--------+--------------+
Department 表包含公司所有部门的信息。
1 2 3 4 5 6
+----+----------+ | Id | Name | +----+----------+ | 1 | IT | | 2 | Sales | +----+----------+
编写一个 SQL 查询，找出每个部门工资最高的员工。例如，根据上述给定的表格，Max 在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资。
解答 内连接匹配查询出所有员工对应的部门名称 然后通过子查询条件匹配
1 2 3
select d.Name as Department,e.Name as Employee, e.Salary from Department d join Employee e on d.Id=e.DepartmentId where e.Salary >=(select max(Salary) from Employee where DepartmentId=d.Id)
部门工资前三高的员工
Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id 。
1 2 3 4 5 6 7 8 9 10
+----+-------+--------+--------------+ | Id | Name | Salary | DepartmentId | +----+-------+--------+--------------+ | 1 | Joe | 70000 | 1 | | 2 | Henry | 80000 | 2 | | 3 | Sam | 60000 | 2 | | 4 | Max | 90000 | 1 | | 5 | Janet | 69000 | 1 | | 6 | Randy | 85000 | 1 | +----+-------+--------+--------------+
Department 表包含公司所有部门的信息。
1 2 3 4 5 6
+----+----------+ | Id | Name | +----+----------+ | 1 | IT | | 2 | Sales | +----+----------+
思路是查询Employee a,Employee b 而且a.salary小于b.salary，a.departmentid=b.departmengtid，其中去重后的b数量不能大于3，这样就可以查出每个部门工资前三的员工
SELECT Department.Name AS Department, e1.Name AS Employee, e1.Salary AS Salary FROM Employee e1 JOIN Department ON e1.DepartmentId = Department.Id WHERE 3 > ( SELECT COUNT(DISTINCT e2.Salary) FROM Employee e2 WHERE e2.Salary > e1.Salary AND e1.DepartmentId = e2.DepartmentId )
删除重复的电子邮箱
编写一个 SQL 查询，来删除 Person 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小 的那个。
条件帅选今天比昨天温度高的数据 w1.Temperature>w2.Temperature select w1.id from weather w1 join weather w2 on DATEDIFF(w1.RecordDate,w2.RecordDate)=1 where w1.Temperature>w2.Temperature
select t.Request_at as Day, round(count( IF ( t.Status !="completed", t.Status, NULL ) ) / count(t.Status),2) as `Cancellation Rate` from Trips t join Users u on t.Client_Id =u.Users_Id where u.Banned='No' and t.request_at between "2013-10-01" and "2013-10-03" group by t.Request_at
select case #如果 when id%2=0 then id-1 # id%2为偶数 则返回 id-1 when id<(select max(id) from seat) then id+1 #如果表中最大的ID 小于当前返回的ID 则执行 id+1 也就是取最大ID else id #如果存在未变化的数则值不变 end as id,student from seat order by id
评论