牛客網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;