各部门工资最高的员工/换座位/分数排名/行程和用户/各部门前3高工资的员工/分数排名

各部门工资最高的员工
select d.Name as Department,e.Name as Employee,e.Salary
from Department d,Employee e
where e.DepartmentId=d.Id and e.Salary=(Select max(Salary) from Employee where DepartmentId=d.Id)

换座位
#当原id为奇数时，交换座位后的id变为id+1,当原id为偶数时，交换座位后的id变为id-1,另一个方面需要考虑的是，学生人数为奇数时，最后一个学生的id不变，故应当用子查询确定学生的人数，然后分情况讨论即可.
#mod（n,m）n除以m之后的余数
select (case 
       when mod(id,2)!=0 and id!=counts then id+1
       when mod(id,2)!=0 and id=counts then id
       else id-1 end)as id,student
       from seat,(select count(*)as counts from seat)
       as seat_counts
       order by id;


分数排名
#SQL COUNT(DISTINCT column_name) 语法
#COUNT(DISTINCT column_name) 函数返回指定列的不同值的数目：
#rank（）函数 简单来说rank函数就是对查询出来的记录进行排名
#使用表别名，与前一个数值进行比较，count大于s.score的score
# 然后降序排列
select Score,
(select count(distinct Score) from Scores where Score >=s.Score) rank
from Scores s order by Score desc;

行程和用户
#Case搜索函数
#SUM 函数返回数值列的总数（总额）
#ROUND 函数用于把数值字段舍入为指定的小数位数
SELECT Request_at Day, 
ROUND(COUNT(IF(Status != 'completed', TRUE, NULL)) / COUNT(*), 2) 'Cancellation Rate' 
            FROM Trips 
            WHERE (Request_at between '2013-10-01' and '2013-10-03') and Client_Id IN (SELECT Users_Id FROM Users WHERE Banned = 'No') 
            GROUP BY Request_at;
  

各部门前3高工资的员工
select 
    d.Name Department, e1.Name Employee, e1.Salary
from
    Employee e1, Employee e2, Department d    
where
    e1.DepartmentId = e2.DepartmentId 
and
    e1.Salary <= e2.Salary
and
    e1.DepartmentId = d.Id
group by
    e1.Id    
having 
    count(distinct e2.Salary) <= 3   
order by
    d.Name, e1.Salary desc    
;
#工资前三高的员工可以通过连表求出来。
#连表的条件：e1.DepartmentId = e2.DepartmentId and e1.Salary <= e2.Salary
#再通过group by e1.Id having count(distinct e2.Salary) <= 3 求得前三高的员工
#部门的名字也是通过连表求出来
#连表的条件:e1.DepartmentId = d.Id
#最后输出结果需要进行排序
#order by d.Name, e1.Salary desc;
          
分数排名
#SQL COUNT(DISTINCT column_name) 语法
#COUNT(DISTINCT column_name) 函数返回指定列的不同值的数目：
#rank（）函数 简单来说rank函数就是对查询出来的记录进行排名
##使用表别名，与前一个数值进行比较，count大于s.score的score
# 然后降序排列
select Score,
(select count(distinct Score) from Scores where Score >=s.Score) rank
from Scores s order by Score desc;