文章来源于网络收集而来，版权归原创者所有，如有侵权请及时联系！

高级条件语句

发布于 2024-08-17 15:49:00 字数 25033 浏览 0 评论 0 收藏 0

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

描述：

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

id	uid	nick_name	achievement	level	job	register_time
1	1001	牛客 1 号	1000	2	算法	2020-01-01 10:00:00
2	1002	牛客 2 号	1200	3	算法	2020-01-01 10:00:00
3	1003	进击的 3 号	2200	5	算法	2020-01-01 10:00:00
4	1004	牛客 4 号	2500	6	算法	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 得分）：

id	uid	exam_id	start_time	submit_time	score
1	1001	9001	2020-01-02 09:01:01	2020-01-02 09:21:59	80
3	1001	9002	2021-02-02 19:01:01	2021-02-02 19:30:01	87
2	1001	9001	2021-05-02 10:01:01	(NULL)	(NULL)
4	1001	9001	2021-06-02 19:01:01	2021-06-02 19:32:00	20
6	1001	9002	2021-09-01 12:01:01	(NULL)	(NULL)
5	1001	9002	2021-09-05 19:01:01	2021-09-05 19:40:01	89
11	1002	9001	2020-01-01 12:01:01	2020-01-01 12:31:01	81
12	1002	9002	2020-02-01 12:01:01	2020-02-01 12:31:01	82
13	1002	9002	2020-02-02 12:11:01	2020-02-02 12:31:01	83
7	1002	9002	2021-05-05 18:01:01	2021-05-05 18:59:02	90
16	1002	9001	2021-09-06 12:01:01	2021-09-06 12:21:01	80
17	1002	9001	2021-09-06 12:01:01	(NULL)	(NULL)
18	1002	9001	2021-09-07 12:01:01	(NULL)	(NULL)
8	1003	9003	2021-02-06 12:01:01	(NULL)	(NULL)
9	1003	9001	2021-09-07 10:01:01	2021-09-07 10:31:01	89
10	1004	9002	2021-08-06 12:01:01	(NULL)	(NULL)
14	1005	9001	2021-02-01 11:01:01	2021-02-01 11:31:01	84
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 得分）：

id	uid	question_id	submit_time	score
1	1001	8001	2021-08-02 11:41:01	60
2	1002	8001	2021-09-02 19:30:01	50
3	1002	8001	2021-09-02 19:20:01	70
4	1002	8002	2021-09-02 19:38:01	70
5	1003	8002	2021-09-01 19:38:01	80

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

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

uid	nick_name	achievement
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

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

描述：

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

id	uid	nick_name	achievement	level	job	register_time
1	1001	牛客 1 号	1900	2	算法	2020-01-01 10:00:00
2	1002	牛客 2 号	1200	3	算法	2020-01-01 10:00:00
3	1003	牛客 3 号 ♂	2200	5	算法	2020-01-01 10:00:00
4	1004	牛客 4 号	2500	6	算法	2020-01-01 10:00:00
5	1005	牛客 555 号	2000	7	C++	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 发布时间）：

id	exam_id	tag	difficulty	duration	release_time
1	9001	C++	hard	60	2020-01-01 10:00:00
2	9002	c#	hard	80	2020-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 得分）：

id	uid	exam_id	start_time	submit_time	score
1	1001	9001	2020-01-02 09:01:01	2020-01-02 09:21:59	80
2	1001	9001	2021-05-02 10:01:01	(NULL)	(NULL)
4	1001	9001	2021-06-02 19:01:01	2021-06-02 19:32:00	20
3	1001	9002	2021-02-02 19:01:01	2021-02-02 19:30:01	87
5	1001	9002	2021-09-05 19:01:01	2021-09-05 19:40:01	89
6	1001	9002	2021-09-01 12:01:01	(NULL)	(NULL)
11	1002	9001	2020-01-01 12:01:01	2020-01-01 12:31:01	81
16	1002	9001	2021-09-06 12:01:01	2021-09-06 12:21:01	80
17	1002	9001	2021-09-06 12:01:01	(NULL)	(NULL)
18	1002	9001	2021-09-07 12:01:01	(NULL)	(NULL)
7	1002	9002	2021-05-05 18:01:01	2021-05-05 18:59:02	90
12	1002	9002	2020-02-01 12:01:01	2020-02-01 12:31:01	82
13	1002	9002	2020-02-02 12:11:01	2020-02-02 12:31:01	83
9	1003	9001	2021-09-07 10:01:01	2021-09-07 10:31:01	89
8	1003	9003	2021-02-06 12:01:01	(NULL)	(NULL)
10	1004	9002	2021-08-06 12:01:01	(NULL)	(NULL)
14	1005	9001	2021-02-01 11:01:01	2021-02-01 11:31:01	84
15	1006	9001	2021-02-01 11:01:01	2021-09-01 11:31:01	84

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

uid	exam_id	avg_score
1002	9001	81
1002	9002	85
1005	9001	84
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 '^牛客[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;

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

描述：

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

id	uid	nick_name	achievement	level	job	register_time
1	1001	牛客 1 号	19	0	算法	2020-01-01 10:00:00
2	1002	牛客 2 号	1200	3	算法	2020-01-01 10:00:00
3	1003	进击的 3 号	22	0	算法	2020-01-01 10:00:00
4	1004	牛客 4 号	25	0	算法	2020-01-01 10:00:00
5	1005	牛客 555 号	2000	7	C++	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 得分）：

id	uid	exam_id	start_time	submit_time	score
1	1001	9001	2020-01-02 09:01:01	2020-01-02 09:21:59	80
2	1001	9001	2021-05-02 10:01:01	(NULL)	(NULL)
3	1001	9002	2021-02-02 19:01:01	2021-02-02 19:30:01	87
4	1001	9002	2021-09-01 12:01:01	(NULL)	(NULL)
5	1001	9003	2021-09-02 12:01:01	(NULL)	(NULL)
6	1001	9004	2021-09-03 12:01:01	(NULL)	(NULL)
7	1002	9001	2020-01-01 12:01:01	2020-01-01 12:31:01	99
8	1002	9003	2020-02-01 12:01:01	2020-02-01 12:31:01	82
9	1002	9003	2020-02-02 12:11:01	(NULL)	(NULL)
10	1002	9002	2021-05-05 18:01:01	(NULL)	(NULL)
11	1002	9001	2021-09-06 12:01:01	(NULL)	(NULL)
12	1003	9003	2021-02-06 12:01:01	(NULL)	(NULL)
13	1003	9001	2021-09-07 10:01:01	2021-09-07 10:31:01	89

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

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

uid	incomplete_cnt	incomplete_rate
1004	0	0.000
1003	1	0.500
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

然后再分别写出两种情况的 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

情况 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

拼在一起，就是答案

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.结果按未完成率升序排序

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

描述：

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

id	uid	nick_name	achievement	level	job	register_time
1	1001	牛客 1 号	19	0	算法	2020-01-01 10:00:00
2	1002	牛客 2 号	1200	3	算法	2020-01-01 10:00:00
3	1003	牛客 3 号 ♂	22	0	算法	2020-01-01 10:00:00
4	1004	牛客 4 号	25	0	算法	2020-01-01 10:00:00
5	1005	牛客 555 号	2000	7	C++	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 得分）：

id	uid	exam_id	start_time	submit_time	score
1	1001	9001	2020-01-02 09:01:01	2020-01-02 09:21:59	80
2	1001	9001	2021-05-02 10:01:01	(NULL)	(NULL)
3	1001	9002	2021-02-02 19:01:01	2021-02-02 19:30:01	75
4	1001	9002	2021-09-01 12:01:01	2021-09-01 12:11:01	60
5	1001	9003	2021-09-02 12:01:01	2021-09-02 12:41:01	90
6	1001	9001	2021-06-02 19:01:01	2021-06-02 19:32:00	20
7	1001	9002	2021-09-05 19:01:01	2021-09-05 19:40:01	89
8	1001	9004	2021-09-03 12:01:01	(NULL)	(NULL)
9	1002	9001	2020-01-01 12:01:01	2020-01-01 12:31:01	99
10	1002	9003	2020-02-01 12:01:01	2020-02-01 12:31:01	82
11	1002	9003	2020-02-02 12:11:01	2020-02-02 12:41:01	76

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

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

level	score_grade	ratio
3	良	0.667
3	优	0.333
0	良	0.500
0	中	0.167
0	优	0.167
0	差	0.167

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

uid	exam_id	score	level	score_grade
1001	9001	80	0	良
1001	9002	75	0	良
1001	9002	60	0	中
1001	9003	90	0	优
1001	9001	20	0	差
1001	9002	89	0	良
1002	9001	99	3	优
1002	9003	82	3	良
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 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

分享到QQ

分享到微博