经过之前两次的学习，这次用MySQL进行略微复杂的操作练习
首先创建表employee和表department。如下
我们需要查询每个部门工资最高的员工
select a.Name as Department,b.Name as Employee,b.Salary from department a join (select DepartmentId,Name,Salary from employee where Salary in( select max(salary) as salary from employee group by DepartmentId)) b on a.Id = b.DepartmentId;
同样的先创建表seat
SELECT(CASE WHEN id %2 = 1 AND id!=max_id THEN id+1 WHEN id %2 = 0 THEN id-1 WHEN id = max_id THEN id END) AS id,student from (select id,student,(select MAX(id) from seat) as max_id from seat) a order by id;
先创建表scores
我们要查询来实现分数排名。如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。
SELECT Score, CASE WHEN @prevRank = Score THEN @curRank WHEN @prevRank := Score THEN @curRank := @curRank + 1 END AS Rank FROM scores, (SELECT @curRank :=0, @prevRank := NULL) ORDER BY Score desc;
写一段 SQL 语句查出 2013年10月1日 至 2013年10月3日 期间非禁止用户的取消率。基于上表，你的 SQL 语句应返回如下结果，取消率（Cancellation Rate）保留两位小数。
对于上次的employee表我们需要新插入两行数据
找出每个部门工资前三高的员工
SELECT d.name AS Department, e.Name AS Employee, Salary FROM Employee e JOIN Department d ON e.DepartmentId = d.Id WHERE (SELECT COUNT(DISTINCT em.Salary) FROM Employee em WHERE em.Salary >= e.Salary AND em.DepartmentId = e.DepartmentId) <= 3 GROUP BY Department , Salary DESC;
上次的表scores
SELECT s.Score, (SELECT COUNT(*) + 1 FROM Scores AS s1 WHERE s1.Score > s.Score) AS Rank FROM scores s ORDER BY Score DESC;
原文：https://www.cnblogs.com/mambakb/p/10662981.html