常见sql总结一

1、牛客每个人最近的登录日期(五)

描述

牛客每天有很多人登录，请你统计一下牛客每个日期新用户的次日留存率。
有一个登录(login)记录表，简况如下:

请你写出一个sql语句查询每个日期新用户的次日留存率，结果保留小数点后面3位数(3位之后的四舍五入)，并且查询结果按照日期升序排序，上面的例子查询结果如下:

查询结果表明:
2020-10-12登录了3个(user_id为2，3，1)新用户，2020-10-13，只有2个(id为2,1)登录，故2020-10-12新用户次日留存率为2/3=0.667;
2020-10-13没有新用户登录，输出0.000;
2020-10-14登录了1个(user_id为4)新用户，2020-10-15，user_id为4的用户登录，故2020-10-14新用户次日留存率为1/1=1.000;

2020-10-15没有新用户登录，输出0.000;

(注意:sqlite里查找某一天的后一天的用法是:date(yyyy-mm-dd, ‘+1 day’)，sqlite里1/2得到的不是0.5，得到的是0，只有1*1.0/2才会得到0.5)

SELECT date,IFNULL(ROUND(SUM(CASE WHEN (user_id,date) IN
(SELECT user_id,DATE_ADD(date,INTERVAL -1 DAY) FROM login)
AND (user_id,date) IN (SELECT user_id,MIN(date) FROM login GROUP BY user_id)
THEN 1 ELSE 0 END)/
SUM(CASE WHEN (user_id,date) IN (SELECT user_id,MIN(date) FROM login GROUP BY user_id)
THEN 1 ELSE 0 END),3),0) AS p
FROM login
GROUP BY date
ORDER BY date;

2、获取员工其当前的薪水比其manager当前薪水还高的相关信息

描述

有一个，部门关系表dept_emp简况如下:

有一个部门经理表dept_manager简况如下:

有一个薪水表salaries简况如下:

获取员工其当前的薪水比其manager当前薪水还高的相关信息，

第一列给出员工的emp_no，
第二列给出其manager的manager_no，
第三列给出该员工当前的薪水emp_salary,
第四列给该员工对应的manager当前的薪水manager_salary

以上例子输出如下:

selecta.emp_no,b.emp_no as manager_no,c.salary as emp_salary,d.salary as manager_dalary
fromdept_emp aleft join dept_manager b on a.dept_no = b.dept_noleft join salaries c on a.emp_no = c.emp_noleft join salaries d on b.emp_no = d.emp_no
havingc.salary > d.salary

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

UPDATEtitles_test
setemp_no = REPLACE (emp_no, "10001", "10005");

4、获取当前薪水第二多的员工的emp_no以及其对应的薪水sal

链接：https://www.nowcoder.com/questionTerminal/8d2c290cc4e24403b98ca82ce45d04db
来源：牛客网

有一个薪水表salaries简况如下:

请你获取薪水第二多的员工的emp_no以及其对应的薪水salary，

若有多个员工的薪水为第二多的薪水，则将对应的员工的emp_no和salary全部输出，并按emp_no升序排序。

selectemp_no,salary
from(selectemp_no,salary,rank() over (order bysalary desc) as t_rankfromsalaries) as T
whereT.t_rank = 2

5、获取每个部门中当前员工薪水最高的相关信息

描述

有一个员工表dept_emp简况如下:

有一个薪水表salaries简况如下:

获取每个部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门编号dept_no升序排列，以上例子输出如下:

selectdept_no,emp_no,salary as maxSalary
from(selecta.dept_no,a.emp_no,b.salary,row_number() over (partition bya.dept_noorder bysalary desc) as tmp_nofromdept_emp aleft join salaries b on a.emp_no = b.emp_no) as T
whereT.tmp_no = 1

6、查找入职员工时间排名倒数第三的员工所有信息

要求可能有并列的存在

select*
fromemployees
wherehire_date = (selecthire_datefromemployeesgroup byhire_dateorder byhire_date desclimit2, 1)