task06-练习

练习一: 各部门工资最高的员工
select departname,name,salary  from (
select t.*,row_number() over (partition by departmentid order by salary desc ) as ranking from
(
select a.id,a.name,a.salary,a.departmentid,b.name as departname
from  Employee a
left join  Department b on a.departmentid=b.id
) t
) t1
where ranking ='1'

练习二: 换座位,改变相邻俩学生的座位。
SELECT if(id%2=0,id-1,if(id%2=1 and id!=(select max(id) from seat),id+1,id)) as id,student 
from seat order by id asc 

select 
case when  id%2=0 then id-1 
when id%2=1 and id!=(select max(id) from seat) then id+1
else id end as id,student from seat
order by id asc 
--练习三: 分数排名
select score,
DENSE_RANK() over (order by score desc) as ranking
from score
--练习四：连续出现的数字
select distinct t1.num  ConsecutiveNums 
from Logs t1 left join Logs t2 on t1.id =t2.id + 1
			left join Logs t3 on t1.id =t3.id + 2 
where t1.num = t2.num and t1.num = t3.num;
--练习五：树节点
 SELECT id
      ,IF(ISNULL(p_id), 'Root'
      ,IF(id IN (SELECT p_id FROM tree), 'Inner', 'Leaf')) AS Type
  FROM tree 
 ORDER BY id;
 
 
 select id,
	(case when p_id is null then 'Root' 
		when id in (select p_id from tree ) then 'Inner'
		when id not in (select p_id from tree ) then 'Leaf' end ) as type
 from tree
--练习六：至少有五名直接下属的经理
SELECT a.Name FROM Employee a
JOIN
    (SELECT  ManagerId FROM Employee
    GROUP BY ManagerId
    HAVING COUNT(*) >= 5) b 
ON a.Id = b.ManagerId
--练习七: 分数排名 
select a.* , rank() over (order by score desc) as ranking  from score a
--练习八：查询回答率最高的问题
select *  from (
select a.* ,rank() over ( order by r desc) ranking from 
(
select question_id,
(sum(case when  action='answer' then 1 else 0 end)/sum(case when action='show' then 1 else 0 end) ) as r
from  survey_log
group by question_id
) a
) b
where ranking ='1'
--练习九：各部门前3高工资的员工
select departname,name,salary  from (
select t.*,row_number() over (partition by departmentid order by salary desc ) as ranking from
(
select a.id,a.name,a.salary,a.departmentid,b.name as departname
from  Employee a
left join  Department b on a.departmentid=b.id
) t
) t1
where ranking <='3'
--练习十：平面上最近距离
select  round(sqrt(min(power((t1.x-t2.x),2)+power((t1.y-t2.y),2))),2)  as shortest 
 from point_2d t1,point_2d t2
where t1.x<>t2.x  or t1.y<>t2.y
--练习十一：行程和用户,非禁止用户的取消率
select Request_at day,
round(sum(case when status<>'completed' then 1 else 0 end)/count(id),2) as  Cancellation
from 
(select t1.* from trips t1
left join users t2 on  t1.client_id=t2.users_id
where t2.banned='no') a 
group by  Request_at 
order by  day asc