【SQL】SQL常見面試題總結（4）

目錄

  • 1、空值處理
    • 1.1、統計有未完成狀態的試卷的未完成數和未完成率
    • 1.2、0 級用戶高難度試卷的平均用時和平均得分
  • 2、高級條件語句
    • 2.1、篩選限定昵稱成就值活躍日期的用戶（較難）
    • 2.2、篩選昵稱規則和試卷規則的作答記錄（較難）
    • 2.3、各用戶等級的不同得分表現占比（較難）
  • 3、限量查詢
    • 3.1、注冊時間最早的三個人
    • 3.2、注冊當天就完成了試卷的名單第三頁（較難）
  • 4、文本轉換函數
    • 4.1、修復串列了的記錄
    • 4.2、對過長的昵稱截取處理

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_tookFROM (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_scoreFROM exam_record  erLEFT JOIN user_info ui ON er.uid = ui.uidLEFT JOIN examination_info ei ON er.exam_id = ei.exam_idWHERE 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 1200AND 2500AND (date_format(record.submit_time, '%Y%m')= 202109OR 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 UIDFROM user_infoWHERE nick_name RLIKE "^機器人[0-9]+號 $"OR nick_name RLIKE "^[0-9]+$")AND exam_id IN(SELECT exam_idFROM examination_infoWHERE 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

CASEWHEN 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,(CASEWHEN a.score >= 90 THEN '優'WHEN a.score < 90AND a.score >= 75 THEN '良'WHEN a.score < 75AND a.score >= 60 THEN '中'ELSE '差'END) AS score_grade,count(1) AS cur_countFROM exam_record aLEFT JOIN user_info b ON a.uid = b.uidWHERE a.submit_time IS NOT NULLGROUP BY b.LEVEL,score_grade) a
LEFT JOIN(SELECT b.LEVEL AS LEVEL,count(b.LEVEL) AS total_numFROM exam_record aLEFT JOIN user_info b ON a.uid = b.uidWHERE a.submit_time IS NOT NULLGROUP 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'

答案：

SELECTexam_id,substring_index( tag, ',', 1 ) tag,substring_index( substring_index( tag, ',', 2 ), ',',- 1 ) difficulty,substring_index( tag, ',',- 1 ) duration
FROMexamination_info
WHEREdifficulty = ''

在這里插入圖片描述

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 THENCONCAT(SUBSTR(nick_name,1,17),'...') ELSE nick_nameEND) AS nick_name
FROM user_info
WHERE CHAR_LENGTH(nick_name) > 10

在這里插入圖片描述

好文推薦：
《【SQL】SQL常見面試題總結（1）》
《【SQL】SQL常見面試題總結（2）》
《【SQL】SQL常見面試題總結（3）》
在這里插入圖片描述

本文來自互聯網用戶投稿，該文觀點僅代表作者本人，不代表本站立場。本站僅提供信息存儲空間服務，不擁有所有權，不承擔相關法律責任。
如若轉載，請注明出處：http://www.pswp.cn/web/13394.shtml
繁體地址，請注明出處：http://hk.pswp.cn/web/13394.shtml
英文地址，請注明出處：http://en.pswp.cn/web/13394.shtml

如若內容造成侵權/違法違規/事實不符，請聯系多彩編程網進行投訴反饋email:809451989@qq.com，一經查實，立即刪除！

相關文章

SmartEDA助力電工基礎實驗：打造高效、智能的學習新體驗

SmartEDA助力電工基礎實驗：打造高效、智能的學習新體驗

在電工基礎實驗的教學與學習中&#xff0c;傳統的實驗設備往往存在著操作復雜、數據處理繁瑣等問題&#xff0c;給學生的學習帶來了不小的挑戰。然而&#xff0c;隨著科技的不斷發展&#xff0c;一種名為SmartEDA的智能電工實驗輔助設備正逐漸走入課堂&#xff0c;以其高效、智…
閱讀更多...
Es6-對象新增了哪些擴展？

Es6-對象新增了哪些擴展？

?&#x1f308;個人主頁&#xff1a;前端青山 &#x1f525;系列專欄&#xff1a;Javascript篇 &#x1f516;人終將被年少不可得之物困其一生 依舊青山,本期給大家帶來Javascript篇專欄內容:Es6-對象新增了哪些擴展&#xff1f; 目錄 一、參數 二、屬性 函數的length屬性 …
閱讀更多...
Unsupervised Out-of-Distribution Detection with Diffusion Inpainting

Unsupervised Out-of-Distribution Detection with Diffusion Inpainting

Unsupervised Out-of-Distribution Detection with Diffusion Inpainting 摘要1.介紹2 背景3 3. Lift, Map, Detect摘要 無監督的異常分布檢測（OOD）旨在通過僅從未標記的域內數據中學習來識別域外數據。我們提出了一種用于此任務的新方法——提升、映射、檢測（LMD），該方法…
閱讀更多...
數據結構-棧(帶圖)

數據結構-棧(帶圖)

目錄 棧的概念 畫圖理解棧 棧的實現 fun.h fun.c main.c 棧的概念 棧&#xff08;Stack&#xff09;是一種基本的數據結構&#xff0c;其特點是只允許在同一端進行插入和刪除操作&#xff0c;這一端被稱為棧頂。遵循后進先出&#xff08;Last In, First Out, LIFO&#…
閱讀更多...
瀏覽器下載附件流建議

瀏覽器下載附件流建議

大文件下載可采用附件流的方式&#xff0c;后端設置一下響應參數&#xff0c;然后以流的方式返回前端 res.set({ "Content-Type": "application/octet-stream", "Content-Disposition": "attachment;filename* UTF-8"fixedEncodeUR…
閱讀更多...
【論文粗讀|arXiv】GaSpCT: Gaussian Splatting for Novel CT Projection View Synthesis

【論文粗讀|arXiv】GaSpCT: Gaussian Splatting for Novel CT Projection View Synthesis

Abstract 本文提出了一種新穎的視圖合成和3D場景表示方法&#xff0c;用于為計算機斷層掃描&#xff08;CT&#xff09;生成新的投影視圖。 方法采用了Gaussian Splatting 框架&#xff0c;基于有限的2D圖像投影集&#xff0c;無需運動結構&#xff08;SfM&#xff09;方法&am…
閱讀更多...
CSPM-4是什么？報考條件有哪些?

CSPM-4是什么？報考條件有哪些?

2021年10月&#xff0c;《國家標準化發展綱要》明確提出構建多層次從業人員培養培訓體系&#xff0c;開展專業人才培養培訓和國家質量基礎設施綜合教育。建立健全人才的職業能力評價和激勵機制。由中國標準化協會&#xff08;CAS&#xff09;組織開展的項目管理專業人員能力評價…
閱讀更多...
Swift 5.9 中 if 與 switch 語句簡潔新語法讓擼碼更帶勁

Swift 5.9 中 if 與 switch 語句簡潔新語法讓擼碼更帶勁

概覽 在實際代碼開發中&#xff0c;可能初學 Swift 語言的小伙伴們在擼碼時最常用的得數 if 和 switch…case 條件選擇語句了。不過在某些場景下它們顯得略有那么一丟丟“矯揉造作”&#xff0c;還好從 Swift 5.9 開始蘋果知趣的為其簡化了語法且增強了它們的表現力。 在本篇…
閱讀更多...
Vitis HLS 學習筆記--優化本地存儲器訪問瓶頸

Vitis HLS 學習筆記--優化本地存儲器訪問瓶頸

目錄 1. 簡介 2. 代碼解析 2.1 原始代碼 2.2 優化后 2.3 分析優化措施 3. 總結 1. 簡介 在Vitis HLS中&#xff0c;實現II&#xff08;迭代間隔&#xff09; 1是提高循環執行效率的關鍵。II1意味著每個時鐘周期都可以開始一個新的迭代&#xff0c;這是最理想的情況&…
閱讀更多...
Java實現音頻轉文本（語音識別）

Java實現音頻轉文本（語音識別）

在Java中實現音頻轉文本&#xff08;也稱為語音識別或ASR&#xff09;通常涉及使用專門的語音識別服務&#xff0c;如Google Cloud Speech-to-Text、IBM Watson Speech to Text、Amazon Transcribe、Microsoft Azure Speech Services&#xff0c;或者一些開源庫如CMU Sphinx。 …
閱讀更多...
2024年第四屆長三角高校數學建模競賽C題思路

2024年第四屆長三角高校數學建模競賽C題思路

賽道C：汽后配件需求預測問題 在汽后行業的供應鏈管理中， 精準的需求預測是后續管理及決策的基礎。 各個汽后配件即為一個庫存單位（SKU， Stock Keeping Unit）， 如果可以準確預知未來對于各個配件的市場需求， 就可以提前將庫存放在靠近需求的倉庫中， 從而降低庫存成本，…
閱讀更多...
HNCTF ——baby_python

HNCTF ——baby_python

H&NCTF 2024 官方WP (qq.com) OpCodes Pickle.jl (juliahub.com) nc之后 PS D:\ForCode\pythoncode\.idea> nc hnctf.yuanshen.life 33267 # Python 3.10.12 from pickle import loads main b"\x80\x04ctypes\nFunctionType\n(ctypes\nCodeType\n(I1\nI0\nI0\n…
閱讀更多...
[Linux] 常用服務器命令(持續更新)

[Linux] 常用服務器命令(持續更新)

文件操作 # 顯示文件系統的磁盤空間使用情況 df -h全局查找文件 find / -type f -iname "java"find / -name libncurses*拷貝整個文件夾 cp -r /home/a/ /home/b/ 解壓&#xff0c;撤銷解壓 撤銷zip解壓 zipinfo -1 path/xx.zip | xargs rm -rf 撤銷tar解壓 tar …
閱讀更多...
【Vim】

【Vim】

一、什么是Vim&#xff1f; Vim 是一個歷史悠久的文本編輯器&#xff0c;可以追溯到 qed。 Bram Moolenaar 于 1991 年發布初始版本。Vim 有著悠久的歷史;它起源于 Vi 編輯器&#xff08;1976 年&#xff09;&#xff0c;至今仍在開發中。(Vim has a rich history; it origina…
閱讀更多...
css+html 愛心?

css+html 愛心?

效果 代碼實現 html <div class"main"><div class"aixin"></div></div>css .main {transform: rotate(-45deg);}.aixin {height: 100px;width: 100px;background-color: red;margin: auto;margin-top: 200px;position: relativ…
閱讀更多...
MySQL第一次作業（基本操作）

MySQL第一次作業（基本操作）

目錄 一、登陸數據庫 二、創建數據庫zoo 三、修改數據庫zoo字符集為gbk 四、選擇當前數據庫為zoo 五、查看創建數據庫zoo信息 六、刪除數據庫zoo 一、登陸數據庫 指令&#xff1a; mysql -u root -p 二、創建數據庫zoo 指令&#xff1a; create database zoo; 三、修改數…
閱讀更多...
基于PHP+MySQL組合開發的多用戶自定義商城系統源碼 附帶源代碼包以及搭建教程

基于PHP+MySQL組合開發的多用戶自定義商城系統源碼 附帶源代碼包以及搭建教程

系統概述 互聯網技術的飛速發展&#xff0c;電子商務已成為人們日常生活中不可或缺的一部分。商城系統作為電子商務的核心&#xff0c;其開發技術和用戶體驗直接影響著電商平臺的競爭力和用戶滿意度。本文旨在介紹一個基于PHPMySQL組合開發的多用戶自定義商城系統&#xff0c;…
閱讀更多...
C++學習~~string類

C++學習~~string類

1.STL簡單介紹 &#xff08;1&#xff09;標準模版庫&#xff0c;是C里面的標準庫的一部分&#xff0c;C標準庫里面還有其他的東西&#xff0c;但是我們不經常使用&#xff0c;我們經常使用的還是STL這個標準庫部分。 &#xff08;2&#xff09;六大件&#xff1a;仿函數&…
閱讀更多...
C# WinForm —— 16 MonthCalendar 介紹

C# WinForm —— 16 MonthCalendar 介紹

1. 簡介 可以選擇單個日期&#xff0c;也可以選擇一段日期&#xff0c;在選擇時間范圍上 比較適用&#xff0c;但不能跨月份選擇日期范圍 在直觀上&#xff0c;可以快速查看、選擇日期/日期范圍 2. 常用屬性 屬性解釋(Name)控件ID&#xff0c;在代碼里引用的時候會用到,一般…
閱讀更多...
Uni-app基礎知識

Uni-app基礎知識

uni-app組成和跨端原理 | uni-app官網uni-app,uniCloud,serverless,uni-app組成和跨端原理,基本語言和開發規范,編譯器,運行時&#xff08;runtime&#xff09;,邏輯層和渲染層分離https://uniapp.dcloud.net.cn/tutorial/1.adb連接模擬器 找到adb所在位置&#xff08;一般在hb…
閱讀更多...
最新文章

Copyright @ 2022~2023