1.员工奖金

select a.name,b.bonus
from Employee  as a
left join Bonus b
on a.empId = b.empId
where b.bonus<1000 or b.bonus is null

select a.name,b.bonus
from Employee  as a
left join Bonus b
on a.empId = b.empId
where a.empId not in(
    select a1.empId 
    from Employee  as a1
    left join Bonus b1
    on a1.empId = b1.empId 
    where b1.bonus>1000 
)

2.查询回答率最高的问题

select a.question_id as survey_log  
from survey_log  as a
where a.answer_id is not null
group by a.question_id  
order by count(*) desc
limit 1

select question_id as survey_log
from survey_log
group by question_id
order by sum(if(action = 'answer', 1, 0)) / sum(if(action = 'show', 1, 0)) desc
limit 1

3.查询员工的累计薪水

窗口部分有3部分

• partition by 必选
• order by 可选(row_number和range between是必选的)
• rows between 、range between

参考：窗口函数rows between 、range between的使用

select b.Id,b.Month,
        sum(Salary) over(partition by Id order by Month rows between 2 preceding and 0 following) as Salary
from (select  Id , Month , Salary
from (select *,max(Month) over(partition by Id)as max_
from Employee) as a
where a.Month <> a.max_) as b
order by b.Id,b.Month desc

4.统计各专业学生人数

select d.dept_name , count(s.student_name) as student_number
from department as d
left join student  as s
on d.dept_id =  s.dept_id
group by d.dept_id
order by student_number desc,department.dept_name

5. 寻找用户推荐人

select c.name
from customer as c
where c.id  not in(
    select id
    from  customer
    where referee_id=2
)

select name 
from customer 
where referee_id != 2 or referee_id is null

6.2016年的投资

select round(sum(TIV_2016),2) as TIV_2016
from (
select *,
    count(TIV_2015) over(partition by TIV_2015) as cnt1,
    count(TIV_2015) over(partition by LAT ,LON) as cnt2
from insurance 
) as a
where a.cnt1>1 and a.cnt2=1

7.订单最多的客户

SELECT CUSTOMER_NUMBER
FROM ORDERS
GROUP BY 1
ORDER BY COUNT(*) DESC
LIMIT 1;

8.好友申请 I：总体通过率

select round(
    ifnull(
        (select count(*) from(
            select distinct requester_id, accepter_id
            from request_accepted)
        as A)
        /
        (select count(*) from(
            select distinct sender_id, send_to_id
            from friend_request)
        as B)
    , 0)
, 2) as accept_rate;

最后

以上就是真实自行车为你收集整理的【数据库与SQL】力扣刷题SQL篇（5）rows between1.员工奖金2.查询回答率最高的问题3.查询员工的累计薪水4.统计各专业学生人数5. 寻找用户推荐人6.2016年的投资7.订单最多的客户8.好友申请 I：总体通过率的全部内容，希望文章能够帮你解决【数据库与SQL】力扣刷题SQL篇（5）rows between1.员工奖金2.查询回答率最高的问题3.查询员工的累计薪水4.统计各专业学生人数5. 寻找用户推荐人6.2016年的投资7.订单最多的客户8.好友申请 I：总体通过率所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。