牛客网sql练习笔记（三）

于 2021-09-13 19:10:41 发布
阅读量260 收藏
点赞数
分类专栏： SQL学习笔记 文章标签： sql 数据库 mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_43656500/article/details/120261638
版权

第46题

描述

在audit表上创建外键约束，其emp_no对应employees_test表的主键id。
(以下2个表已经创建了)

CREATE TABLE employees_test(
	ID INT PRIMARY KEY NOT NULL,
	NAME TEXT NOT NULL,
	AGE INT NOT NULL,
	ADDRESS CHAR(50),
	SALARY REAL
);

CREATE TABLE audit(
	EMP_no INT NOT NULL,
	create_date datetime NOT NULL
);
解答
ALTER TABLE audit
ADD CONSTRAINT FOREIGN KEY (emp_no)
REFERENCES employees_test(id)

第51题

描述

查找字符串’10,A,B’ 中逗号’,'出现的次数cnt。

解答
  • “10,A,B” 中的 逗号用空串替代， 变成 “10AB”
  • 原来串的长度 - 替换之后的串的长度，就是被替换的逗号的个数
select length("10,A,B") - length(replace("10,A,B",","."")) as cxc

第52题

描述

获取Employees中的first_name，查询按照first_name最后两个字母，按照升序进行排列

CREATE TABLE `employees` (
	`emp_no` int(11) NOT NULL,
	`birth_date` date NOT NULL,
	`first_name` varchar(14) NOT NULL,
	`last_name` varchar(16) NOT NULL,
	`gender` char(1) NOT NULL,
	`hire_date` date NOT NULL,
	PRIMARY KEY (`emp_no`));
解答
SELECT first_name FROM employees ORDER BY RIGHT(first_name,2)

RIGHT返回从最右边开始指定长度的字符串。同理LEFT函数就是返回从最左边开始的指定长度字符串。

第53题

描述

按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees

CREATE TABLE `dept_emp` (
	`emp_no` int(11) NOT NULL,
	`dept_no` char(4) NOT NULL,
	`from_date` date NOT NULL,
	`to_date` date NOT NULL,
	PRIMARY KEY (`emp_no`,`dept_no`));
解答
SELECT dept_no,GROUP_CONCAT(emp_no) as employees
FROM dept_emp group by dept_no

聚合函数group_concat（X，Y），其中X是要连接的字段，Y是连接时用的符号，可省略，默认为逗号。此函数必须与GROUP BY配合使用。

第54题

描述

查找排除最大、最小salary之后的当前(to_date = ‘9999-01-01’ )员工的平均工资avg_salary。

CREATE TABLE `salaries` ( `emp_no` int(11) NOT NULL,
	`salary` int(11) NOT NULL,
	`from_date` date NOT NULL,
	`to_date` date NOT NULL,
	PRIMARY KEY (`emp_no`,`from_date`));
INSERT INTO salaries VALUES(10001,85097,'2001-06-22','2002-06-22');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,43699,'2000-12-01','2001-12-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,70698,'2000-11-27','2001-11-27');
INSERT INTO salaries VALUES(10004,74057,'2001-11-27','9999-01-01');
解答

我把题理解错了，我的答案是

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

这样的平均值是，to_date=‘999-01-01’里去掉最小最大值后的平均值
题目要求的是，查找排除最大、最小salary之后的当前(to_date = ‘9999-01-01’ )

SELECT (SUM(salary) - MAX(salary) - MIN(salary)) / (COUNT(1)-2) as avg_salary
FROM salaries where to_date = '9999-01-01';

第55题

描述

分页查询employees表，每5行一页，返回第2页的数据

CREATE TABLE `employees` (
	`emp_no` int(11) NOT NULL,
	`birth_date` date NOT NULL,
	`first_name` varchar(14) NOT NULL,
	`last_name` varchar(16) NOT NULL,
	`gender` char(1) NOT NULL,
	`hire_date` date NOT NULL,
	PRIMARY KEY (`emp_no`));
解答
SELECT * from employees LIMIT 5,5

LIMIT ： LIMIT X,Y
Y ：返回几条记录
X：从第几条记录开始返回（第一条记录序号为0，默认为0）

第57题

描述

使用含有关键字exists查找未分配具体部门的员工的所有信息。

CREATE TABLE `employees` (
	`emp_no` int(11) NOT NULL,
	`birth_date` date NOT NULL,
	`first_name` varchar(14) NOT NULL,
	`last_name` varchar(16) NOT NULL,
	`gender` char(1) NOT NULL,
	`hire_date` date NOT NULL,
	PRIMARY KEY (`emp_no`));
CREATE TABLE `dept_emp` (
	`emp_no` int(11) NOT NULL,
	`dept_no` char(4) NOT NULL,
	`from_date` date NOT NULL,
	`to_date` date NOT NULL,
	PRIMARY KEY (`emp_no`,`dept_no`));
解答
select * from employees e
where not exists
(select emp_no from dept_emp d where d.emp_no = e.emp_no);

第59题

描述

获取有奖金的员工相关信息。

CREATE TABLE `employees` (
		`emp_no` int(11) NOT NULL,
		`birth_date` date NOT NULL,
		`first_name` varchar(14) NOT NULL,
		`last_name` varchar(16) NOT NULL,
		`gender` char(1) NOT NULL,
		`hire_date` date NOT NULL,
		PRIMARY KEY (`emp_no`));
CREATE TABLE `dept_emp` (
		`emp_no` int(11) NOT NULL,
		`dept_no` char(4) NOT NULL,
		`from_date` date NOT NULL,
		`to_date` date NOT NULL,
		PRIMARY KEY (`emp_no`,`dept_no`));
create table emp_bonus(
		emp_no int not null,
		received datetime not null,
		btype smallint not null);
CREATE TABLE `salaries` (
		`emp_no` int(11) NOT NULL,
		`salary` int(11) NOT NULL,
		`from_date` date NOT NULL,
		`to_date` date NOT NULL, 
		PRIMARY KEY (`emp_no`,`from_date`));

给出emp_no、first_name、last_name、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus。 bonus类型btype为1其奖金为薪水salary的10%，btype为2其奖金为薪水的20%，其他类型均为薪水的30%。 当前薪水表示to_date=‘9999-01-01’
在这里插入图片描述

解答
SELECT e.emp_no,e.first_name,e.last_name,eb.btype,s.salary,
(case 
     when btype = 1 then salary*0.1
     when btype = 2 then salary*0.2
     else salary*0.3 end) as bonus
from employees e
join emp_bonus eb
using(emp_no)
join salaries s
using(emp_no)
where to_date = '9999-01-01'

第60题

描述

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

CREATE TABLE `salaries` ( `emp_no` int(11) NOT NULL,
		`salary` int(11) NOT NULL,
		`from_date` date NOT NULL,
		`to_date` date NOT NULL,
		PRIMARY KEY (`emp_no`,`from_date`));

在这里插入图片描述

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

窗口函数也称OLAP函数（online analytical processing),意思对数据库进行实时分析：

<窗口函数> OVER ([PARTITION BY <列清单>]
            ORDER BY <排序用列清单>)

窗口函数分为两类：

  1. 聚合函数：sum,min,max,avg,count
  2. 专用窗口函数：rank,dense_rank,row_number
    在这里插入图片描述

第61题

描述

对于employees表中，输出first_name排名(按first_name升序排序)为奇数的first_name

CREATE TABLE `employees` (
		`emp_no` int(11) NOT NULL,
		`birth_date` date NOT NULL,
		`first_name` varchar(14) NOT NULL,
		`last_name` varchar(16) NOT NULL,
		`gender` char(1) NOT NULL,
		`hire_date` date NOT NULL,
		PRIMARY KEY (`emp_no`));

INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12');
INSERT INTO employees VALUES(10006,'1953-04-20','Anneke','Preusig','F','1989-06-02');
解答
select e1.first_name from employees e1
where (select count(1) from employees e2 
       where e1.first_name>= e2.first_name)%2 = 1

第65题

描述

统计正常用户发送给正常用户邮件失败的概率:
有一个邮件(email)表，id为主键， type是枚举类型，枚举成员为(completed，no_completed)，completed代表邮件发送是成功的，no_completed代表邮件是发送失败的。
在这里插入图片描述
下面是一个用户(user)表，id为主键，is_blacklist为0代表为正常用户，is_blacklist为1代表为黑名单用户
在这里插入图片描述
写一个sql查询，每一个日期里面，正常用户发送给正常用户邮件失败的概率是多少，结果保留到小数点后面3位(3位之后的四舍五入)，并且按照日期升序排序，上面例子查询结果如下:
在这里插入图片描述

解答
select date,round(sum(case when type = 'no_completed' then 1 else 0 end )/
                    count(*),3)
from email 
where send_id in(select id from user where is_blacklist =0)
   and receive_id in (select id from user where is_blacklist =0)
GROUP by date
ORDER by date

第66题 牛客每个人最近的登录日期(一)

描述

牛客每天有很多人登录，请你统计一下牛客每个用户最近登录是哪一天。
有一个登录(login)记录表，简况如下:
在这里插入图片描述
查询每个用户最近一天登录的日子，并且按照user_id升序排序，上面的例子查询结果如下:
在这里插入图片描述

解答
select user_id,max(date)
from login
GROUP BY user_id
order by user_id

第67题 牛客每个人最近的登录日期(二)

描述

牛客每天有很多人登录，请你统计一下牛客每个用户最近登录是哪一天，用的是什么设备.
有一个登录(login)记录表，简况如下:
在这里插入图片描述
用户(user)表，简况如下:
在这里插入图片描述
客户端(client)表，简况如下:
在这里插入图片描述
查询每个用户最近一天登录的日子，用户的名字，以及用户用的设备的名字，并且查询结果按照user的name升序排序，上面的例子查询结果如下:
在这里插入图片描述

解答

错误示例！！！

select u.name u_n,c.name c_n,max(date)
FROM login l join user u on l.user_id = u.id
JOIN client c on c.id = l.client_id
GROUP BY u_n

在这里插入图片描述
对于max不能直接group by ,对应关系会出现混乱

正确方法：

select u.name as u_n,c.name as c_n,date
from (select id,user_id,client_id,a.date from
        (select *,row_number()over(partition by user_id order by date desc) as rn
        from login) a
where rn = 1) l
join user u on l.user_id = u.id
join client c on l.client_id = c.id
group by u_n,c_n
order by u.name

第68题 牛客每个人最近的登录日期(三)

== 连续登录 ==
描述

牛客每天有很多人登录，请你统计一下牛客新登录用户的次日成功的留存率，
有一个登录(login)记录表，简况如下:
在这里插入图片描述
查询新登录用户次日成功的留存率，即第1天登陆之后，第2天再次登陆的概率,保存小数点后面3位(3位之后的四舍五入)，上面的例子查询结果如下:
在这里插入图片描述

解答
SELECT
ROUND(COUNT(DISTINCT user_id)*1.0 / (SELECT COUNT(DISTINCT user_id) FROM login), 3)
FROM login
WHERE (user_id, date) #注册第二天登录
IN
(SELECT user_id, DATE_ADD(MIN(date),INTERVAL 1 DAY) FROM login GROUP BY user_id);

第69题 牛客每个人最近的登录日期(四)

描述

牛客每天有很多人登录，请你统计一下牛客每个日期登录新用户个数，
有一个登录(login)记录表，简况如下:
在这里插入图片描述
查询每个日期登录新用户个数，并且查询结果按照日期升序排序，上面的例子查询结果如下:
在这里插入图片描述

解答
select a.date,sum(case when rn = 1 then 1 else 0 end) 
from(select *,ROW_NUMBER() over(partition by user_id order by l.date) as rn
    from login l
) a
group by a.date
order by a.date

第70题 牛客每个人最近的登录日期(五)

== 连续登录 ==
描述

统计一下牛客每个日期新用户的次日留存率。
有一个登录(login)记录表，简况如下:
在这里插入图片描述
查询每个日期新用户的次日留存率，结果保留小数点后面3位数(3位之后的四舍五入)，并且查询结果按照日期升序排序，上面的例子查询结果如下:
在这里插入图片描述

解答

用到了函数 date_add()：
date_add(date,interval -1 day)可以得出date前一天的日期

select date
    ,ifnull(round(
        (sum(case when 
            #是新用户
            (user_id,date)in
            (select user_id,min(date)from login group by user_id)
             and
             #连续两天登录
            (user_id,date)in 
            (select user_id,date_add(date,interval -1 day)
             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;

第71题 牛客每个人最近的登录日期(六)

描述

统计一下牛客每个用户查询刷题信息，包括: 用户的名字，以及截止到某天，累计总共通过了多少题。 不存在没有登录却刷题的情况，但是存在登录了没刷题的情况，不会存在刷题表里面，有提交代码没有通过的情况，但是会记录在刷题表里，只不过通过数目是0。
有一个登录(login)记录表，简况如下:
在这里插入图片描述
有一个刷题（passing_number)表，简况如下:
在这里插入图片描述
用户(user)表，简况如下:
在这里插入图片描述
查询刷题信息，包括: 用户的名字，以及截止到某天，累计总共通过了多少题，并且查询结果先按照日期升序排序，再按照姓名升序排序，有登录却没有刷题的哪一天的数据不需要输出，上面的例子查询结果如下:
在这里插入图片描述

解答
SELECT u.name,pn.date,sum(number)over(partition by pn.user_id order by pn.date) as ps_num
from login l 
join passing_number pn on l.user_id = pn.user_id and l.date = pn.date
join user u on u.id = l.user_id
order by pn.date,u.name
2023好运来
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值