牛客网SQL练习----学习总结(二) 薪水(成绩)的排名

获取排名第二多薪水员工的信息
类似的有查询每门功课的前两名(第二名)
查找当前薪水(to_date=‘9999-01-01’)排名第二多的员工编号emp_no、薪水salary、last_name以及first_name

CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

方法一,使用order by 和 limit
重点是理解同一薪水的可能存在多个人

1. 首先选择employees 表和salaries 表,通过join 间接 以及emp_no 限制
2. to_date 限制当前薪水
3. 通过按照salary 进行分组和排序以后得出 薪水的阶梯水平,从高到低,再通过limit 语句限制是第二高的薪水,limit 1.1表示从第一列开始取一列

select e.emp_no,s.salary,e.last_name,e.first_name
from employees e join salaries s on e.emp_no=s.emp_no and s.to_date='9999-01-01'
where s.salary=(select max(salary) from salaries group by salary order by salary desc limit 1,1)

方法二 不使用order by 通过表的自连接 确定第二高的薪水

select e.emp_no,s.salary, e.last_name,e.first_name
from emplotyees e join salaries s on e.emp_no=s.emp_no
and s.to_date='9999-01-01'
where s.salary=(select s1.salary from salaries s1 
join salaries s2 on s1.salary<=s2.salary  and s2.to_date='9999-01-01' 
and s2.to_date='9999-01-01'
group by s1.salary having count(distinct s2.salary)=2)

方法三 使用max 函数

select e.emp_no,s.salary, e.last_name,e.first_name
from emplotyees e join salaries s on e.emp_no=s.emp_no
and s.to_date='9999-01-01'
where s.salary=( select max(salary) 
			     from salaries 
			     where salary<(select max(salary)
			          		   from salaries
			          		   where  to_date='9999-01-01'
			          		   )
				and to_date='9999-01-01'
				)

---

– 查询每门功成绩最好的前两名

select r.s_id, r.s_name, r.c_id, r.s_score from
				(select  b.s_id,b.s_name,a.c_id,a.s_score , @iii:=@iii+1 as rank 
						from score a,student b,(select @iii:=0)s  where a.s_id=b.s_id and a.c_id='01' order by a.s_score desc)r
where r.rank between 1 and 2
union
select  r.s_id ,r.s_name,r.c_id,r.s_score from (select  b.s_id,b.s_name,a.c_id,a.s_score ,
		case when @i=a.s_score then @j
              when @i:=a.s_score then @j:=@j+1
              end as rank 
from score a,student b,(select @j:=0,@i:=null)s
where a.s_id=b.s_id and a.c_id='02' order by a.s_score desc)r
where r.rank between 1 and 2
union
select  r.s_id ,r.s_name,r.c_id,r.s_score from (select  b.s_id,b.s_name,a.c_id,a.s_score ,
		case when @ii=a.s_score then @jj
              when @ii:=a.s_score then @jj:=@jj+1
              end as rank 
from score a,student b,(select @jj:=0,@ii:=null)s
where a.s_id=b.s_id and a.c_id='03' order by a.s_score desc)r
where r.rank between 1 and 2;

---

– 超简介的写法

select a.s_id,a.c_id,a.s_score from score a
		where (select COUNT(*) 
		from score b where b.c_id=a.c_id 
		and b.s_score>=a.s_score)<=2 ORDER BY a.c_id;

---

–另一种

select a.s_id,a.c_id,a.s_score from score a left join score b 
on a.c_id=b.c_id and a.s_score<b.s_score
group by a.c_id,a.s_id,a.s_score having count(b.s_id)<2;