sql做题记录（一）

题目来自leetcode
记录题型，做题方法，错误原因和注意事项
如有问题 请及时纠正

1.分数排序
要求：如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。

SELECT Score, dense_rank() over(order by Score DESC) 
AS 'Rank' from Scores;

这里用dense_rank()来对成绩进行排序

row_number(),rank(),dense_rank()的区别

1. row_number() 按前后顺序依次连续递增排序 相同数值的排名是不同的 1，2，3，4，5…
2. rank() 相同数值的排序相同 总体排名可能不连续 基于个数进行排序 1，2，2，2，5… 主要用于学生成绩排名等
3. dense_rank() 相同数值的排序相同 总体排名连续
   1,2,2,2,3…

2.第n高的薪水

```bash
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
SET N=N-1;
  RETURN (
      
      # Write your MySQL query statement below.
      select ifnull((select distinct Salary from Employee order by Salary DESC 
      limit N,1),NULL) 
  );
END

思路：先按薪水降序排序，使用limit x,y 去掉前面x行的值，取后面y行
由于要取第n个，所以set n=n-1,即去掉前面n-1，去后面1

注：
1)用ifnull()使没有的情况下取null
2）如果要取拥有第n高薪水人的信息，不能直接在原基础上加上其他列，会取不到相同薪水的人员；要先得到第n高的薪水值，再用where进行过滤

3.每个部门薪资最高的员工

SELECT
	Department.NAME AS Department,
	Employee.NAME AS Employee,
	Salary 
FROM
	Employee,
	Department 
WHERE
	Employee.DepartmentId = Department.Id 
	AND ( Employee.DepartmentId, Salary ) 
    IN (SELECT DepartmentId, max( Salary ) 
        FROM Employee 
        GROUP BY DepartmentId )

思路：联结employee和department两个表
选取DepartmentId, Salary 符合max标准的行
注：group by 只能作用于DepartmentId, max( Salary )，如果加上其他的列进行select在数据多时会出现错误

4.首日登录后次日登陆的玩家数量

select round(count(b.player_id)/count(distinct a.player_id),2) as 'fraction' 
from (select player_id,min(EVENT_DATE)as e  from Activity 
group by player_id) a //a表为首日登陆用户和登陆的日期
left join Activity b
on a.player_id=b.player_id and DATEDIFF(b.EVENT_DATE, a.E) = 1 

思路：联结两张表，条件是日期差一天
注：日期要取最小值计算；不可以直接用min(a.play_id)， 要新建表

5.最多好友数

select a.id,sum(a.n)as num from
(select requester_id as id ,count(*) as 'n' from request_accepted group by requester_id union all
select  accepter_id as id ,count(*) as 'n' from request_accepted group by accepter_id) as a
group by a.id 
order by 2 desc limit 

因为requester_id，accepter_id的id好友数都要计算 所以连接两个表用union all 之后取最大的值，所以可以用order by desc limit 1

6.比较每个月各部门的平均收入与公司总体收入

select  distinct date_format(pay_date, '%Y-%m') as pay_month,e.department_id,
 case when avg(s.amount) over(partition by s.pay_date,e.department_id )>avg(s.amount) over(partition by s.pay_date)
    then 'higher'
    when avg(s.amount) over(partition by s.pay_date,e.department_id )<avg(s.amount)over (partition by s.pay_date)
    then 'lower'
    else 'same'
end as comparison
 from salary s , employee e 
 where s.employee_id=e.employee_id
 order by 1 desc

用case when…将几种不同的情况分类获取输出比较结果
注：在取公司每月平均值时，不要忘记(partition by s.pay_date)按时间来选取

7.学生地理位置 纵向输出
题目指出来自america的学生要多于其他两个地方的学生

select America, Asia,Europe
from (select row_number() over(order by name) id, name as America from student where continent = 'America') a
left join (select row_number() over(order by name) id, name as Asia from student where continent = 'Asia') b
on a.id = b.id
left join (select row_number() over(order by name) id, name as Europe from student where continent = 'Europe') c
on a.id = c.id
order by 1,2,3

思路：首先选取处分别来自着三个地方的学生，再将学生进行排序（序号作为id)，方便后期表联结，由于来自美洲的学生最多，所以用美洲的同学进行两次与两位两个表的left join 联结，联结的依据是排序编号id相同。
注：这里尽量不要用case when 。。。then 'america’这样的形式尽享选取，不然会出现空表选取时，返回结果为[null,null,null]而不是[ ]。