MySQL基础练习(三)

经过之前两次的学习，这次用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）保留两位小数。

各部门前3高工资的员工

 对于上次的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;