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;