MySQL练习题01

一、第二高的薪水

方法1，limit：

select (
select distinct Salary
from Employee
order by Salary desc
limit 1,1
)  SecondHighestSalary

方法2 ：

select max(Salary) SecondHighestSalary
from Employee
where Salary<(
    select max(Salary)
    from Employee
)

方法3，ifnull+limit：

select ifnull(
    (
    select distinct Salary
    from Employee
    order by Salary
    limit 1,1
    ),null
) SecondHighestSalary

方法4，自连接：

select e1.Salary,e2.Salary
from Employee e1,Employee e2
where e1.Salary<=e2.Salary
group by e1.Salary
having count(distinct e2.Salary)=1

二、第N高的薪水

方法1，第一题的各种方法：

create function getNthHighestSalary(N int)
returns int
begin
	set N=N-1;
	return(
		select distinct Salary from Employee order by Salary limit N,1
	);
end

方法2，窗口函数：

create function getNthHighestSalary(N int) returns int
begin
	return(
	select T.Salary
	from (
		select Salary
		,dense_rank() over(order by Salary desc) rn
		from Employee	
	)T
	where T.rn=N
	);
end

三、部门工资前三高的所有员工

方法1，窗口函数：

select T.Department,T.Employee,T.Salary
from(
    select e.Id,d.Name Department ,e.Name Employee ,e.Salary
    ,dense_rank() over(partition by e.DepartmentId order by e.Salary desc) dn
    from Employee e
    inner join Department d on e.DepartmentId=d.Id
)T
where T.dn<=3
order by T.Id

方法2，自连接内连接
这题最初写的时候，count里的条件写错了，写成了e1的salary。

select d.Name Department,e1.Name Employee,e1.Salary Salary
from Employee e1
inner join Employee e2 on e1.DepartmentId=e2.DepartmentId and e1.Salary<=e2.Salary
inner join Department d on d.Id=e1.DepartmentId 
group by e1.Id
having count(distinct e2.Salary)<=3

四、行程和用户

select Trips.Request_at as Day, round(sum(if(Status='completed',0,1))/count(if(Status='completed',1,0)),2) as 'Cancellation Rate'
from Trips 
inner join Users u1 on u1.Users_Id=Trips.Client_Id
inner join Users u2 on u2.Users_Id=Trips.Driver_Id 
where u1.Banned='No' and u2.Banned='No' and Trips.Request_at between '2013-10-01' and '2013-10-03'
group by Trips.Request_at

五、求员工薪水中位数

方法1，使用窗口函数：

select Id,Company,Salary 
from(
select *
,row_number() over(partition by Company order by Salary asc) rn
,count(*) over(partition by Company) cc
from Employee)T
where T.rn in (cc/2,cc/2+1,cc/2+0.5)

方法2，使用自连接：
中位数概念：小于等于这个数的数量和大于等于这个数的数量相等，即为中位数

select id,Company,Salary
from Employee
where Id in (
select e1.Id
from Employee e1,Employee e2
where e1.Company=e2.Company
group by e1.Id
having sum(case when e1.Salary>=e2.Salary then 1 else 0 end )>=count(*)/2 
	and sum(case when e1.Salary<=e2.Salary then 1 else 0 end )>=count(*)/2)
group by Company,Salary
order by Salary 

六、超过经理收入的员工

方法1，使用两表自连接

select e1.Name Employee 
from Employee e1,Employee e2
where e1.ManagerId=e2.Id and e1.Salary>e2.Salary

七、连续出现的数字

方法1:，使用窗口函数lag：

select distinct T.Num ConsecutiveNums 
from (
    select Num
    ,lag(Num,1,null) over() l1
    ,lag(Num,2,null) over() l2
    from Logs
)T
where T.Num=T.l1 and T.Num=T.l2

方法2:，使用用户变量

select distinct Num ConsecutiveNums 
from (
    select Num
    ,case when @next=Num then @count:=@count+1 
    	when (@next:=Num) is not null then @count:=1 end as cnt
    from Logs,(
        select @next:=null,@count:=null
    )T1
)T2
where cnt>=3

代码解释：

• 使用用户变量，把第一个值赋给next，如果下一行等于这个变量，count++，否则count重新赋1

方法3:，自连接

select distinct l1.Num ConsecutiveNums
from Logs l1,Logs l2,Logs l3
where l1.Id=l2.Id+1 and l1.Id=l3.Id+2 and l1.Num=l2.Num and l1.Num=l3.Num
group by l1.Id

八、分数排名

方法1：自连接，比自身大或小的个数

select s1.Score,count(distinct s2.Score) 'Rank'
from Scores s1,Scores s2
where s1.Score<=s2.Score
group by s1.Id
order by s1.Score desc

方法2：窗口函数

select Score
,dense_rank() over(order by Score desc) 'Rank'
from Scores