经过之前两次的学习,这次用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;