牛客网61道sql题目个人题解（未完待续）

71. 牛客每天有很多人登录，请你统计一下牛客每个日期新用户的次日留存率。

select d1.date, 
 case
     when count(f1.user_id)=0 then 0.000
     else round(count(l1.user_id)*1.0/count(f1.user_id), 3)
 end as p 
from login as d1
--(select distinct date from login) as d1
left join 
(
    select user_id, min(date) as fday
    from login
    group by user_id
) as f1 on d1.date=f1.fday
left join
  login as l1 on date(f1.fday, '+1 day') = l1.date and f1.user_id=l1.user_id
 group by d1.date
 order by d1.date

---

69. 牛客每天都有很多人登陆，请你统计一下牛客新登陆用户的次日成功的留存率。

解答：首先需要理清题意，这里的留存率是数据库中的所有数据的留存率，而不是每日留存率，因此这里的计算公式为：留存率=数据库中新登陆用户的次日登陆人数/数据库中的总用户数。在知道题目后，接下来的就比较简单了，只要分别求出式子中的两个数，便可以得出答案。
第一步，求出数据库中的总用户数

-- 第一步，计算数据库中的总用户数
select count(distinct user_id)
from login

-- 第二步，计算数据库中新登陆用户在次日仍旧登陆的人数
select count(distinct l2.user_id)
from (
	select user_id, min(date) as min_date
	from login
	group by user_id
) as l1
left join login l2 
on date(l1.date, '+1 day')=date(l2.date) and l1.user_id=l2.user_id)

-- 第三步，将上面两步得到的结果相除即可,注意保留小数点后3位，这里用round函数
select round(count(distinct l2.user_id)/count(distinct l1.user_id), 3) as p
from (
	select user_id, min(date) as min_date
	from login
	group by user_id
) as l1
left join login l2 
on date(l1.date, '+1 day')=date(l2.date) and l1.user_id=l2.user_id)

代码中涉及的函数说明：

• round(column_name, decimals): 该函数用于把数值字段(column_name)舍入为指定的小数位数(decimals)。如：round(num, 3)表示将列num保留3位小数。w3school中关于该函数的解释
• date(column_name, ‘+1 day’): 表示将列 column_name 的后一天，这是sqlite中的用法，在sql server或MySQL中可以使用DATEDIFF来表示两个字段的差，即DATEDIFF(datepart, date1, date2)来表示以datepart指定的方式，返回date2-date1的差，如本题中可表示为: datediff(d, l2.date, l1.date)=1 w3school中关于Date函数的说明

---

63. 请你根据上表，输出通过的题目的排名，通过题目个数相同的，排名相同，此时按照id升序排列.

解答：方法一：不适用窗口函数，利用两表联立

select p1.id, p1.number, count(distinct p2.number) as 'rank'
from passing_number p1
left join passing_number p2 on p1.number<=p2.number
group by p1.id
order by rank, p1.id

方法二：利用窗口函数dense_rank() over()

select id, number, dense_rank() over(order by number desc) as 'rank'
from passing_number
order by rank, id

---

60. 题目：按照salary的累计和running_total，其中running_total为前N个当前( to_date = ‘9999-01-01’)员工的salary累计和，其他以此类推。

解答：
方法一：利用窗口函数sum() over()

select emp_no, salary, sum(salary) over(order by emp_no) as running_total
from salaries
where to_date='9999-01-01'

方法二：利用条件查询

select s1.emp_no, s1.salary, sum(s2.salary) as running_total
from salaries s1
left join salaries s2 on s1.emp_no>=s2.emp_no
where s1.to_date = '9999-01-01' and s2.to_date='9999-01-01'
group by s1.emp_no;

---

56. 题目：获取所有员工的emp_no、部门编号dept_no以及对应的bonus类型btype和received，没有分配奖金的员工不显示对应的bonus类型btype和received.

解答：如果只用表dept_emp和表emp_bonus的话，只使用左连接亦可通过

select e.emp_no AS 'e.emp_no', de.dept_no, eb.btype, eb.received
from employees e
inner join dept_emp de on e.emp_no=de.emp_no
left join emp_bonus eb on e.emp_no=eb.emp_no

---

55. 分页查询employees表，每5行一页，返回第2页的数据
    解答：考察limit的使用

• limit x,y; 表示跳过前x条数据后，再读取前y条数据
• limit y offset x; 表示跳过前x条数据后，再读取前y条数据

select *
from employees
limit 5,5

---

54. 题目：查找排除最大、最小salary之后的当前(to_date = ‘9999-01-01’ )员工的平均工资avg_salary。
    解答：去除最大和最小值即可

select avg(salary) as 'avg_salary'
from salaries
where to_date='9999-01-01'
and salary > (select min(salary) from salaries where to_date='9999-01-01')
and salary < (select max(salary) from salaries where to_date='9999-01-01')

---

53. 按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果
    解答：考察函数group_concat的使用，该函数用于将分组后同一分组的某一字段的值连接在一起

**用法：**group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator ‘分隔符’] )
举个例子： group_concat(id order by name separator ‘;’) – 将分组后同一组的id按找name排序连接在一起，分隔符为;

select dept_no, group_concat(emp_no) as 'employees'
from dept_emp
group by dept_no

52. 获取Employees中的first_name，查询按照first_name最后两个字母，按照升序进行排列
    解答：本题的难点在于如何提取出first_name的最后两个字母，这可以用substr函数实现(该函数在不同的数据库中有不同的名字：MySQL: substr(), substring(); Oracle: substr(); SQL Server: substring(). )

**函数用法：**substr(str, pos, len) 参文1：百度百科, 参文2：1keydata
意思是由 <str> 中的第<pos> 位置开始，选出接下去的 <len> 个字符。
注意1：len为可选字段，若len省略，则表示从第pos位置到字符串结束。
注意2：pos为必选字段，可以是正数（表示正数第pos个字符开始），也可以是负数（表示倒数第|pos|个字符），还可以是0（表示字符串的第一个字符）

select e.first_name from (
    select first_name, substr(first_name, -2, 2) as tmp
    from employees
) e
order by e.tmp;

---

51. 查找字符串’10,A,B’ 中逗号’,'出现的次数cnt。
    解答：利用length函数和replace函数即可
    函数说明：length(str) 获取字符串str的长度
    replace(str, str1, str2) 将str中的所有str1用str2代替

select length("10,a,b")-length(replace("10,a,b", ",", "")) as cnt

---

49-50. 略

---

48. 请你写出更新语句，将所有获取奖金的员工当前的(salaries.to_date=‘9999-01-01’)薪水增加10%。(emp_bonus里面的emp_no都是当前获奖的所有员工)

update salaries
set salary = salary*1.1
where emp_no in (
    select emp_no from emp_bonus
    where to_date="9999-01-01"
)

---

45. 将titles_test表名修改为titles_2017。

alter table titles_test rename to titles_2017

---

44. 将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变，使用replace实现。

UPDATE titles_test 
SET emp_no = REPLACE(emp_no,10001,10005) 
WHERE id = 5

---

42. 删除emp_no重复的记录，只保留最小的id对应的记录。

delete from titles_test
where id not in(
	select * from(
    	select min(id)
    	from titles_test
    	group by emp_no
    )
)

---

28. 查找描述信息(film.description)中包含robot的电影对应的分类名称(category.name)以及电影数目(count(film.film_id))，而且还需要该分类包含电影总数量(count(film_category.category_id))>=5部

select c.name, count(f.film_id)
from category c, film f, film_category fc
where f.film_id=fc.film_id and c.category_id=fc.category_id 
and f.description like '%robot%'
and c.category_id in 
    (select category_id 
     from film_category 
     group by category_id 
     having count(film_id)>=5)
group by c.category_id

---

23. 对所有员工的当前(to_date=‘9999-01-01’)薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_no升序排列
    解答：方法1：利用窗口函数dense_rank() over()

select emp_no, salary, dense_rank() over(order by salary desc) as rank
from salaries
where to_date='9999-01-01'
order by rank, emp_no

方法2：利用count()

select s1.emp_no, s1.salary, count(distinct s2.salary) as rank
from salaries as s1, salaries as s2
where s1.to_date = '9999-01-01' and s2.to_date = '9999-01-01' and s1.salary <= s2.salary
group by s1.emp_no
order by s1.salary desc, s1.emp_no

---

题目来源：
牛客网：sql实战

如有错误，请指出，我会及时进行修改的。