LeetCode力扣 精选数据库70题 （SQL自学记录）

目录

511写一条 SQL 查询语句获取每位用户 第一次登陆平台的日期。

题目

解答思路：可以用窗口函数;也可以用最小值函数min

## 最小函数
select player_id,min(event_date) as first_login
from Activity
group by player_id

## 最小值窗口函数
select distinct player_id,min(event_date) over(partition by player_id) as first_login
from Activity

512. 游戏玩法分析 II

题目

解答思路：一种是窗口函数方法；另一种是用（）in（）方法

## （）in（）方法
select player_id,device_id
from Activity
where (player_id,event_date) in 
( select player_id,min(event_date) as event_date
from Activity 
group by player_id )

## 窗口函数
select player_id, device_id
from 
(select player_id, device_id, 
dense_rank() over(partition by player_id order by event_date asc) rk 
from activity) a
where a.rk=1

534. 游戏玩法分析 III

题目

解答思路：使用累加求和窗口函数sum()over()

补充知识点：窗口函数sum()over() 链接：Hive–开窗函数–窗口聚合函数：SUM、AVG、COUNT、MAX、MIN_韩家小志的博客-CSDN博客_窗口函数count

select player_id, event_date, 
sum(games_played) over(partition by player_id order by event_date) games_played_so_far 
from activity

550. 游戏玩法分析 IV  重难点

解答思路：先得到用户首次注册后次日登录的日期（用date_add函数得出），再对应原数据是否存在该日期，统计存在的个数，除以总人数。注意这道题求的是首日注册后第二天连续登录的，不是任意两天连续登录就行。

补充知识点：

select round(count(distinct player_id)/ (select count(distinct player_id) from activity),2) fraction
from activity 
where (player_id, event_date) in
(
    select player_id, date_add(min(event_date),interval 1 day)
    from activity
    group by player_id

)

569. 员工薪水中位数

题目

解答思路：观察一下两种情况，总数为奇数，总数为偶数，A总共6个数，即total=6，我们想要rn=3和rn=4，C总共5个数，即total=5，我们想要rn=3。

select id,company,salary
from
( select id, company,salary,
    row_number() over(partition by company order by salary) as rk,
    count(salary) over(partition by company) as cnt
    from Employee 
  )  t
where t.rk in (cnt/2,(cnt/2)+1,(cnt/2)+0.5)

578. 查询回答率最高的问题    重难点

题目

解答思路：先对问题id及进行分组，求出各个问题的显现及回答的次数，用case when方法计算其比率，再从中查找相应字段并排序，选取第一行

579. 查询员工的累计薪水

题目

解答思路：先排除掉每个人最大的月份，再用窗口函数求累积的和，注意这里的“order by month range 2 preceding”意思为当前月份往前两个范围再包括当前月份，例当前月份为5，则计算3、4、5月的薪资。

补充知识点：row和range的区别

• rows 是指 行数， 以ID 1 为例， 月数 1，2，3，4，7，8。 当我们用 rows between 2 preceding and current row时，当月数等于7时，我们sum了7所在的一行加7之前的两行（月数3和4）所以sum是190
• range 是指 逻辑 或者 读值。 我的理解就是这玩意比较智能，当我们用 range between 2 preceding and current row时，当月数等于7时， range智能的选择了 7-1 = 6月， 和 7-2 =5月 这两个月，然而表里id 1并没有这两个月所以是0， 所以sum是90.

select id,month,sum(salary)over(partition by id order by month range 2 precding) Salary
from Employee
where (id,month) not in
(select id,max(month)
from employee
group by id)
order by id,month desc

584 ifnull的用法

题目

解答思路：null不能直接与值比较，用ifnull转换。知识点 ifnull：IFNULL(expr1,expr2)，如果expr1的值为null，则返回expr2的值，如果expr1的值不为null，则返回expr1的值。

MySQL中IF()、IFNULL()、NULLIF()、ISNULL()函数的使用_pan_junbiao的博客-CSDN博客_mybantis ifnull(d.issync,’0′)

select name
from customer
where ifnull(referee_id,0) !=2

585 2016的投资

题目

解答思路：使用窗口函数

select round(sum(TIV_2016),2) TIV_2016
from 
( select * ,
count(pid)over(partition by TIV_2015 ) a,
count(pid)over(partition by lat,lon) b
from insurance  )  i
where i.a > 1 and i.b=1

597 好友申请总通过率

题目

select round(
ifnull(
    (select count(distinct requester_id,accepter_id)from RequestAccepted)/
    (select count(distinct sender_id,send_to_id) from FriendRequest)
    ,0)
    ,2) as accept_rate

602 好友申请

题目

解答思路：union all对两个结果集进行并集操作, 包括重复行, 即所有的结果全部显示, 不管是不是重复。

union和union all的区别_我心依依旧的博客-CSDN博客_union和union all有什么区别

select id,count(*) num
from
((select requester_id id from RequestAccepted)
union all
(select accepter_id id from RequestAccepted)) a
group by id
order by num desc
limit 1

607 销售员

题目

--两种解答方法

select name
from SalesPerson
where sales_id not in
(select sales_id
from Orders O
left join Company c
on o.com_id=c.com_id
where name = 'RED')


select name
from SalesPerson 
where sales_id not in
( select sales_id 
from Orders
where com_id in
(select com_id from Company where name='RED') )

614 二级关注者

题目

解答思路：对followee重命名字段为follower，则可以直接对followee进行聚合。

select followee as follower,count(follower) num
from follow
where followee in 
(select follower from follow)
group by followee
order by followee

617 平均工资：部门与公司比较

题目

解答。知识点：date_format、case when 的运用、窗口函数的运用

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

618 学生地理位置

题目

解答思路：max()函数的功效：（‘jack’, null, null）中得出‘jack’，（null,null）中得出null。 min()函数也可以。

select max(case when continent='America' then name else null end) America,
max(case when continent='Asia' then name else null end) Asia,
max(case when continent='Europe' then name else null end) Europe
from 
(select *,row_number()over(partition by continent order by name) rk
from student  ) a
group by rk