牛客数据库SQL实战 51-60（substr切割字符串、group_concat组拼接、limit_offset分页、exists条件成立判断、case分支、表的复用）

最新推荐文章于 2022-04-18 22:45:15 发布
最新推荐文章于 2022-04-18 22:45:15 发布
阅读量537 收藏 1
点赞数
分类专栏： 数据库 文章标签： sql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_43508544/article/details/106115274
版权

文章目录


欢迎访问笔者个人技术博客： http://rukihuang.xyz/
牛客网数据库SQL实战

51* 获取Employees中的first_name

51.1 题目描述

获取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`));

输出格式：

first_name
Chirstian
Tzvetan
Bezalel
Duangkaew
Georgi
Kyoichi
Anneke
Sumant
Mary
Parto
Saniya

51.2 题解 substr

select 
	first_name
from 
	employees
order by
	substr(first_name, length(first_name)-1,length(first_name))
  • 本题考查 substr(X,Y,Z)或substr(X,Y)函数的使用。其中X是要截取的字符串。Y是字符串的起始位置（注意第一个字符的位置为1，而不为0），取值范围是±(1~length(X))，当Y等于length(X)时，则截取最后一个字符；当Y等于负整数-n时，则从倒数第n个字符处截取。Z是要截取字符串的长度，取值范围是正整数，若Z省略，则从Y处一直截取到字符串末尾；若Z大于剩下的字符串长度，也是截取到字符串末尾为止。

52* 按照dept_no进行汇总

52.1 题目描述

按照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`));

输出格式:

dept_noemployees
d00110001,10002
d00210006
d00310005
d00410003,10004
d00510007,10008,10010
d00610009,10010

52.2 题解 group_concat(字段名,分隔符)

select 
	dept_no
	,group_concat(emp_no, ",")
from 
	dept_emp
group by
	dept_no
  • 本题要用到SQLite的聚合函数group_concat(X,Y)，其中X是要连接的字段，Y是连接时用的符号，可省略，默认为逗号。此函数必须与 GROUP BY 配合使用。此题以 dept_no 作为分组，将每个分组中不同的emp_no用逗号连接起来（即可省略Y）。

53 查找排除当前最大、最小salary之后的员工的平均工资avg_salary

53.1 题目描述

查找排除当前最大、最小salary之后的员工的平均工资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`));

输出格式:

avg_salary
69462.5555555556

53.2 题解

  • 正常逻辑，OJ过不了
select
	avg(salary) as avg_salary
from 
	salaries
where
	salary not in(
		-- 1 找到最高工资和最小工资的salary
		(select	
			max(salary)
		from 
			salaries
		where 
			to_date = '9999-01-01') -- OJ中需要把时间条件删除，才能过
		,(select
			min(salary)
		from 
			salaries
		where 
			to_date = '9999-01-01') -- OJ中需要把时间条件删除，才能过
	)
and
	to_date = '9999-01-01'
  • OJ通过题解
select
	avg(salary) as avg_salary
from 
	salaries
where
	salary not in(
		-- 1 找到最高工资和最小工资的salary
		(select	
			max(salary)
		from 
			salaries
		)
		,(select
			min(salary)
		from 
			salaries
		)
	)
and
	to_date = '9999-01-01'

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

54.1 题目描述

分页查询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`));

54.2 题解 limit offset

  • 方法一：利用 LIMIT 和 OFFSET 关键字。LIMIT 后的数字代表返回几条记录，OFFSET 后的数字代表从第几条记录开始返回==（第一条记录序号为0）==，也可理解为跳过多少条记录后开始返回。
select 
	*
from 
	employees
limit 
	5 -- 返回5条数据
offset
    5 -- 从第6条数据开始返回
  • 只利用 LIMIT 关键字。注意：在 LIMIT X,Y 中，Y代表返回几条记录，X代表从第几条记录开始返回（第一条记录序号为0），切勿记反。
select 
	*
from 
	employees
limit 
	5,5 -- 从第6条记录开始，返回4条数据（第1条数据是第0条数据）

55 获取所有员工的emp_no

55.1 题目描述

获取所有员工的emp_no、部门编号dept_no以及对应的bonus类型btype和received ，没有分配具体的员工不显示

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 `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
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 `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`));

create table emp_bonus(
emp_no int not null,
received datetime not null,
btype smallint not null);

输出格式:

e.emp_nodept_nobtypereceived
10001d00112010-01-01
10002d00122010-10-01
10003d00432011-12-03
10004d00412010-01-01
10005d003
10006d002
10007d005
10008d005
10009d006
10010d005
10010d006

55.2 题解

select
	d.emp_no
	,d.dept_no
	,e.btype
	,e.recevied -- 这个字段是题目打错的，我日，建议击毙
from 
	dept_emp d
left join
	emp_bonus e
on 
	d.emp_no = e.emp_no
  • 正常题解：本题严谨的思路为，先将 employees与dept_emp 用 INNER JOIN 连接，挑选出分配了部门的员工，再用 LEFT JOIN 连接 emp_bonus（在前面的题中可看到此表），分配了奖金的员工显示奖金类型和授予时间，没分配奖金的员工则不显示。
SELECT 
	em.emp_no
	, de.dept_no
	, eb.btype
	, eb.recevied
FROM 
	employees AS em 
INNER JOIN 
	dept_emp AS de
ON 
	em.emp_no = de.emp_no
LEFT JOIN 
	emp_bonus AS eb 
ON 
	de.emp_no = eb.emp_no

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

56.1 题目描述

使用含有关键字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`));

输出格式:

emp_nobirth_datefirst_namelast_namegenderhire_date
100111953-11-07MarySluisF1990-01-22

56.2 题解 exists

  • 不使用exists
select 
    * 
from 
    employees
where 
    emp_no not in (
        select 
            emp_no
        from 
            dept_emp d
    )
  • 使用exists
select 
    * 
from 
    employees
where not exists (
    select 
        emp_no
    from 
    	dept_emp 
    where 
   		emp_no = employees.emp_no -- 外面的表一定要和存在条件的子表差生关联
)

SELECT 
	* 
FROM 
	employees 
WHERE emp_no NOT exists (
    SELECT 
    	emp_no 
   	FROM 
    	dept_emp)  -- 过不了，在 employees 中没有一条记录能使 (SELECT emp_no FROM dept_emp) 不成立。(SELECT emp_no FROM dept_emp) 没有跟 emplotees 产生联系，因为无论选中 employees 中的哪条记录，(SELECT emp_no FROM dept_emp) 都成立

57 获取employees中的行数据，且这些行也存在于emp_v中

57.1 题目描述

存在如下的视图：

create view emp_v as select * from employees where emp_no >10005;
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`));

获取employees中的行数据，且这些行也存在于emp_v中。注意不能使用intersect关键字。
输出格式:

emp_nobirth_datefirst_namelast_namegenderhire_date
100061953-04-20AnnekePreusigF1989-06-02
100071957-05-23TzvetanZielinskiF1989-02-10
100081958-02-19SaniyaKalloufiM1994-09-15
100091952-04-19SumantPeacF1985-02-18
100101963-06-01DuangkaewPiveteauF1989-08-24
100111953-11-07MarySluisF1990-01-22

57.2 题解

-- 这题真的智障
select 
    * 
from
    emp_v

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

58.1 题目描述

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

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,
recevied 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’
输出格式:

emp_nofirst_namelast_namebtypesalarybonus
10001GeorgiFacello1889588895.8
10002BezalelSimmel27252714505.4
10003PartoBamford34331112993.3
10004ChirstianKoblick1740577405.7

58.2 题解 case…when…then…else…end

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

59* 统计salary的累计和running_total

59.1 题目描述

按照salary的累计和running_total，其中running_total为前两个员工的salary累计和，其他以此类推。 具体结果如下Demo展示。。

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_nosalaryrunning_total
100018895888958
1000272527161485
1000343311204796
1000474057278853
1000594692373545
1000643311416856
1000788070504926
1000995409600335
1001094409694744
1001125828720572

59.2 题解

本题的思路为复用 salaries 表进行子查询，最后以 s1.emp_no 排序输出求和结果。

1、输出的第三个字段，是由一个 SELECT 子查询构成。将子查询内复用的 salaries 表记为 s2，主查询的 salaries 表记为 s1，当主查询的 s1.emp_no 确定时，对子查询中不大于 s1.emp_no 的 s2.emp_no 所对应的薪水求和

2、注意是对员工当前的薪水求和，所以在主查询和子查询内都要加限定条件 to_date = ‘9999-01-01’

select 
	s2.emp_no
	,s2.salary
	,(
		select 
			sum(salary)
		from 
			salaries s1
		where 
			s1.emp_no <= s2.emp_no -- 复用两张表
		and 
			s1.to_date = '9999-01-01'
	) as running_total
from 
	salaries s2
where 
	s2.to_date = '9999-01-01'

60* 对于employees表中，给出奇数行的first_name

60.1 题目描述

对于employees表中，给出奇数行的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`));

输出格式:

first_name
Georgi
Chirstian
Anneke
Tzvetan
Saniya
Mary

60.2 题解

SELECT 
	E1.first_name 
FROM 
	employees E1
WHERE (
	SELECT 
		COUNT(*)  -- 序号 复用2张表，先对first_name进行排序，count(*)就是序号
	FROM 
		employees E2 
	WHERE 
		E1.first_name >= E2.first_name) % 2 = 1;
文泽路小男孩
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL分组聚合group_concat + substr_index
weixin_30827565的博客
06-24 168
场景：给予一张商品售卖表，表中数据为商品的售卖记录，假设表中数据是定时脚本插入的，每个时间段的商品售卖数量不同，根据此表找各个商品的最多售卖数量的数据。1、数据表 CREATE TABLE `goods_sell` ( `id` int(11) NOT NULL AUTO_INCREMENT, `goods_id` int(10) unsigned NOT NULL DEFAUL...
SQL_基础指令：|| ’连接‘ ||，length(), substr(), group_concat(), limit ... offset
weixin_42836351的博客
07-30 786
1、将employees表的所有员工的last_name和first_name拼接起来作为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 N...
SQL中concat，left，upper，lower，substr，group_concat函数的使用
qq_53946134的博客
04-18 701
SQL中的concat函数：用于将两个字符串连接起来 select concat(first,second) as name from table_name; left()函数：从字符串左侧提取多个字符； select row_name,left(row_name,2) as a from table_name upper(s)函数和ucase函数，将字符串s中的字母字符全部转化成大写字母 lower(s)函数和lcase(s)函数，将字符串s中的字母字符全部转化成小写字母substr s
MySQL sql执行顺序与 group by 根据虚拟字段分组
weixin_44259638的博客
02-21 661
SQL执行顺序： FROM /JOIN WHERE GROUP BY (可使用select中的别名) HAVING SELECT DISTINCT ORDER BY LIMIT/OFFSET 有表如下 现在有这样一个需求 查某个月每天 humidity的的平均值 怎么实现呢 每天就是年月日是固定的 根据年月日来分组就可以 但是没有年月日怎么办呢？ 那就造一个呗 SELECT SUBSTRING(create_time,1,10) AS time,ROUND(AVG(a.humidity),1) as
DBA-oracle随笔-拼接sql语句（substr,instr和 ||）用法
weixin_45112130的博客
06-05 1253
一.先介绍一下最简单的拼接写法(用 ||连接字符串) 老板有一个需求给出每一个职员所在的部门编号，部门名称，员工编号，员工姓名，薪水和职称。 先看一下不拼接的查询结果 SQL> select a.EMPNO,a.ENAME,a.JOB,a.SAL,a.DEPTNO,b.DNAME from emp a,dept b where a.DEPTNO=b.DEPTNO; EMPNO ENAME JOB SAL DEPTNO DNAME ----------
牛客网站数据库SQL实战题目
最新发布
05-17
题目设计紧密结合实际业务需求，模拟真实工作环境中的数据库操作。 用户需要根据具体业务要求编写高效的SQL查询，解决数据处理和分析问题。 逐步提升： 题目难度从基础到高级逐步递进，适合不同水平的学习者。 初学...
Python牛客初级项目实战视频课程-03 数据库交互以及flask-sqlalchem.mp4
05-19
Python牛客初级项目实战视频课程-03 数据库交互以及flask-sqlalchem.mp4
牛客题目网上的数据库SQL实战题目，包含详细的解题思路、代码实现和优化建议，适合准备SQL面试和实际开发的学习者
05-16
### 牛客题目网数据库SQL实战汇总 #### 资源概述 本资源包汇总了牛客题目网上精选的数据库SQL实战题目，旨在帮助学习者提升SQL技能，准备SQL相关面试，并在实际开发中应用这些技能。资源内容详尽，包括每道题目的...
牛客SQL刷题 Day-07
12-14
2、将employees表的所有员工的last_name和first_name拼接起来作为Name，中间以一个空格区分 3、创建一个actor表，包含如下列信息 4、对于表actor批量插入如下数据 5、对于表actor批量插入如下数据,如果数据已经存在...
牛客独家春招实习备战手册-技术篇_春招_简历_
09-30
牛客春招指南，里面包含了很多的干货，包括春招介绍，简历建议，以及公司真题等。
elasticsearch对字段进行截取字符串进行聚合
12-04 1万+
文章目录最终dsl原始需求遇到的坑资料 最终dsl { "query": { "bool": { "filter": [ { "range": { "sendDate": { "gte": "2019-01-08T16:00:00Z", "lte": "201...
Caffe 代码解读之全连接层concat layer
热门推荐
bailufeiyan的博客
03-13 2万+
今天，我们看一下caffe的拼接层，即将两个或多个layer进行拼接。 包括前向传播、反向传播、reshape，axis、CHECK_**、count函数等
group_concat的使用
百卷
09-14 4711
  在我们使用数据库时，会碰到这样一种情况：同一个Name的人有两条数据，但是他们的其他数据并不相同，而我们所需要的是同一个Name的人的Score1集合，即需要获得Name为A的所有的Score1并且希望将其拼接为一个字段，这里我们就需要用到GROUP_CONCAT。    函数语法如下：group_concat( 要连接的字段 )  [Order BY 排序字段 ASC/DESC]   [...
Group_concat介绍与例子
从未完美过的博客
06-22 2145
有一个需求就是对于多行的数据在一行显示完整语法如下group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])[sql] view plain copySELECT * FROM testgroup  表结构与数据如上现在的需求就是每个id为一行 在前台每行显示该id所有分数group_concat 上场！！！...
mysql group_concat的截断问题
胡老汉的博客
12-31 621
show variables like 'group_concat_max_len'; 默认1024，超过就会截断 解决办法： set group_concat_max_len=2048; 通常拼接的内容不要过长,设置适合的长度，如果拼接内容很长的话，建议在程序中拼接 ...
MYSQL中group_concat有长度限制,默认1024
02-23 8266
在mysql中，有个函数叫“group_concat”，平常使用可能发现不了问题，在处理大数据的时候，会发现内容被截取了， 其实MYSQL内部对这个是有设置的，默认不设置的长度是1024，如果我们需要更大，就需要手工去修改配置文件。 详细说明如下： 用了group_concat后，select里如果使用了limit是不起作用的. 用group_concat连接字段的时候是有长度限制的，并不是有多少连多少。但你可以设置一下。 使用group_concat_max_len系统变量，你可以设置允许的最大长度。
sqlite截取字符串的函数使用实例
yqj234的专栏
03-13 3415
select invoice_batch_no, substr(invoice_batch_no,instr(invoice_batch_no,'-')) from inv_invoice --读取-分隔符，前面的字符，123-ABC返回123 select invoice_batch_no, substr(invoice_batch_no,1,instr(invoice_batch_no...
59. 获取有奖金的员工相关信息
chenchenchenchenying的博客
10-26 1018
题目描述 获取有奖金的员工相关信息。 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, `h...
group_concat牛客例题
09-22
group_concat函数是MySQL中的一个聚合函数，用于将多个行的值连接成一个字符串。在牛客网例题中，group_concat函数被用来将不同的日期和标签按照特定的分隔符连接起来。 在引用的例子中，通过使用group_concat函数，将start_time的日期和tag连接起来，并使用分号作为分隔符。可以使用以下语句来实现： group_concat(distinct CONCAT(DATE_FORMAT(start_time, '%Y-%m-%d'),':',tag) separator ';') as detail 这个语句将会返回一个字符串，其中包含了所有的日期和标签的组合。每个组合由日期和标签用冒号连接，不同的组合之间用分号分隔。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值