SQL常见面试题总结（5）

题目来源于：牛客题霸 - SQL 进阶挑战

题目来源于：牛客题霸 - SQL 进阶挑战

牛客题霸 - SQL 进阶挑战

较难或者困难的题目可以根据自身实际情况和面试需要来决定是否要跳过。

空值处理

空值处理

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

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

描述：

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

exam_record

uid

exam_id

start_time

submit_time

score

iduidexam_idstart_timesubmit_timescore1100190012020-01-02 09:01:012020-01-02 09:21:01802100190012021-05-02 10:01:012021-05-02 10:30:01813100190012021-09-02 12:01:01(NULL)(NULL)

iduidexam_idstart_timesubmit_timescore

id

uid

exam_id

start_time

submit_time

score

1100190012020-01-02 09:01:012020-01-02 09:21:0180

1

1001

9001

2020-01-02 09:01:01

2020-01-02 09:21:01

80

2100190012021-05-02 10:01:012021-05-02 10:30:0181

2

1001

9001

2021-05-02 10:01:01

2021-05-02 10:30:01

81

3100190012021-09-02 12:01:01(NULL)(NULL)

3

1001

9001

2021-09-02 12:01:01

(NULL)

(NULL)

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

exam_idincomplete_cntcomplete_rate900110.333

exam_idincomplete_cntcomplete_rate

exam_id

incomplete_cnt

complete_rate

900110.333

9001

1

0.333

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

思路：

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

答案：

写法 1：

SELECT
    exam_id,
    (COUNT(*) - COUNT(submit_time)) AS incomplete_cnt,
    ROUND((COUNT(*) - COUNT(submit_time)) / COUNT(*), 3) AS incomplete_rate
FROM
    exam_record
GROUP BY
    exam_id
HAVING
    (COUNT(*) - COUNT(submit_time)) > 0;

SELECT
    exam_id,
    (COUNT(*) - COUNT(submit_time)) AS incomplete_cnt,
    ROUND((COUNT(*) - COUNT(submit_time)) / COUNT(*), 3) AS incomplete_rate
FROM
    exam_record
GROUP BY
    exam_id
HAVING
    (COUNT(*) - COUNT(submit_time)) > 0;

利用 COUNT(*)统计分组内的总记录数，COUNT(submit_time) 只统计 submit_time 字段不为 NULL 的记录数（即已完成数）。两者相减，就是未完成数。

COUNT(*)

COUNT(submit_time)

submit_time

写法 2：

SELECT
    exam_id,
    COUNT(CASE WHEN submit_time IS NULL THEN 1 END) AS incomplete_cnt,
    ROUND(COUNT(CASE WHEN submit_time IS NULL THEN 1 END) / COUNT(*), 3) AS incomplete_rate
FROM
    exam_record
GROUP BY
    exam_id
HAVING
    COUNT(CASE WHEN submit_time IS NULL THEN 1 END) > 0;

SELECT
    exam_id,
    COUNT(CASE WHEN submit_time IS NULL THEN 1 END) AS incomplete_cnt,
    ROUND(COUNT(CASE WHEN submit_time IS NULL THEN 1 END) / COUNT(*), 3) AS incomplete_rate
FROM
    exam_record
GROUP BY
    exam_id
HAVING
    COUNT(CASE WHEN submit_time IS NULL THEN 1 END) > 0;

使用 CASE 表达式，当条件满足时返回一个非 NULL 值（例如 1），否则返回 NULL。然后用 COUNT 函数来统计非 NULL 值的数量。

CASE

NULL

NULL

COUNT

NULL

写法 3：

SELECT
    exam_id,
    SUM(submit_time IS NULL) AS incomplete_cnt,
    ROUND(SUM(submit_time IS NULL) / COUNT(*), 3) AS incomplete_rate
FROM
    exam_record
GROUP BY
    exam_id
HAVING
    incomplete_cnt > 0;

SELECT
    exam_id,
    SUM(submit_time IS NULL) AS incomplete_cnt,
    ROUND(SUM(submit_time IS NULL) / COUNT(*), 3) AS incomplete_rate
FROM
    exam_record
GROUP BY
    exam_id
HAVING
    incomplete_cnt > 0;

利用 SUM 函数对一个表达式求和。当 submit_time 为 NULL 时，表达式 (submit_time IS NULL) 的值为 1 (TRUE)，否则为 0 (FALSE)。将这些 1 和 0 加起来，就得到了未完成的数量。

SUM

submit_time

NULL

(submit_time IS NULL)

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

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

描述：

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

user_info

uid

nick_name

achievement

level

job

register_time

iduidnick_nameachievementleveljobregister_time11001牛客 1 号100算法2020-01-01 10:00:0021002牛客 2 号21006算法2020-01-01 10:00:00

iduidnick_nameachievementleveljobregister_time

id

uid

nick_name

achievement

level

job

register_time

11001牛客 1 号100算法2020-01-01 10:00:00

1

1001

牛客 1 号

10

0

算法

2020-01-01 10:00:00

21002牛客 2 号21006算法2020-01-01 10:00:00

2

1002

牛客 2 号

2100

6

算法

2020-01-01 10:00:00

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

examination_info

exam_id

tag

difficulty

duration

release_time

idexam_idtagdifficultydurationrelease_time19001SQLhard602020-01-01 10:00:0029002SQLeasy602020-01-01 10:00:0039004算法medium802020-01-01 10:00:00

idexam_idtagdifficultydurationrelease_time

id

exam_id

tag

difficulty

duration

release_time

19001SQLhard602020-01-01 10:00:00

1

9001

SQL

hard

60

2020-01-01 10:00:00

29002SQLeasy602020-01-01 10:00:00

2

9002

SQL

easy

60

2020-01-01 10:00:00

39004算法medium802020-01-01 10:00:00

3

9004

算法

medium

80

2020-01-01 10:00:00

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

exam_record

uid

exam_id

start_time

submit_time

score

iduidexam_idstart_timesubmit_timescore1100190012020-01-02 09:01:012020-01-02 09:21:59802100190012021-05-02 10:01:01(NULL)(NULL)3100190022021-02-02 19:01:012021-02-02 19:30:01874100190012021-06-02 19:01:012021-06-02 19:32:00205100190022021-09-05 19:01:012021-09-05 19:40:01896100190022021-09-01 12:01:01(NULL)(NULL)7100290022021-05-05 18:01:012021-05-05 18:59:0290

iduidexam_idstart_timesubmit_timescore

id

uid

exam_id

start_time

submit_time

score

1100190012020-01-02 09:01:012020-01-02 09:21:5980

1

1001

9001

2020-01-02 09:01:01

2020-01-02 09:21:59

80

2100190012021-05-02 10:01:01(NULL)(NULL)

2

1001

9001

2021-05-02 10:01:01

(NULL)

(NULL)

3100190022021-02-02 19:01:012021-02-02 19:30:0187

3

1001

9002

2021-02-02 19:01:01

2021-02-02 19:30:01

87

4100190012021-06-02 19:01:012021-06-02 19:32:0020

4

1001

9001

2021-06-02 19:01:01

2021-06-02 19:32:00

20

5100190022021-09-05 19:01:012021-09-05 19:40:0189

5

1001

9002

2021-09-05 19:01:01

2021-09-05 19:40:01

89

6100190022021-09-01 12:01:01(NULL)(NULL)

6

1001

9002

2021-09-01 12:01:01

(NULL)

(NULL)

7100290022021-05-05 18:01:012021-05-05 18:59:0290

7

1002

9002

2021-05-05 18:01:01

2021-05-05 18:59:02

90

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

uidavg_scoreavg_time_took10013336.7

uidavg_scoreavg_time_took

uid

avg_score

avg_time_took

10013336.7

1001

33

36.7

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

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

IF

case when

答案：

SELECT UID,
       round(avg(new_socre)) 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), ef.duration) AS time_diff,
          IF (er.submit_time IS NOT NULL,er.score,0) AS new_socre
   FROM exam_record er
   LEFT JOIN user_info uf ON er.uid = uf.uid
   LEFT JOIN examination_info ef ON er.exam_id = ef.exam_id
   WHERE uf.LEVEL = 0 AND ef.difficulty = 'hard' ) t
GROUP BY UID
ORDER BY UID

SELECT UID,
       round(avg(new_socre)) 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), ef.duration) AS time_diff,
          IF (er.submit_time IS NOT NULL,er.score,0) AS new_socre
   FROM exam_record er
   LEFT JOIN user_info uf ON er.uid = uf.uid
   LEFT JOIN examination_info ef ON er.exam_id = ef.exam_id
   WHERE uf.LEVEL = 0 AND ef.difficulty = 'hard' ) t
GROUP BY UID
ORDER BY UID

高级条件语句

高级条件语句

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

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

描述：

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

user_info

uid

nick_name

achievement

level

job

register_time

iduidnick_nameachievementleveljobregister_time11001牛客 1 号10002算法2020-01-01 10:00:0021002牛客 2 号12003算法2020-01-01 10:00:0031003进击的 3 号22005算法2020-01-01 10:00:0041004牛客 4 号25006算法2020-01-01 10:00:0051005牛客 5 号30007C++2020-01-01 10:00:00

iduidnick_nameachievementleveljobregister_time

id

uid

nick_name

achievement

level

job

register_time

11001牛客 1 号10002算法2020-01-01 10:00:00

1

1001

牛客 1 号

1000

2

算法

2020-01-01 10:00:00

21002牛客 2 号12003算法2020-01-01 10:00:00

2

1002

牛客 2 号

1200

3

算法

2020-01-01 10:00:00

31003进击的 3 号22005算法2020-01-01 10:00:00

3

1003

进击的 3 号

2200

5

算法

2020-01-01 10:00:00

41004牛客 4 号25006算法2020-01-01 10:00:00

4

1004

牛客 4 号

2500

6

算法

2020-01-01 10:00:00

51005牛客 5 号30007C++2020-01-01 10:00:00

5

1005

牛客 5 号

3000

7

C++

2020-01-01 10:00:00

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

exam_record

uid

exam_id

start_time

submit_time

score

iduidexam_idstart_timesubmit_timescore1100190012020-01-02 09:01:012020-01-02 09:21:59803100190022021-02-02 19:01:012021-02-02 19:30:01872100190012021-05-02 10:01:01(NULL)(NULL)4100190012021-06-02 19:01:012021-06-02 19:32:00206100190022021-09-01 12:01:01(NULL)(NULL)5100190022021-09-05 19:01:012021-09-05 19:40:018911100290012020-01-01 12:01:012020-01-01 12:31:018112100290022020-02-01 12:01:012020-02-01 12:31:018213100290022020-02-02 12:11:012020-02-02 12:31:01837100290022021-05-05 18:01:012021-05-05 18:59:029016100290012021-09-06 12:01:012021-09-06 12:21:018017100290012021-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:018910100490022021-08-06 12:01:01(NULL)(NULL)14100590012021-02-01 11:01:012021-02-01 11:31:018415100690012021-02-01 11:01:012021-02-01 11:31:0184

iduidexam_idstart_timesubmit_timescore

id

uid

exam_id

start_time

submit_time

score

1100190012020-01-02 09:01:012020-01-02 09:21:5980

1

1001

9001

2020-01-02 09:01:01

2020-01-02 09:21:59

80

3100190022021-02-02 19:01:012021-02-02 19:30:0187

3

1001

9002

2021-02-02 19:01:01

2021-02-02 19:30:01

87

2100190012021-05-02 10:01:01(NULL)(NULL)

2

1001

9001

2021-05-02 10:01:01

(NULL)

(NULL)

4100190012021-06-02 19:01:012021-06-02 19:32:0020

4

1001

9001

2021-06-02 19:01:01

2021-06-02 19:32:00

20

6100190022021-09-01 12:01:01(NULL)(NULL)

6

1001

9002

2021-09-01 12:01:01

(NULL)

(NULL)

5100190022021-09-05 19:01:012021-09-05 19:40:0189

5

1001

9002

2021-09-05 19:01:01

2021-09-05 19:40:01

89

11100290012020-01-01 12:01:012020-01-01 12:31:0181

11

1002

9001

2020-01-01 12:01:01

2020-01-01 12:31:01

81

12100290022020-02-01 12:01:012020-02-01 12:31:0182

12

1002

9002

2020-02-01 12:01:01

2020-02-01 12:31:01

82

13100290022020-02-02 12:11:012020-02-02 12:31:0183

13

1002

9002

2020-02-02 12:11:01

2020-02-02 12:31:01

83

7100290022021-05-05 18:01:012021-05-05 18:59:0290

7

1002

9002

2021-05-05 18:01:01

2021-05-05 18:59:02

90

16100290012021-09-06 12:01:012021-09-06 12:21:0180

16

1002

9001

2021-09-06 12:01:01

2021-09-06 12:21:01

80

17100290012021-09-06 12:01:01(NULL)(NULL)

17

1002

9001

2021-09-06 12:01:01

(NULL)

(NULL)

18100290012021-09-07 12:01:01(NULL)(NULL)

18

1002

9001

2021-09-07 12:01:01

(NULL)

(NULL)

8100390032021-02-06 12:01:01(NULL)(NULL)

8

1003

9003

2021-02-06 12:01:01

(NULL)

(NULL)

9100390012021-09-07 10:01:012021-09-07 10:31:0189

9

1003

9001

2021-09-07 10:01:01

2021-09-07 10:31:01

89

10100490022021-08-06 12:01:01(NULL)(NULL)

10

1004

9002

2021-08-06 12:01:01

(NULL)

(NULL)

14100590012021-02-01 11:01:012021-02-01 11:31:0184

14

1005

9001

2021-02-01 11:01:01

2021-02-01 11:31:01

84

15100690012021-02-01 11:01:012021-02-01 11:31:0184

15

1006

9001

2021-02-01 11:01:01

2021-02-01 11:31:01

84

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

practice_record

uid

question_id

submit_time

score

iduidquestion_idsubmit_timescore1100180012021-08-02 11:41:01602100280012021-09-02 19:30:01503100280012021-09-02 19:20:01704100280022021-09-02 19:38:01705100380022021-09-01 19:38:0180

iduidquestion_idsubmit_timescore

id

uid

question_id

submit_time

score

1100180012021-08-02 11:41:0160

1

1001

8001

2021-08-02 11:41:01

60

2100280012021-09-02 19:30:0150

2

1002

8001

2021-09-02 19:30:01

50

3100280012021-09-02 19:20:0170

3

1002

8001

2021-09-02 19:20:01

70

4100280022021-09-02 19:38:0170

4

1002

8002

2021-09-02 19:38:01

70

5100380022021-09-01 19:38:0180

5

1003

8002

2021-09-01 19:38:01

80

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

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

uidnick_nameachievement1002牛客 2 号1200

uidnick_nameachievement

uid

nick_name

achievement

1002牛客 2 号1200

1002

牛客 2 号

1200

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

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

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

思路：

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

昵称以『牛客』开头『号』结尾: nick_name LIKE "牛客%号"

nick_name LIKE "牛客%号"

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

achievement BETWEEN 1200 AND 2500

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

( 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

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

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

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

描述：

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

user_info

uid

nick_name

achievement

level

job

register_time

iduidnick_nameachievementleveljobregister_time11001牛客 1 号19002算法2020-01-01 10:00:0021002牛客 2 号12003算法2020-01-01 10:00:0031003牛客 3 号 ♂22005算法2020-01-01 10:00:0041004牛客 4 号25006算法2020-01-01 10:00:0051005牛客 555 号20007C++2020-01-01 10:00:006100666666630006C++2020-01-01 10:00:00

iduidnick_nameachievementleveljobregister_time

id

uid

nick_name

achievement

level

job

register_time

11001牛客 1 号19002算法2020-01-01 10:00:00

1

1001

牛客 1 号

1900

2

算法

2020-01-01 10:00:00

21002牛客 2 号12003算法2020-01-01 10:00:00

2

1002

牛客 2 号

1200

3

算法

2020-01-01 10:00:00

31003牛客 3 号 ♂22005算法2020-01-01 10:00:00

3

1003

牛客 3 号 ♂

2200

5

算法

2020-01-01 10:00:00

41004牛客 4 号25006算法2020-01-01 10:00:00

4

1004

牛客 4 号

2500

6

算法

2020-01-01 10:00:00

51005牛客 555 号20007C++2020-01-01 10:00:00

5

1005

牛客 555 号

2000

7

C++

2020-01-01 10:00:00

6100666666630006C++2020-01-01 10:00:00

6

1006

666666

3000

6

C++

2020-01-01 10:00:00

试卷信息表 examination_info（exam_id 试卷 ID, tag 试卷类别, difficulty 试卷难度, duration 考试时长, release_time 发布时间）：

examination_info

exam_id

tag

difficulty

duration

release_time

idexam_idtagdifficultydurationrelease_time19001C++hard602020-01-01 10:00:0029002c#hard802020-01-01 10:00:0039003SQLmedium702020-01-01 10:00:00

idexam_idtagdifficultydurationrelease_time

id

exam_id

tag

difficulty

duration

release_time

19001C++hard602020-01-01 10:00:00

1

9001

C++

hard

60

2020-01-01 10:00:00

29002c#hard802020-01-01 10:00:00

2

9002

c#

hard

80

2020-01-01 10:00:00

39003SQLmedium702020-01-01 10:00:00

3

9003

SQL

medium

70

2020-01-01 10:00:00

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

exam_record

uid

exam_id

start_time

submit_time

score

iduidexam_idstart_timesubmit_timescore1100190012020-01-02 09:01:012020-01-02 09:21:59802100190012021-05-02 10:01:01(NULL)(NULL)4100190012021-06-02 19:01:012021-06-02 19:32:00203100190022021-02-02 19:01:012021-02-02 19:30:01875100190022021-09-05 19:01:012021-09-05 19:40:01896100190022021-09-01 12:01:01(NULL)(NULL)11100290012020-01-01 12:01:012020-01-01 12:31:018116100290012021-09-06 12:01:012021-09-06 12:21:018017100290012021-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:029012100290022020-02-01 12:01:012020-02-01 12:31:018213100290022020-02-02 12:11:012020-02-02 12:31:01839100390012021-09-07 10:01:012021-09-07 10:31:01898100390032021-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:018415100690012021-02-01 11:01:012021-09-01 11:31:0184

iduidexam_idstart_timesubmit_timescore

id

uid

exam_id

start_time

submit_time

score

1100190012020-01-02 09:01:012020-01-02 09:21:5980

1

1001

9001

2020-01-02 09:01:01

2020-01-02 09:21:59

80

2100190012021-05-02 10:01:01(NULL)(NULL)

2

1001

9001

2021-05-02 10:01:01

(NULL)

(NULL)

4100190012021-06-02 19:01:012021-06-02 19:32:0020

4

1001

9001

2021-06-02 19:01:01

2021-06-02 19:32:00

20

3100190022021-02-02 19:01:012021-02-02 19:30:0187

3

1001

9002

2021-02-02 19:01:01

2021-02-02 19:30:01

87

5100190022021-09-05 19:01:012021-09-05 19:40:0189

5

1001

9002

2021-09-05 19:01:01

2021-09-05 19:40:01

89

6100190022021-09-01 12:01:01(NULL)(NULL)

6

1001

9002

2021-09-01 12:01:01

(NULL)

(NULL)

11100290012020-01-01 12:01:012020-01-01 12:31:0181

11

1002

9001

2020-01-01 12:01:01

2020-01-01 12:31:01

81

16100290012021-09-06 12:01:012021-09-06 12:21:0180

16

1002

9001

2021-09-06 12:01:01

2021-09-06 12:21:01

80

17100290012021-09-06 12:01:01(NULL)(NULL)

17

1002

9001

2021-09-06 12:01:01

(NULL)

(NULL)

18100290012021-09-07 12:01:01(NULL)(NULL)

18

1002

9001

2021-09-07 12:01:01

(NULL)

(NULL)

7100290022021-05-05 18:01:012021-05-05 18:59:0290

7

1002

9002

2021-05-05 18:01:01

2021-05-05 18:59:02

90

12100290022020-02-01 12:01:012020-02-01 12:31:0182

12

1002

9002

2020-02-01 12:01:01

2020-02-01 12:31:01

82

13100290022020-02-02 12:11:012020-02-02 12:31:0183

13

1002

9002

2020-02-02 12:11:01

2020-02-02 12:31:01

83

9100390012021-09-07 10:01:012021-09-07 10:31:0189

9

1003

9001

2021-09-07 10:01:01

2021-09-07 10:31:01

89

8100390032021-02-06 12:01:01(NULL)(NULL)

8

1003

9003

2021-02-06 12:01:01

(NULL)

(NULL)

10100490022021-08-06 12:01:01(NULL)(NULL)

10

1004

9002

2021-08-06 12:01:01

(NULL)

(NULL)

14100590012021-02-01 11:01:012021-02-01 11:31:0184

14

1005

9001

2021-02-01 11:01:01

2021-02-01 11:31:01

84

15100690012021-02-01 11:01:012021-09-01 11:31:0184

15

1006

9001

2021-02-01 11:01:01

2021-09-01 11:31:01

84

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

uidexam_idavg_score1002900181100290028510059001841006900184

uidexam_idavg_score

uid

exam_id

avg_score

1002900181

1002

9001

81

1002900285

1002

9002

85

1005900184

1005

9001

84

1006900184

1006

9001

84

解释：昵称满足条件的用户有 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 '牛客%号' OR nick_name REGEXP '^[0-9]+$'

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

nick_name LIKE '^牛客[0-9]+号'

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

e_info.tag LIKE 'c%'

tag regexp '^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;

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;

根据指定记录是否存在输出不同情况（困难）

根据指定记录是否存在输出不同情况（困难）

描述：

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

user_info

uid

nick_name

achievement

level

job

register_time

iduidnick_nameachievementleveljobregister_time11001牛客 1 号190算法2020-01-01 10:00:0021002牛客 2 号12003算法2020-01-01 10:00:0031003进击的 3 号220算法2020-01-01 10:00:0041004牛客 4 号250算法2020-01-01 10:00:0051005牛客 555 号20007C++2020-01-01 10:00:006100666666630006C++2020-01-01 10:00:00

iduidnick_nameachievementleveljobregister_time

id

uid

nick_name

achievement

level

job

register_time

11001牛客 1 号190算法2020-01-01 10:00:00

1

1001

牛客 1 号

19

0

算法

2020-01-01 10:00:00

21002牛客 2 号12003算法2020-01-01 10:00:00

2

1002

牛客 2 号

1200

3

算法

2020-01-01 10:00:00

31003进击的 3 号220算法2020-01-01 10:00:00

3

1003

进击的 3 号

22

0

算法

2020-01-01 10:00:00

41004牛客 4 号250算法2020-01-01 10:00:00

4

1004

牛客 4 号

25

0

算法

2020-01-01 10:00:00

51005牛客 555 号20007C++2020-01-01 10:00:00

5

1005

牛客 555 号

2000

7

C++

2020-01-01 10:00:00

6100666666630006C++2020-01-01 10:00:00

6

1006

666666

3000

6

C++

2020-01-01 10:00:00

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

exam_record

uid

exam_id

start_time

submit_time

score

iduidexam_idstart_timesubmit_timescore1100190012020-01-02 09:01:012020-01-02 09:21:59802100190012021-05-02 10:01:01(NULL)(NULL)3100190022021-02-02 19:01:012021-02-02 19:30:01874100190022021-09-01 12:01:01(NULL)(NULL)5100190032021-09-02 12:01:01(NULL)(NULL)6100190042021-09-03 12:01:01(NULL)(NULL)7100290012020-01-01 12:01:012020-01-01 12:31:01998100290032020-02-01 12:01:012020-02-01 12:31:01829100290032020-02-02 12:11:01(NULL)(NULL)10100290022021-05-05 18:01:01(NULL)(NULL)11100290012021-09-06 12:01:01(NULL)(NULL)12100390032021-02-06 12:01:01(NULL)(NULL)13100390012021-09-07 10:01:012021-09-07 10:31:0189

iduidexam_idstart_timesubmit_timescore

id

uid

exam_id

start_time

submit_time

score

1100190012020-01-02 09:01:012020-01-02 09:21:5980

1

1001

9001

2020-01-02 09:01:01

2020-01-02 09:21:59

80

2100190012021-05-02 10:01:01(NULL)(NULL)

2

1001

9001

2021-05-02 10:01:01

(NULL)

(NULL)

3100190022021-02-02 19:01:012021-02-02 19:30:0187

3

1001

9002

2021-02-02 19:01:01

2021-02-02 19:30:01

87

4100190022021-09-01 12:01:01(NULL)(NULL)

4

1001

9002

2021-09-01 12:01:01

(NULL)

(NULL)

5100190032021-09-02 12:01:01(NULL)(NULL)

5

1001

9003

2021-09-02 12:01:01

(NULL)

(NULL)

6100190042021-09-03 12:01:01(NULL)(NULL)

6

1001

9004

2021-09-03 12:01:01

(NULL)

(NULL)

7100290012020-01-01 12:01:012020-01-01 12:31:0199

7

1002

9001

2020-01-01 12:01:01

2020-01-01 12:31:01

99

8100290032020-02-01 12:01:012020-02-01 12:31:0182

8

1002

9003

2020-02-01 12:01:01

2020-02-01 12:31:01

82

9100290032020-02-02 12:11:01(NULL)(NULL)

9

1002

9003

2020-02-02 12:11:01

(NULL)

(NULL)

10100290022021-05-05 18:01:01(NULL)(NULL)

10

1002

9002

2021-05-05 18:01:01

(NULL)

(NULL)

11100290012021-09-06 12:01:01(NULL)(NULL)

11

1002

9001

2021-09-06 12:01:01

(NULL)

(NULL)

12100390032021-02-06 12:01:01(NULL)(NULL)

12

1003

9003

2021-02-06 12:01:01

(NULL)

(NULL)

13100390012021-09-07 10:01:012021-09-07 10:31:0189

13

1003

9001

2021-09-07 10:01:01

2021-09-07 10:31:01

89

请你筛选表中的数据，当有任意一个 0 级用户未完成试卷数大于 2 时，输出每个 0 级用户的试卷未完成数和未完成率（保留 3 位小数）；若不存在这样的用户，则输出所有有作答记录的用户的这两个指标。结果按未完成率升序排序。

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

uidincomplete_cntincomplete_rate100400.000100310.500100140.667

uidincomplete_cntincomplete_rate

uid

incomplete_cnt

incomplete_rate

100400.000

1004

0

0.000

100310.500

1003

1

0.500

100140.667

1001

4

0.667

解释：0 级用户有 1001、1003、1004；他们作答试卷数和未完成数分别为：6:4、2:1、0:0；

存在 1001 这个 0 级用户未完成试卷数大于 2，因此输出这三个用户的未完成数和未完成率（1004 未作答过试卷，未完成率默认填 0，保留 3 位小数后是 0.000）；

结果按照未完成率升序排序。

附：如果 1001 不满足『未完成试卷数大于 2』，则需要输出 1001、1002、1003 的这两个指标，因为试卷作答记录表里只有这三个用户的作答记录。

思路：

先把可能满足条件“0 级用户未完成试卷数大于 2”的 SQL 写出来

SELECT ui.uid UID
FROM user_info ui
LEFT JOIN exam_record er ON ui.uid = er.uid
WHERE ui.uid IN
    (SELECT ui.uid
     FROM user_info ui
     LEFT JOIN exam_record er ON ui.uid = er.uid
     WHERE er.submit_time IS NULL
       AND ui.LEVEL = 0 )
GROUP BY ui.uid
HAVING sum(IF(er.submit_time IS NULL, 1, 0)) > 2

SELECT ui.uid UID
FROM user_info ui
LEFT JOIN exam_record er ON ui.uid = er.uid
WHERE ui.uid IN
    (SELECT ui.uid
     FROM user_info ui
     LEFT JOIN exam_record er ON ui.uid = er.uid
     WHERE er.submit_time IS NULL
       AND ui.LEVEL = 0 )
GROUP BY ui.uid
HAVING sum(IF(er.submit_time IS NULL, 1, 0)) > 2

然后再分别写出两种情况的 SQL 查询语句：

情况 1. 查询存在条件要求的 0 级用户的试卷未完成率

SELECT
	tmp1.uid uid,
	sum(
	IF
	( er.submit_time IS NULL AND er.start_time IS NOT NULL, 1, 0 )) incomplete_cnt,
	round(
		sum(
		IF
		( er.submit_time IS NULL AND er.start_time IS NOT NULL, 1, 0 ))/ count( tmp1.uid ),
		3
	) incomplete_rate
FROM
	(
	SELECT DISTINCT
		ui.uid
	FROM
		user_info ui
		LEFT JOIN exam_record er ON ui.uid = er.uid
	WHERE
		er.submit_time IS NULL
		AND ui.LEVEL = 0
	) tmp1
	LEFT JOIN exam_record er ON tmp1.uid = er.uid
GROUP BY
	tmp1.uid
ORDER BY
	incomplete_rate

SELECT
	tmp1.uid uid,
	sum(
	IF
	( er.submit_time IS NULL AND er.start_time IS NOT NULL, 1, 0 )) incomplete_cnt,
	round(
		sum(
		IF
		( er.submit_time IS NULL AND er.start_time IS NOT NULL, 1, 0 ))/ count( tmp1.uid ),
		3
	) incomplete_rate
FROM
	(
	SELECT DISTINCT
		ui.uid
	FROM
		user_info ui
		LEFT JOIN exam_record er ON ui.uid = er.uid
	WHERE
		er.submit_time IS NULL
		AND ui.LEVEL = 0
	) tmp1
	LEFT JOIN exam_record er ON tmp1.uid = er.uid
GROUP BY
	tmp1.uid
ORDER BY
	incomplete_rate

情况 2. 查询不存在条件要求时所有有作答记录的 yong 用户的试卷未完成率

SELECT
	ui.uid uid,
	sum( CASE WHEN er.submit_time IS NULL AND er.start_time IS NOT NULL THEN 1 ELSE 0 END ) incomplete_cnt,
	round(
		sum(
		IF
		( er.submit_time IS NULL AND er.start_time IS NOT NULL, 1, 0 ))/ count( ui.uid ),
		3
	) incomplete_rate
FROM
	user_info ui
	JOIN exam_record er ON ui.uid = er.uid
GROUP BY
	ui.uid
ORDER BY
	incomplete_rate

SELECT
	ui.uid uid,
	sum( CASE WHEN er.submit_time IS NULL AND er.start_time IS NOT NULL THEN 1 ELSE 0 END ) incomplete_cnt,
	round(
		sum(
		IF
		( er.submit_time IS NULL AND er.start_time IS NOT NULL, 1, 0 ))/ count( ui.uid ),
		3
	) incomplete_rate
FROM
	user_info ui
	JOIN exam_record er ON ui.uid = er.uid
GROUP BY
	ui.uid
ORDER BY
	incomplete_rate

拼在一起，就是答案

WITH host_user AS
  (SELECT ui.uid UID
   FROM user_info ui
   LEFT JOIN exam_record er ON ui.uid = er.uid
   WHERE ui.uid IN
       (SELECT ui.uid
        FROM user_info ui
        LEFT JOIN exam_record er ON ui.uid = er.uid
        WHERE er.submit_time IS NULL
          AND ui.LEVEL = 0 )
   GROUP BY ui.uid
   HAVING sum(IF (er.submit_time IS NULL, 1, 0))> 2),
     tt1 AS
  (SELECT tmp1.uid UID,
                   sum(IF (er.submit_time IS NULL
                           AND er.start_time IS NOT NULL, 1, 0)) incomplete_cnt,
                   round(sum(IF (er.submit_time IS NULL
                                 AND er.start_time IS NOT NULL, 1, 0))/ count(tmp1.uid), 3) incomplete_rate
   FROM
     (SELECT DISTINCT ui.uid
      FROM user_info ui
      LEFT JOIN exam_record er ON ui.uid = er.uid
      WHERE er.submit_time IS NULL
        AND ui.LEVEL = 0 ) tmp1
   LEFT JOIN exam_record er ON tmp1.uid = er.uid
   GROUP BY tmp1.uid
   ORDER BY incomplete_rate),
     tt2 AS
  (SELECT ui.uid UID,
                 sum(CASE
                         WHEN er.submit_time IS NULL
                              AND er.start_time IS NOT NULL THEN 1
                         ELSE 0
                     END) incomplete_cnt,
                 round(sum(IF (er.submit_time IS NULL
                               AND er.start_time IS NOT NULL, 1, 0))/ count(ui.uid), 3) incomplete_rate
   FROM user_info ui
   JOIN exam_record er ON ui.uid = er.uid
   GROUP BY ui.uid
   ORDER BY incomplete_rate)
  (SELECT tt1.*
   FROM tt1
   LEFT JOIN
     (SELECT UID
      FROM host_user) t1 ON 1 = 1
   WHERE t1.uid IS NOT NULL )
UNION ALL
  (SELECT tt2.*
   FROM tt2
   LEFT JOIN
     (SELECT UID
      FROM host_user) t2 ON 1 = 1
   WHERE t2.uid IS NULL)

WITH host_user AS
  (SELECT ui.uid UID
   FROM user_info ui
   LEFT JOIN exam_record er ON ui.uid = er.uid
   WHERE ui.uid IN
       (SELECT ui.uid
        FROM user_info ui
        LEFT JOIN exam_record er ON ui.uid = er.uid
        WHERE er.submit_time IS NULL
          AND ui.LEVEL = 0 )
   GROUP BY ui.uid
   HAVING sum(IF (er.submit_time IS NULL, 1, 0))> 2),
     tt1 AS
  (SELECT tmp1.uid UID,
                   sum(IF (er.submit_time IS NULL
                           AND er.start_time IS NOT NULL, 1, 0)) incomplete_cnt,
                   round(sum(IF (er.submit_time IS NULL
                                 AND er.start_time IS NOT NULL, 1, 0))/ count(tmp1.uid), 3) incomplete_rate
   FROM
     (SELECT DISTINCT ui.uid
      FROM user_info ui
      LEFT JOIN exam_record er ON ui.uid = er.uid
      WHERE er.submit_time IS NULL
        AND ui.LEVEL = 0 ) tmp1
   LEFT JOIN exam_record er ON tmp1.uid = er.uid
   GROUP BY tmp1.uid
   ORDER BY incomplete_rate),
     tt2 AS
  (SELECT ui.uid UID,
                 sum(CASE
                         WHEN er.submit_time IS NULL
                              AND er.start_time IS NOT NULL THEN 1
                         ELSE 0
                     END) incomplete_cnt,
                 round(sum(IF (er.submit_time IS NULL
                               AND er.start_time IS NOT NULL, 1, 0))/ count(ui.uid), 3) incomplete_rate
   FROM user_info ui
   JOIN exam_record er ON ui.uid = er.uid
   GROUP BY ui.uid
   ORDER BY incomplete_rate)
  (SELECT tt1.*
   FROM tt1
   LEFT JOIN
     (SELECT UID
      FROM host_user) t1 ON 1 = 1
   WHERE t1.uid IS NOT NULL )
UNION ALL
  (SELECT tt2.*
   FROM tt2
   LEFT JOIN
     (SELECT UID
      FROM host_user) t2 ON 1 = 1
   WHERE t2.uid IS NULL)

V2 版本（根据上面做出的改进，答案缩短了，逻辑更强）：

SELECT
	ui.uid,
	SUM(
	IF
	( start_time IS NOT NULL AND score IS NULL, 1, 0 )) AS incomplete_cnt,#3.试卷未完成数
	ROUND( AVG( IF ( start_time IS NOT NULL AND score IS NULL, 1, 0 )), 3 ) AS incomplete_rate #4.未完成率

FROM
	user_info ui
	LEFT JOIN exam_record USING ( uid )
WHERE
CASE

		WHEN (#1.当有任意一个0级用户未完成试卷数大于2时
		SELECT
			MAX( lv0_incom_cnt )
		FROM
			(
			SELECT
				SUM(
				IF
				( score IS NULL, 1, 0 )) AS lv0_incom_cnt
			FROM
				user_info
				JOIN exam_record USING ( uid )
			WHERE
				LEVEL = 0
			GROUP BY
				uid
			) table1
			)> 2 THEN
			uid IN ( #1.1找出每个0级用户
			SELECT uid FROM user_info WHERE LEVEL = 0 ) ELSE uid IN ( #2.若不存在这样的用户，找出有作答记录的用户
			SELECT DISTINCT uid FROM exam_record )
		END
		GROUP BY
			ui.uid
	ORDER BY
	incomplete_rate #5.结果按未完成率升序排序

SELECT
	ui.uid,
	SUM(
	IF
	( start_time IS NOT NULL AND score IS NULL, 1, 0 )) AS incomplete_cnt,#3.试卷未完成数
	ROUND( AVG( IF ( start_time IS NOT NULL AND score IS NULL, 1, 0 )), 3 ) AS incomplete_rate #4.未完成率

FROM
	user_info ui
	LEFT JOIN exam_record USING ( uid )
WHERE
CASE

		WHEN (#1.当有任意一个0级用户未完成试卷数大于2时
		SELECT
			MAX( lv0_incom_cnt )
		FROM
			(
			SELECT
				SUM(
				IF
				( score IS NULL, 1, 0 )) AS lv0_incom_cnt
			FROM
				user_info
				JOIN exam_record USING ( uid )
			WHERE
				LEVEL = 0
			GROUP BY
				uid
			) table1
			)> 2 THEN
			uid IN ( #1.1找出每个0级用户
			SELECT uid FROM user_info WHERE LEVEL = 0 ) ELSE uid IN ( #2.若不存在这样的用户，找出有作答记录的用户
			SELECT DISTINCT uid FROM exam_record )
		END
		GROUP BY
			ui.uid
	ORDER BY
	incomplete_rate #5.结果按未完成率升序排序

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

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

描述：

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

user_info

uid

nick_name

achievement

level

job

register_time

iduidnick_nameachievementleveljobregister_time11001牛客 1 号190算法2020-01-01 10:00:0021002牛客 2 号12003算法2020-01-01 10:00:0031003牛客 3 号 ♂220算法2020-01-01 10:00:0041004牛客 4 号250算法2020-01-01 10:00:0051005牛客 555 号20007C++2020-01-01 10:00:006100666666630006C++2020-01-01 10:00:00

iduidnick_nameachievementleveljobregister_time

id

uid

nick_name

achievement

level

job

register_time

11001牛客 1 号190算法2020-01-01 10:00:00

1

1001

牛客 1 号

19

0

算法

2020-01-01 10:00:00

21002牛客 2 号12003算法2020-01-01 10:00:00

2

1002

牛客 2 号

1200

3

算法

2020-01-01 10:00:00

31003牛客 3 号 ♂220算法2020-01-01 10:00:00

3

1003

牛客 3 号 ♂

22

0

算法

2020-01-01 10:00:00

41004牛客 4 号250算法2020-01-01 10:00:00

4

1004

牛客 4 号

25

0

算法

2020-01-01 10:00:00

51005牛客 555 号20007C++2020-01-01 10:00:00

5

1005

牛客 555 号

2000

7

C++

2020-01-01 10:00:00

6100666666630006C++2020-01-01 10:00:00

6

1006

666666

3000

6

C++

2020-01-01 10:00:00

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

iduidexam_idstart_timesubmit_timescore1100190012020-01-02 09:01:012020-01-02 09:21:59802100190012021-05-02 10:01:01(NULL)(NULL)3100190022021-02-02 19:01:012021-02-02 19:30:01754100190022021-09-01 12:01:012021-09-01 12:11:01605100190032021-09-02 12:01:012021-09-02 12:41:01906100190012021-06-02 19:01:012021-06-02 19:32:00207100190022021-09-05 19:01:012021-09-05 19:40:01898100190042021-09-03 12:01:01(NULL)(NULL)9100290012020-01-01 12:01:012020-01-01 12:31:019910100290032020-02-01 12:01:012020-02-01 12:31:018211100290032020-02-02 12:11:012020-02-02 12:41:0176

iduidexam_idstart_timesubmit_timescore

id

uid

exam_id

start_time

submit_time

score

1100190012020-01-02 09:01:012020-01-02 09:21:5980

1

1001

9001

2020-01-02 09:01:01

2020-01-02 09:21:59

80

2100190012021-05-02 10:01:01(NULL)(NULL)

2

1001

9001

2021-05-02 10:01:01

(NULL)

(NULL)

3100190022021-02-02 19:01:012021-02-02 19:30:0175

3

1001

9002

2021-02-02 19:01:01

2021-02-02 19:30:01

75

4100190022021-09-01 12:01:012021-09-01 12:11:0160

4

1001

9002

2021-09-01 12:01:01

2021-09-01 12:11:01

60

5100190032021-09-02 12:01:012021-09-02 12:41:0190

5

1001

9003

2021-09-02 12:01:01

2021-09-02 12:41:01

90

6100190012021-06-02 19:01:012021-06-02 19:32:0020

6

1001

9001

2021-06-02 19:01:01

2021-06-02 19:32:00

20

7100190022021-09-05 19:01:012021-09-05 19:40:0189

7

1001

9002

2021-09-05 19:01:01

2021-09-05 19:40:01

89

8100190042021-09-03 12:01:01(NULL)(NULL)

8

1001

9004

2021-09-03 12:01:01

(NULL)

(NULL)

9100290012020-01-01 12:01:012020-01-01 12:31:0199

9

1002

9001

2020-01-01 12:01:01

2020-01-01 12:31:01

99

10100290032020-02-01 12:01:012020-02-01 12:31:0182

10

1002

9003

2020-02-01 12:01:01

2020-02-01 12:31:01

82

11100290032020-02-02 12:11:012020-02-02 12:41:0176

11

1002

9003

2020-02-02 12:11:01

2020-02-02 12:41:01

76

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

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

levelscore_graderatio3良0.6673优0.3330良0.5000中0.1670优0.1670差0.167

levelscore_graderatio

level

score_grade

ratio

3良0.667

3

良

0.667

3优0.333

3

优

0.333

0良0.500

0

良

0.500

0中0.167

0

中

0.167

0优0.167

0

优

0.167

0差0.167

0

差

0.167

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

uidexam_idscorelevelscore_grade10019001800良10019002750良10019002600中10019003900优10019001200差10019002890良10029001993优10029003823良10029003763良

uidexam_idscorelevelscore_grade

uid

exam_id

score

level

score_grade

10019001800良

1001

9001

80

0

良

10019002750良

1001

9002

75

0

良

10019002600中

1001

9002

60

0

中

10019003900优

1001

9003

90

0

优

10019001200差

1001

9001

20

0

差

10019002890良

1001

9002

89

0

良

10029001993优

1002

9001

99

3

优

10029003823良

1002

9003

82

3

良

10029003763良

1002

9003

76

3

良

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

思路：

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

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

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

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

限量查询

限量查询

注册时间最早的三个人

注册时间最早的三个人

描述：

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

user_info

uid

nick_name

achievement

level

job

register_time

iduidnick_nameachievementleveljobregister_time11001牛客 1 号190算法2020-01-01 10:00:0021002牛客 2 号12003算法2020-02-01 10:00:0031003牛客 3 号 ♂220算法2020-01-02 10:00:0041004牛客 4 号250算法2020-01-02 11:00:0051005牛客 555 号40007C++2020-01-11 10:00:006100666666630006C++2020-11-01 10:00:00

iduidnick_nameachievementleveljobregister_time

id

uid

nick_name

achievement

level

job

register_time

11001牛客 1 号190算法2020-01-01 10:00:00

1

1001

牛客 1 号

19

0

算法

2020-01-01 10:00:00

21002牛客 2 号12003算法2020-02-01 10:00:00

2

1002

牛客 2 号

1200

3

算法

2020-02-01 10:00:00

31003牛客 3 号 ♂220算法2020-01-02 10:00:00

3

1003

牛客 3 号 ♂

22

0

算法

2020-01-02 10:00:00

41004牛客 4 号250算法2020-01-02 11:00:00

4

1004

牛客 4 号

25

0

算法

2020-01-02 11:00:00

51005牛客 555 号40007C++2020-01-11 10:00:00

5

1005

牛客 555 号

4000

7

C++

2020-01-11 10:00:00

6100666666630006C++2020-11-01 10:00:00

6

1006

666666

3000

6

C++

2020-11-01 10:00:00

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

uidnick_nameregister_time1001牛客 12020-01-01 10:00:001003牛客 3 号 ♂2020-01-02 10:00:001004牛客 4 号2020-01-02 11:00:00

uidnick_nameregister_time

uid

nick_name

register_time

1001牛客 12020-01-01 10:00:00

1001

牛客 1

2020-01-01 10:00:00

1003牛客 3 号 ♂2020-01-02 10:00:00

1003

牛客 3 号 ♂

2020-01-02 10:00:00

1004牛客 4 号2020-01-02 11: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

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

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

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

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

user_info

uid

nick_name

achievement

level

job

register_time

iduidnick_nameachievementleveljobregister_time11001牛客 1190算法2020-01-01 10:00:0021002牛客 2 号12003算法2020-01-01 10:00:0031003牛客 3 号 ♂220算法2020-01-01 10:00:0041004牛客 4 号250算法2020-01-01 10:00:0051005牛客 555 号40007算法2020-01-11 10:00:0061006牛客 6 号250算法2020-01-02 11:00:0071007牛客 7 号250算法2020-01-02 11:00:0081008牛客 8 号250算法2020-01-02 11:00:0091009牛客 9 号250算法2020-01-02 11:00:00101010牛客 10 号250算法2020-01-02 11:00:0011101166666630006C++2020-01-02 10:00:00

iduidnick_nameachievementleveljobregister_time

id

uid

nick_name

achievement

level

job

register_time

11001牛客 1190算法2020-01-01 10:00:00

1

1001

牛客 1

19

0

算法

2020-01-01 10:00:00

21002牛客 2 号12003算法2020-01-01 10:00:00

2

1002

牛客 2 号

1200

3

算法

2020-01-01 10:00:00

31003牛客 3 号 ♂220算法2020-01-01 10:00:00

3

1003

牛客 3 号 ♂

22

0

算法

2020-01-01 10:00:00

41004牛客 4 号250算法2020-01-01 10:00:00

4

1004

牛客 4 号

25

0

算法

2020-01-01 10:00:00

51005牛客 555 号40007算法2020-01-11 10:00:00

5

1005

牛客 555 号

4000

7

算法

2020-01-11 10:00:00

61006牛客 6 号250算法2020-01-02 11:00:00

6

1006

牛客 6 号

25

0

算法

2020-01-02 11:00:00

71007牛客 7 号250算法2020-01-02 11:00:00

7

1007

牛客 7 号

25

0

算法

2020-01-02 11:00:00

81008牛客 8 号250算法2020-01-02 11:00:00

8

1008

牛客 8 号

25

0

算法

2020-01-02 11:00:00

91009牛客 9 号250算法2020-01-02 11:00:00

9

1009

牛客 9 号

25

0

算法

2020-01-02 11:00:00

101010牛客 10 号250算法2020-01-02 11:00:00

10

1010

牛客 10 号

25

0

算法

2020-01-02 11:00:00

11101166666630006C++2020-01-02 10:00:00

11

1011

666666

3000

6

C++

2020-01-02 10:00:00

试卷信息表 examination_info（exam_id 试卷 ID, tag 试卷类别, difficulty 试卷难度, duration 考试时长, release_time 发布时间）：

idexam_idtagdifficultydurationrelease_time19001算法hard602020-01-01 10:00:0029002算法hard802020-01-01 10:00:0039003SQLmedium702020-01-01 10:00:00

idexam_idtagdifficultydurationrelease_time

id

exam_id

tag

difficulty

duration

release_time

19001算法hard602020-01-01 10:00:00

1

9001

算法

hard

60

2020-01-01 10:00:00

29002算法hard802020-01-01 10:00:00

2

9002

算法

hard

80

2020-01-01 10:00:00

39003SQLmedium702020-01-01 10:00:00

3

9003

SQL

medium

70

2020-01-01 10:00:00

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

exam_record

uid

exam_id

start_time

submit_time

score

iduidexam_idstart_timesubmit_timescore1100190012020-01-02 09:01:012020-01-02 09:21:59802100290032020-01-20 10:01:012020-01-20 10:10:01813100290022020-01-01 12:11:012020-01-01 12:31:01834100390022020-01-01 19:01:012020-01-01 19:30:01755100490022020-01-01 12:01:012020-01-01 12:11:01606100590022020-01-01 12:01:012020-01-01 12:41:01907100690012020-01-02 19:01:012020-01-02 19:32:00208100790022020-01-02 19:01:012020-01-02 19:40:01899100890032020-01-02 12:01:012020-01-02 12:20:019910100890012020-01-02 12:01:012020-01-02 12:31:019811100990022020-01-02 12:01:012020-01-02 12:31:018212101090022020-01-02 12:11:012020-01-02 12:41:017613101190012020-01-02 10:01:012020-01-02 10:31:0189

iduidexam_idstart_timesubmit_timescore

id

uid

exam_id

start_time

submit_time

score

1100190012020-01-02 09:01:012020-01-02 09:21:5980

1

1001

9001

2020-01-02 09:01:01

2020-01-02 09:21:59

80

2100290032020-01-20 10:01:012020-01-20 10:10:0181

2

1002

9003

2020-01-20 10:01:01

2020-01-20 10:10:01

81

3100290022020-01-01 12:11:012020-01-01 12:31:0183

3

1002

9002

2020-01-01 12:11:01

2020-01-01 12:31:01

83

4100390022020-01-01 19:01:012020-01-01 19:30:0175

4

1003

9002

2020-01-01 19:01:01

2020-01-01 19:30:01

75

5100490022020-01-01 12:01:012020-01-01 12:11:0160

5

1004

9002

2020-01-01 12:01:01

2020-01-01 12:11:01

60

6100590022020-01-01 12:01:012020-01-01 12:41:0190

6

1005

9002

2020-01-01 12:01:01

2020-01-01 12:41:01

90

7100690012020-01-02 19:01:012020-01-02 19:32:0020

7

1006

9001

2020-01-02 19:01:01

2020-01-02 19:32:00

20

8100790022020-01-02 19:01:012020-01-02 19:40:0189

8

1007

9002

2020-01-02 19:01:01

2020-01-02 19:40:01

89

9100890032020-01-02 12:01:012020-01-02 12:20:0199

9

1008

9003

2020-01-02 12:01:01

2020-01-02 12:20:01

99

10100890012020-01-02 12:01:012020-01-02 12:31:0198

10

1008

9001

2020-01-02 12:01:01

2020-01-02 12:31:01

98

11100990022020-01-02 12:01:012020-01-02 12:31:0182

11

1009

9002

2020-01-02 12:01:01

2020-01-02 12:31:01

82

12101090022020-01-02 12:11:012020-01-02 12:41:0176

12

1010

9002

2020-01-02 12:11:01

2020-01-02 12:41:01

76

13101190012020-01-02 10:01:012020-01-02 10:31:0189

13

1011

9001

2020-01-02 10:01:01

2020-01-02 10:31:01

89

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

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

uidlevelregister_timemax_score101002020-01-02 11:00:0076100302020-01-01 10:00:0075100402020-01-01 11:00:0060

uidlevelregister_timemax_score

uid

level

register_time

max_score

101002020-01-02 11:00:0076

1010

0

2020-01-02 11:00:00

76

100302020-01-01 10:00:0075

1003

0

2020-01-01 10:00:00

75

100402020-01-01 11:00:0060

1004

0

2020-01-01 11:00:00

60

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

按最高分排名如下：

uidlevelregister_timemax_score100802020-01-02 11:00:0099100572020-01-01 10:00:0090100702020-01-02 11:00:0089100232020-01-01 10:00:0083100902020-01-02 11:00:0082100102020-01-01 10:00:0080101002020-01-02 11:00:0076100302020-01-01 10:00:0075100402020-01-01 11:00:0060100602020-01-02 11:00:0020

uidlevelregister_timemax_score

uid

level

register_time

max_score

100802020-01-02 11:00:0099

1008

0

2020-01-02 11:00:00

99

100572020-01-01 10:00:0090

1005

7

2020-01-01 10:00:00

90

100702020-01-02 11:00:0089

1007

0

2020-01-02 11:00:00

89

100232020-01-01 10:00:0083

1002

3

2020-01-01 10:00:00

83

100902020-01-02 11:00:0082

1009

0

2020-01-02 11:00:00

82

100102020-01-01 10:00:0080

1001

0

2020-01-01 10:00:00

80

101002020-01-02 11:00:0076

1010

0

2020-01-02 11:00:00

76

100302020-01-01 10:00:0075

1003

0

2020-01-01 10:00:00

75

100402020-01-01 11:00:0060

1004

0

2020-01-01 11:00:00

60

100602020-01-02 11:00:0020

1006

0

2020-01-02 11:00:00

20

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

思路：

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

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

limit

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

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

答案：

SELECT t1.uid,
       LEVEL,
       register_time,
       max(score) AS max_score
FROM exam_record t
JOIN examination_info USING (exam_id)
JOIN user_info t1 ON t.uid = t1.uid
AND date(t.submit_time) = date(t1.register_time)
WHERE job = '算法'
  AND tag = '算法'
GROUP BY t1.uid,
         LEVEL,
         register_time
ORDER BY max_score DESC
LIMIT 6,3

SELECT t1.uid,
       LEVEL,
       register_time,
       max(score) AS max_score
FROM exam_record t
JOIN examination_info USING (exam_id)
JOIN user_info t1 ON t.uid = t1.uid
AND date(t.submit_time) = date(t1.register_time)
WHERE job = '算法'
  AND tag = '算法'
GROUP BY t1.uid,
         LEVEL,
         register_time
ORDER BY max_score DESC
LIMIT 6,3

文本转换函数

文本转换函数

修复串列了的记录

修复串列了的记录

描述：现有试卷信息表 examination_info（exam_id 试卷 ID, tag 试卷类别, difficulty 试卷难度, duration 考试时长, release_time 发布时间）：

examination_info

exam_id

tag

difficulty

duration

release_time

idexam_idtagdifficultydurationrelease_time19001算法hard602021-01-01 10:00:0029002算法hard802021-01-01 10:00:0039003SQLmedium702021-01-01 10:00:0049004算法,medium,8002021-01-01 10:00:00

idexam_idtagdifficultydurationrelease_time

id

exam_id

tag

difficulty

duration

release_time

19001算法hard602021-01-01 10:00:00

1

9001

算法

hard

60

2021-01-01 10:00:00

29002算法hard802021-01-01 10:00:00

2

9002

算法

hard

80

2021-01-01 10:00:00

39003SQLmedium702021-01-01 10:00:00

3

9003

SQL

medium

70

2021-01-01 10:00:00

49004算法,medium,8002021-01-01 10:00:00

4

9004

算法,medium,80

0

2021-01-01 10:00:00

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

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

exam_idtagdifficultyduration9004算法medium80

exam_idtagdifficultyduration

exam_id

tag

difficulty

duration

9004算法medium80

9004

算法

medium

80

思路：

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

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

SUBSTRING_INDEX

以下是 SUBSTRING_INDEX 函数的语法：

SUBSTRING_INDEX

SUBSTRING_INDEX(str, delimiter,