【SQL】SQL常见面试题总结（4）

最新推荐文章于 2024-09-23 09:43:13 发布
最新推荐文章于 2024-09-23 09:43:13 发布
阅读量1.2k 收藏 24
点赞数 17
分类专栏： 面试题 数据库 文章标签： sql java 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_45683778/article/details/138952563
版权

目录

1、空值处理

1.1、统计有未完成状态的试卷的未完成数和未完成率

描述：

现有试卷作答记录表 exam_record（uid 用户 ID, exam_id 试卷 ID, start_time 开始作答时间, submit_time 交卷时间,score得分），数据如下：

iduidexam_idstart_timesubmit_timescore
1100190012020-01-02 09:01:012020-01-02 09:21:0180
2100190012021-05-02 10:01:012021-05-02 10:30:0181
3100190012021-09-02 12:01:01(NULL)(NULL)

请统计有未完成状态的试卷的未完成数 incomplete_cnt 和未完成率 incomplete_rate。由示例数据结果输出如下：

exam_idincomplete_cntcomplete_rate
900110.333

解释：试卷 9001 有 3 次被作答的记录，其中两次完成，1 次未完成，因此未完成数为 1，未完成率为 0.333（保留 3 位小数）

思路：

这题只需要注意一个是有条件限制，一个是没条件限制的；要么分别查询条件，然后合并；要么直接在 select 里面进行条件判断。

答案：

SELECT exam_id,
			 COUNT(submit_time IS NULL OR NULL) incomplete_cnt,
			 ROUND(COUNT(submit_time IS NULL OR NULL)/COUNT(*),3) complete_rate
FROM exam_record
GROUP BY exam_id
HAVING incomplete_cnt <> 0

在这里插入图片描述

注意：

  1. COUNT(submit_time IS NULL OR NULL):
  • submit_time IS NULL OR NULL 是一个逻辑表达式。submit_time IS NULL 会返回一个布尔值 TRUE 或 FALSE，然后 OR NULL 操作将结果转换为 TRUE 或 NULL。由于 NULL 在 OR 操作中不会影响 TRUE 的结果，所以结果等效于 submit_time IS NULL。
  • 这样，表达式的结果是 TRUE 或 NULL。
  • 然而，COUNT 函数只会计算非 NULL 的值，因此它只会计算 TRUE 的次数。实际上，这意味着它会统计 submit_time 为 NULL 的次数。
  1. COUNT(submit_time IS NULL):
  • submit_time IS NULL 是一个布尔表达式，会返回 TRUE 或 FALSE。
  • 这里 COUNT 函数计算的是真值 TRUE 和 FALSE 的数量，而不是 NULL的数量。
  • 由于 COUNT 只计算非 NULL 的值，这意味着 submit_time IS NULL 返回的所有 TRUE 或 FALSE 值都被计数为 1。因此，它统计的是记录的总数，因为 TRUE 和 FALSE 都不是 NULL。

总结

  • 第一个查询 COUNT(submit_time IS NULL OR NULL) 实际上统计的是 submit_time 列为空的记录数，因为 TRUE 和 NULL 中只有 TRUE 被计数。

  • 第二个查询 COUNT(submit_time IS NULL) 统计的是所有记录的总数，因为布尔表达式 TRUE 或 FALSE 都被认为是非 NULL，因此都被计数。

1.2、0 级用户高难度试卷的平均用时和平均得分

描述：

现有用户信息表 user_info（uid 用户 ID，nick_name 昵称, achievement 成就值, level 等级, job 职业方向, register_time 注册时间），数据如下：

iduidnick_nameachievementleveljobregister_time
11001机器人 1 号100算法2020-01-01 10:00:00
21002机器人 2 号21006算法2020-01-01 10:00:00

试卷信息表 examination_info（exam_id：试卷 ID, tag：试卷类别, difficulty：试卷难度, duration：考试时长, release_time：发布时间），示例数据如下：

idexam_idtagdifficultydurationrelease_time
19001SQLhard602020-01-01 10:00:00
29002SQLeasy602020-01-01 10:00:00
39004算法medium802020-01-01 10:00:00

试卷作答记录表 exam_record（uid 用户 ID,exam_id 试卷 ID, start_time 开始作答时间, submit_time 交卷时间, score 得分） 示例数据如下：

iduidexam_idstart_timesubmit_timescore
1100190012020-01-02 09:01:012020-01-02 09:21:5980
2100190012021-05-02 10:01:01(NULL)(NULL)
3100190022021-02-02 19:01:012021-02-02 19:30:0187
4100190012021-06-02 19:01:012021-06-02 19:32:0020
5100190022021-09-05 19:01:012021-09-05 19:40:0189
6100190022021-09-01 12:01:01(NULL)(NULL)
7100290022021-05-05 18:01:012021-05-05 18:59:0290

请输出每个 0 级用户所有的高难度试卷考试平均用时和平均得分，未完成的默认试卷最大考试时长和 0 分处理。由示例数据结果输出如下：

uidavg_scoreavg_time_took
10013336.7

解释：0 级用户有 1001，高难度试卷有 9001，1001 作答 9001 的记录有 3 条，分别用时 20 分钟、未完成（试卷时长 60 分钟）、30 分钟（未满 31 分钟），分别得分为 80 分、未完成（0 分处理）、20 分。因此他的平均用时为 110/3=36.7（保留一位小数），平均得分为 33 分（取整）

思路：这题用IF是判断的最方便的，因为涉及到 NULL 值的判断。当然 case when也可以，大同小异。这题的难点就在于空值的处理，其他的这些查询条件什么的，我相信难不倒大家。

SELECT uid,
			 ROUND(AVG(new_score)) AS avg_score,
			 ROUND(AVG(time_diff),1) AS avg_time_took
			
FROM (
	SELECT er.uid,
				 IF(er.submit_time IS NOT NULL,TIMESTAMPDIFF(MINUTE,start_time,submit_time),ei.duration) AS time_diff,
				 IF(er.submit_time IS NOT NULL,er.score,0) AS new_score
	FROM exam_record  er
	LEFT JOIN user_info ui ON er.uid = ui.uid
	LEFT JOIN examination_info ei ON er.exam_id = ei.exam_id
	WHERE ui.`level` = 0 AND ei.difficulty = 'hard'
) t
GROUP BY uid

在这里插入图片描述

2、高级条件语句

2.1、筛选限定昵称成就值活跃日期的用户（较难）

描述：

现有用户信息表 user_info（uid 用户 ID，nick_name 昵称, achievement 成就值, level 等级, job 职业方向, register_time 注册时间），数据如下：

iduidnick_nameachievementleveljobregister_time
11001机器人 1 号10002算法2020-01-01 10:00:00
21002机器人 2 号12003算法2020-01-01 10:00:00
31003高达 3 号22005算法2020-01-01 10:00:00
41004机器人 4 号25006算法2020-01-01 10:00:00
51005机器人 5 号30007C++2020-01-01 10:00:00

现有试卷作答记录表 exam_record（uid 用户 ID,exam_id 试卷 ID, start_time 开始作答时间, submit_time 交卷时间, score 得分） 示例数据如下：

iduidexam_idstart_timesubmit_timescore
1100190012020-01-02 09:01:012020-01-02 09:21:5980
3100190022021-02-02 19:01:012021-02-02 19:30:0187
2100190012021-05-02 10:01:01(NULL)(NULL)
4100190012021-06-02 19:01:012021-06-02 19:32:0020
6100190022021-09-01 12:01:01(NULL)(NULL)
5100190022021-09-05 19:01:012021-09-05 19:40:0189
11100290012020-01-01 12:01:012020-01-01 12:31:0181
12100290022020-02-01 12:01:012020-02-01 12:31:0182
13100290022020-02-02 12:11:012020-02-02 12:31:0183
7100290022021-05-05 18:01:012021-05-05 18:59:0290
16100290012021-09-06 12:01:012021-09-06 12:21:0180
17100290012021-09-06 12:01:01(NULL)(NULL)
18100290012021-09-07 12:01:01(NULL)(NULL)
8100390032021-02-06 12:01:01(NULL)(NULL)
9100390012021-09-07 10:01:012021-09-07 10:31:0189
10100490022021-08-06 12:01:01(NULL)(NULL)
14100590012021-02-01 11:01:012021-02-01 11:31:0184
15100690012021-02-01 11:01:012021-02-01 11:31:0184

题目练习表 practice_record（uid 用户 ID, question_id 题目 ID, submit_time 提交时间, score 得分）：

iduidquestion_idsubmit_timescore
1100180012021-08-02 11:41:0160
2100280012021-09-02 19:30:0150
3100280012021-09-02 19:20:0170
4100280022021-09-02 19:38:0170
5100380022021-09-01 19:38:0180

请找到昵称以『机器人』开头『号』结尾、成就值在 1200~2500 之间，且最近一次活跃（答题或作答试卷）在 2021 年 9 月的用户信息。

由示例数据结果输出如下：

uidnick_nameachievement
1002机器人 2 号1200

解释：昵称以『机器人』开头『号』结尾且成就值在 1200~2500 之间的有 1002、1004；

1002 最近一次试卷区活跃为 2021 年 9 月，最近一次题目区活跃为 2021 年 9 月；1004 最近一次试卷区活跃为 2021 年 8 月，题目区未活跃。

因此最终满足条件的只有 1002。

思路：

先根据条件列出主要查询语句

昵称以『机器人』开头『号』结尾: nick_name LIKE "机器人%号"

成就值在 1200~2500 之间：achievement BETWEEN 1200 AND 2500

第三个条件因为限定了为 9 月，所以直接写就行：( date_format( record.submit_time, '%Y%m' )= 202109 OR date_format( pr.submit_time, '%Y%m' )= 202109 )

答案:

SELECT DISTINCT u_info.uid,
                u_info.nick_name,
                u_info.achievement
FROM user_info u_info
LEFT JOIN exam_record record ON record.uid = u_info.uid
LEFT JOIN practice_record pr ON u_info.uid = pr.uid
WHERE u_info.nick_name LIKE "机器人%号"
  AND u_info.achievement BETWEEN 1200
  AND 2500
  AND (date_format(record.submit_time, '%Y%m')= 202109
       OR date_format(pr.submit_time, '%Y%m')= 202109)
GROUP BY u_info.uid

2.2、筛选昵称规则和试卷规则的作答记录（较难）

描述：

现有用户信息表 user_info（uid 用户 ID，nick_name 昵称, achievement 成就值, level 等级, job 职业方向, register_time 注册时间），数据如下：

iduidnick_nameachievementleveljobregister_time
11001机器人1号19002算法2020-01-01 10:00:00
21002机器人2号12003算法2020-01-01 10:00:00
31003机器人3 号 ♂22005算法2020-01-01 10:00:00
41004机器人 4号25006算法2020-01-01 10:00:00
51005机器人555号20007C++2020-01-01 10:00:00
6100666666630006C++2020-01-01 10:00:00

试卷信息表 examination_info（exam_id：试卷 ID, tag：试卷类别, difficulty：试卷难度, duration：考试时长, release_time：发布时间），示例数据如下：

idexam_idtagdifficultydurationrelease_time
19001C++hard602020-01-01 10:00:00
29002c#hard802020-01-01 10:00:00
39003SQLmedium702020-01-01 10:00:00

现有试卷作答记录表 exam_record（uid 用户 ID,exam_id 试卷 ID, start_time 开始作答时间, submit_time 交卷时间, score 得分） 示例数据如下：

iduidexam_idstart_timesubmit_timescore
1100190012020-01-02 09:01:012020-01-02 09:21:5980
2100190012021-05-02 10:01:01(NULL)(NULL)
4100190012021-06-02 19:01:012021-06-02 19:32:0020
3100190022021-02-02 19:01:012021-02-02 19:30:0187
5100190022021-09-05 19:01:012021-09-05 19:40:0189
6100190022021-09-01 12:01:01(NULL)(NULL)
11100290012020-01-01 12:01:012020-01-01 12:31:0181
16100290012021-09-06 12:01:012021-09-06 12:21:0180
17100290012021-09-06 12:01:01(NULL)(NULL)
18100290012021-09-07 12:01:01(NULL)(NULL)
7100290022021-05-05 18:01:012021-05-05 18:59:0290
12100290022020-02-01 12:01:012020-02-01 12:31:0182
13100290022020-02-02 12:11:012020-02-02 12:31:0183
9100390012021-09-07 10:01:012021-09-07 10:31:0189
8100390032021-02-06 12:01:01(NULL)(NULL)
10100490022021-08-06 12:01:01(NULL)(NULL)
14100590012021-02-01 11:01:012021-02-01 11:31:0184
15100690012021-02-01 11:01:012021-09-01 11:31:0184

找到昵称以"机器人"+纯数字+"号"或者纯数字组成的用户对于字母 c 开头的试卷类别（如 C,C++,c#等）的已完成的试卷 ID 和平均得分，按用户 ID、平均分升序排序。由示例数据结果输出如下：

uidexam_idavg_score
1006900184

解释：昵称满足条件的用户有 1001、1002、1004、1005、1006；

c 开头的试卷有 9001、9002；

满足上述条件的作答记录中，1002 完成 9001 的得分有 81、80，平均分为 81（80.5 取整四舍五入得 81）；

1002 完成 9002 的得分有 90、82、83，平均分为 85；

思路：

还是老样子，既然给出了条件，就先把各个条件先写出来

找到昵称以"机器人"+纯数字+"号"或者纯数字组成的用户： 我最开始是这么写的：nick_name LIKE '机器人%号' OR nick_name REGEXP'^[0-9]+$'，如果表中有个 “机器人 H 号” ，那也能通过。

所以这里还得用正则： nick_name LIKE '^机器人[0-9]+号'

对于字母 c 开头的试卷类别： e_info.tag LIKE 'c%' 或者 tag regexp '^c|^C' 第一个也能匹配到大写 C

答案：

SELECT UID,
       exam_id,
       ROUND(AVG(score), 0) avg_score
FROM exam_record
WHERE UID IN
    (SELECT UID
     FROM user_info
     WHERE nick_name RLIKE "^机器人[0-9]+号 $"
       OR nick_name RLIKE "^[0-9]+$")
  AND exam_id IN
    (SELECT exam_id
     FROM examination_info
     WHERE tag RLIKE "^[cC]")
  AND score IS NOT NULL
GROUP BY UID,exam_id
ORDER BY UID,avg_score;

2.3、各用户等级的不同得分表现占比（较难）

描述：

现有用户信息表 user_info（uid 用户 ID，nick_name 昵称, achievement 成就值, level 等级, job 职业方向, register_time 注册时间），数据如下：

iduidnick_nameachievementleveljobregister_time
11001机器人1号190算法2020-01-01 10:00:00
21002机器人2号12003算法2020-01-01 10:00:00
31003机器人3 号 ♂220算法2020-01-01 10:00:00
41004机器人 4号250算法2020-01-01 10:00:00
51005机器人555号20007C++2020-01-01 10:00:00
6100666666630006C++2020-01-01 10:00:00

现有试卷作答记录表 exam_record（uid 用户 ID,exam_id 试卷 ID, start_time 开始作答时间, submit_time 交卷时间, score 得分） 示例数据如下：

iduidexam_idstart_timesubmit_timescore
1100190012020-01-02 09:01:012020-01-02 09:21:5980
2100190012021-05-02 10:01:01(NULL)(NULL)
3100190022021-02-02 19:01:012021-02-02 19:30:0175
4100190022021-09-01 12:01:012021-09-01 12:11:0160
5100190032021-09-02 12:01:012021-09-02 12:41:0190
6100190012021-06-02 19:01:012021-06-02 19:32:0020
7100190022021-09-05 19:01:012021-09-05 19:40:0189
8100190042021-09-03 12:01:01(NULL)(NULL)
9100290012020-01-01 12:01:012020-01-01 12:31:0199
10100290032020-02-01 12:01:012020-02-01 12:31:0182
11100290032020-02-02 12:11:012020-02-02 12:41:0176

为了得到用户试卷作答的定性表现，我们将试卷得分按分界点[90,75,60]分为优良中差四个得分等级（分界点划分到左区间），请统计不同用户等级的人在完成过的试卷中各得分等级占比（结果保留 3 位小数），未完成过试卷的用户无需输出，结果按用户等级降序、占比降序排序。

由示例数据结果输出如下：

levelscore_graderatio
3良0.667
3优0.333
0良0.500
0中0.167
0优0.167
0差0.167

解释：完成过试卷的用户有 1001、1002；完成了的试卷对应的用户等级和分数等级如下：

uidexam_idscorelevel score_grade
10019001800
10019002750
10019002600
10019003900
10019001200
10019002890
10029001993
10029003823
10029003763

因此 0 级用户（只有 1001）的各分数等级比例为：优 1/6，良 1/6，中 1/6，差 3/6；3 级用户（只有 1002）各分数等级比例为：优 1/3，良 2/3。结果保留 3 位小数。

思路：

先把 将试卷得分按分界点[90,75,60]分为优良中差四个得分等级这个条件写出来，这里可以用到case when

CASE
		WHEN a.score >= 90 THEN
		'优'
		WHEN a.score < 90 AND a.score >= 75 THEN
		'良'
		WHEN a.score < 75 AND a.score >= 60 THEN
	'中' ELSE '差'
END

这题的关键点就在于这，其他剩下的就是条件拼接了

答案：

SELECT a.LEVEL,
       a.score_grade,
       ROUND(a.cur_count / b.total_num, 3) AS ratio
FROM
  (SELECT b.LEVEL AS LEVEL,
          (CASE
               WHEN a.score >= 90 THEN '优'
               WHEN a.score < 90
                    AND a.score >= 75 THEN '良'
               WHEN a.score < 75
                    AND a.score >= 60 THEN '中'
               ELSE '差'
           END) AS score_grade,
          count(1) AS cur_count
   FROM exam_record a
   LEFT JOIN user_info b ON a.uid = b.uid
   WHERE a.submit_time IS NOT NULL
   GROUP BY b.LEVEL,
            score_grade) a
LEFT JOIN
  (SELECT b.LEVEL AS LEVEL,
          count(b.LEVEL) AS total_num
   FROM exam_record a
   LEFT JOIN user_info b ON a.uid = b.uid
   WHERE a.submit_time IS NOT NULL
   GROUP BY b.LEVEL) b ON a.LEVEL = b.LEVEL
ORDER BY a.LEVEL DESC,
         ratio DESC

3、限量查询

3.1、注册时间最早的三个人

描述：

现有用户信息表 user_info（uid 用户 ID，nick_name 昵称, achievement 成就值, level 等级, job 职业方向, register_time 注册时间），数据如下：

iduidnick_nameachievementleveljobregister_time
11001机器人1号190算法2020-01-01 10:00:00
21002机器人2号12003算法2020-02-01 10:00:00
31003机器人3 号 ♂220算法2020-01-02 10:00:00
41004机器人 4号250算法2020-01-02 11:00:00
51005机器人555号20007C++2020-01-11 10:00:00
6100666666630006C++2020-11-01 10:00:00

请从中找到注册时间最早的 3 个人。由示例数据结果输出如下：

uidnick_nameregister_time
1001机器人1号2020-01-01 10:00:00
1003机器人 3 号 ♂2020-01-02 10:00:00
1004机器人 4 号2020-01-02 11:00:00

解释：按注册时间排序后选取前三名，输出其用户 ID、昵称、注册时间。

答案：

SELECT uid,nick_name,register_time
FROM user_info
ORDER BY register_time 
LIMIT 3

在这里插入图片描述

3.2、注册当天就完成了试卷的名单第三页（较难）

现有用户信息表 user_info（uid 用户 ID，nick_name 昵称, achievement 成就值, level 等级, job 职业方向, register_time 注册时间），数据如下：

iduidnick_nameachievementleveljobregister_time
11001机器人 1190算法2020-01-01 10:00:00
21002机器人 2 号12003算法2020-01-01 10:00:00
31003机器人 3 号 ♂220算法2020-01-01 10:00:00
41004机器人 4 号250算法2020-01-01 10:00:00
51005机器人 555 号40007算法2020-01-11 10:00:00
61006机器人 6 号250算法2020-01-02 11:00:00
71007机器人 7 号250算法2020-01-02 11:00:00
81008机器人 8 号250算法2020-01-02 11:00:00
91009机器人 9 号250算法2020-01-02 11:00:00
101010机器人 10 号250算法2020-01-02 11:00:00
11101166666630006C++2020-01-02 10:00:00

试卷信息表 examination_info（exam_id：试卷 ID, tag：试卷类别, difficulty：试卷难度, duration：考试时长, release_time：发布时间），示例数据如下：

idexam_idtagdifficultydurationrelease_time
19001算法hard602020-01-01 10:00:00
29002算法hard802020-01-01 10:00:00
39003SQLmedium702020-01-01 10:00:00

现有试卷作答记录表 exam_record（uid 用户 ID,exam_id 试卷 ID, start_time 开始作答时间, submit_time 交卷时间, score 得分） 示例数据如下：

iduidexam_idstart_timesubmit_timescore
1100190012020-01-02 09:01:012020-01-02 09:21:5980
2100290032020-01-20 10:01:012020-01-20 10:10:0181
3100290022020-01-01 12:11:012020-01-01 12:31:0183
4100390022020-01-01 19:01:012020-01-01 19:30:0175
5100490022020-01-01 12:01:012020-01-01 12:11:0160
6100590022020-01-01 12:01:012020-01-01 12:41:0190
7100690012020-01-02 19:01:012020-01-02 19:32:0020
8100790022020-01-02 19:01:012020-01-02 19:40:0189
9100890032020-01-02 12:01:012020-01-02 12:20:0199
10100890012020-01-02 12:01:012020-01-02 12:31:0198
11100990022020-01-02 12:01:012020-01-02 12:31:0182
12101090022020-01-02 12:11:012020-01-02 12:41:0176
13101190012020-01-02 10:01:012020-01-02 10:31:0189

找到求职方向为算法工程师，且注册当天就完成了算法类试卷的人，按参加过的所有考试最高得分排名。排名榜很长，我们将采用分页展示，每页 3 条，现在需要你取出第 3 页（页码从 1 开始）的人的信息。

由示例数据结果输出如下：

uidlevelregister_timemax_score
101002020-01-02 11:00:0076
100302020-01-01 10:00:0075
100402020-01-01 11:00:0060

解释：除了 1011 其他用户的求职方向都为算法工程师；算法类试卷有 9001 和 9002，11 个用户注册当天都完成了算法类试卷；计算他们的所有考试最大分时，只有 1002 和 1008 完成了两次考试，其他人只完成了一场考试，1002 两场考试最高分为 81，1008 最高分为 99。

按最高分排名如下：

uidlevelregister_timemax_score
100802020-01-02 11:00:0099
100572020-01-01 10:00:0090
100702020-01-02 11:00:0089
100232020-01-01 10:00:0083
100902020-01-02 11:00:0082
100102020-01-01 10:00:0080
101002020-01-02 11:00:0076
100302020-01-01 10:00:0075
100402020-01-01 11:00:0060
100602020-01-02 11:00:0020

每页 3 条，第三页也就是第 7~9 条，返回 1010、1003、1004 的行记录即可。

思路：

  • 每页三条，即需要取出第三页的人的信息，要用到limit

  • 统计求职方向为算法工程师且注册当天就完成了算法类试卷的人的信息和每次记录的得分，先求满足条件的用户，后用 left join 做连接查找信息和每次记录的得分

答案：

SELECT ui.uid,
	     ui.`level`,
			 ui.register_time,
			 MAX(score) AS max_score
FROM exam_record er
INNER JOIN examination_info ei USING(exam_id)
INNER JOIN user_info ui ON er.uid = ui.uid AND DATE(er.submit_time) = DATE(ui.register_time)
WHERE ui.job = '算法'
	AND ei.tag = '算法'
GROUP BY ui.uid,
				 ui.`level`,
				 ui.register_time
ORDER BY max_score DESC
LIMIT 6,3

4、文本转换函数

4.1、修复串列了的记录

试卷信息表 examination_info（exam_id：试卷 ID, tag：试卷类别, difficulty：试卷难度, duration：考试时长, release_time：发布时间），示例数据如下：

idexam_idtagdifficultydurationrelease_time
19001算法hard602021-01-01 10:00:00
29002算法hard802021-01-01 10:00:00
39003SQLmedium702021-01-01 10:00:00
49004算法,medium,8002021-01-01 10:00:00

录题同学有一次手误将部分记录的试题类别 tag、难度、时长同时录入到了 tag 字段，请帮忙找出这些录错了的记录，并拆分后按正确的列类型输出。

由示例数据结果输出如下：

exam_idtagdifficultyduration
9004算法medium80

思路：

先来学习下本题要用到的函数

SUBSTRING_INDEX 函数用于提取字符串中指定分隔符的部分。它接受三个参数：原始字符串、分隔符和指定要返回的部分的数量。

以下是SUBSTRING_INDEX函数的语法：

SUBSTRING_INDEX(str, delimiter, count)
  • str：要进行分割的原始字符串。
  • delimiter：用作分割的字符串或字符。
  • count：指定要返回的部分的数量。
    • 如果 count 大于 0，则返回从左边开始的前 count 个部分（以分隔符为界）。
    • 如果 count 小于 0，则返回从右边开始的前 count 个部分（以分隔符为界），即从右侧向左计数。

下面是一些示例，演示了 SUBSTRING_INDEX 函数的使用：

  1. 提取字符串中的第一个部分：
SELECT SUBSTRING_INDEX('apple,banana,cherry', ',', 1);
-- 输出结果：'apple'
  1. 提取字符串中的最后一个部分：
SELECT SUBSTRING_INDEX('apple,banana,cherry', ',', -1);
-- 输出结果：'cherry'
  1. 提取字符串中的前两个部分：
SELECT SUBSTRING_INDEX('apple,banana,cherry', ',', 2);
-- 输出结果：'apple,banana'
  1. 提取字符串中的最后两个部分：
SELECT SUBSTRING_INDEX('apple,banana,cherry', ',', -2);
-- 输出结果：'banana,cherry'

答案：

SELECT
	exam_id,
	substring_index( tag, ',', 1 ) tag,
	substring_index( substring_index( tag, ',', 2 ), ',',- 1 ) difficulty,
	substring_index( tag, ',',- 1 ) duration
FROM
	examination_info
WHERE
	difficulty = ''

在这里插入图片描述

4.2、对过长的昵称截取处理

描述：现有用户信息表 user_info（uid 用户 ID，nick_name 昵称, achievement 成就值, level 等级, job 职业方向, register_time 注册时间），数据如下：

iduidnick_nameachievementleveljobregister_time
11001机器人 1190算法2020-01-01 10:00:00
21002机器人 2 号12003算法2020-01-01 10:00:00
31003机器人 3 号 ♂220算法2020-01-01 10:00:00
41004机器人 4 号250算法2020-01-01 11:00:00
51005机器人 567890123 号40007算法2020-01-11 10:00:00
61006机器人 67890123456789 号250算法2020-01-02 11:00:00

有的用户的昵称特别长，在一些展示场景会导致样式混乱，因此需要将特别长的昵称转换一下再输出，请输出字符数大于 10 的用户信息，对于字符数大于 17 的用户输出前 10 个字符然后加上三个点号：『…』。

由示例数据结果输出如下：

uidnick_name
1005机器人 567890123 号
1006机器人 678901234567…

解释：字符数大于 10 的用户有 1005 和 1006，长度分别为 17、22；因此需要对 1006 的昵称截断输出。

思路：

这题涉及到字符的计算，要计算字符串的字符数（即字符串的长度），可以使用 LENGTH 函数或 CHAR_LENGTH 函数。这两个函数的区别在于对待多字节字符的方式。

  1. LENGTH 函数：它返回给定字符串的字节数。对于包含多字节字符的字符串，每个字符都会被当作一个字节来计算。
    示例：
SELECT LENGTH('你好'); -- 输出结果：6，因为 '你好' 中的每个汉字每个占3个字节
  1. CHAR_LENGTH 函数：它返回给定字符串的字符数。对于包含多字节字符的字符串，每个字符会被当作一个字符来计算。
    示例：
SELECT CHAR_LENGTH('你好'); -- 输出结果：2，因为 '你好' 中有两个字符，即两个汉字

答案：

SELECT uid,
	  (CASE 
	    WHEN CHAR_LENGTH( nick_name ) > 17 THEN
		CONCAT(SUBSTR(nick_name,1,17),'...') ELSE nick_name
		END) AS nick_name
FROM user_info
WHERE CHAR_LENGTH(nick_name) > 10

在这里插入图片描述

好文推荐：
《【SQL】SQL常见面试题总结（1）》
《【SQL】SQL常见面试题总结（2）》
《【SQL】SQL常见面试题总结（3）》
在这里插入图片描述

Hanson Huang
关注
专栏目录
sql面试题
Java_Soldier的博客
09-18 1万+
 教师号　　星期号　是否有课 　１　　　　２　　　有 　１　　　　３　　　有 　２　　　　１　　　有 　３　　　　２　　　有` 　１　　　　２　　　有 写一条sql语句让你变为这样的表 教师号　星期一　星期二　星期三 　１　　　　　　　２　　　１　 　２　　　１　　　 　３　　　　　　　１ 各星期下的数字表示：对应的教师在星期几已经排的课数 =============
2022年SQL常见面试题.doc
11-11
本文档总结了2022年SQL常见面试题，涵盖了SQL语句的基本知识点、数据库设计和高级操作等方面的题目。下面是对每个题目的详细解释和知识点总结： 一.SQL语句基础知识点 1. 用一条SQL语句查询出每门课都不小于80分的...
SQL优化面试题30道 + MySQL面试宝典(尚硅谷)
の博客
06-16 3054
调优
SQL面试常见题目
最新发布
警警的博客
09-23 1362
SQL面试常见题目涉及多个方面，包括数据查询、数据操作、表的设计与优化等。
SQL SERVER面试题
weixin_30677073的博客
06-12 541
1.用一条SQL语句 查询出每门课都大于80分的学生姓名 name kecheng fenshu 张三 语文 81张三 数学 75李四 语文 76李四 数学 90王五 语文 81王五 数学 100王五 英语 90 A: select d...
SQL面试题
行者买刀
12-01 430
目前在职场中很难找到非常合格的数据库开发人员。有人说:“SQL开发是一门语言，它很容易学，但是很难掌握。” 　　在面试应聘的SQL Server数据库开发人员时，我运用了一套标准的基准技术问题。下面这些问题是我觉得能够真正有助于淘汰不合格应聘者的问题。它们按照从易到难的顺序排列。当你问到关于主键和外键的问题时，后面的问题都十分有难度，因为答案可能会更难解释和说明，尤其是在面试的情形下。 　　你能向...
面试 SQL整理 常见的SQL面试题：经典50题
热门推荐
sh_c1314
09-13 26万+
目录 ​ SQL基础知识整理: 常见的SQL面试题：经典50题 三、50道面试题 2.汇总统计分组分析 3.复杂查询 sql面试题：topN问题 4.多表查询 【面试题类型总结】这类题目属于行列如何互换，解题思路如下： 其他面试题: SQL基础知识整理: select 查询结果 如:[学号,平均成绩：组函数avg(成绩)]from 从哪张表中查找数...
SQLServer高频面试题及答案
04-19
SQL Server高频面试题及答案 数据库基础知识篇 1. 主键、外键、超键、候选键 超键是关系模式中能唯一标识元组的属性集。候选键是最小超键，即没有冗余元素的超键。主键是数据库表中对储存数据对象予以唯一和完整...
2022年SQL常见面试题集.doc
11-11
"2022年SQL常见面试题集" 本资源摘要信息涵盖了SQL常见面试题集，涉及学生表、课程表、成绩表、教师表等多个表的操作。这份面试题集涵盖了多种SQL语句，包括查询、插入、更新、删除等操作，对SQL语言的熟练度和问题...
Sql常见面试题
04-25
根据提供的文件内容，我们可以整理并深入探讨以下几个SQL知识点： ### 1. 查询每门课都大于80分的学生姓名 **题目描述**：有一个表格包含了学生的姓名、所学科目及分数，要求通过一条SQL语句找出所有科目分数都...
经典sql面试题
10-25
### 经典SQL面试题解析 #### 颇具挑战性的SQL面试题——删除冗余记录 **题目背景与分析** 在本题中，我们面对的是一个常见的问题：如何有效地处理数据库中的冗余数据。在实际应用中，由于各种原因（如数据导入...
SQL经典面试题及答案
jasonoiu的专栏
04-15 2620
1.一道SQL语句面试题，关于group by表内容：2005-05-09 胜2005-05-09 胜2005-05-09 负2005-05-09 负2005-05-10 胜2005-05-10 负2005-05-10 负如果要生成下列结果, 该如何写sql语句?            胜 负2005-05-09 2 22005-05-10 1 2-------------------------
SQL 题目及答案
殇沫流年的专栏
11-17 1303
SQL 题目及答案 学生成绩表(stuscore)： 姓名：name     课程：subject     分数：score     学号：stuid 张三     数学     89     1 张三     语文     80     1 张三     英语     70     1 李四     数学     90     2 李四     语文     70
Sql面试题
hyunbar的博客
05-14 2510
（1）、 已知一个表的结构为： 姓名 科目 成绩 张三 语文 20 张三 数学 30 张三 英语 50 李四 语文 70 李四 数学 60 李四 英语 90 1.怎样通过select语句把他变成以下结构： 姓名 语文成绩 数学成绩 英语成绩 张三 20 30 50 李四 70 60 90 ...
"2022年SQL常见面试题及解析
2022年SQL常见面试题.doc中有两个问题需要回答： 问题1：用一条SQL语句查询出每门课都不小于80分的学生姓名name、课程名称kecheng、分数fenshu。 解答：根据提供的样例数据，可以使用以下SQL语句实现： ```SQL ...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值