Question

修复串列了的记录

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

id	exam_id	tag	difficulty	duration	release_time
1	9001	算法	hard	60	2021-01-01 10:00:00
2	9002	算法	hard	80	2021-01-01 10:00:00
3	9003	SQL	medium	70	2021-01-01 10:00:00
4	9004	算法,medium,80		0	2021-01-01 10:00:00

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

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

exam_id	tag	difficulty	duration
9004	算法	medium	80

思路 ：

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

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'
   

2. 提取字符串中的最后一个部分：

   SELECT SUBSTRING_INDEX('apple,banana,cherry', ',', -1);
   -- 输出结果：'cherry'
   

3. 提取字符串中的前两个部分：

   SELECT SUBSTRING_INDEX('apple,banana,cherry', ',', 2);
   -- 输出结果：'apple,banana'
   

4. 提取字符串中的最后两个部分：

   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 = ''

对过长的昵称截取处理

描述 ：现有用户信息表 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 11:00:00
5	1005	牛客 5678901234 号	4000	7	算法	2020-01-11 10:00:00
6	1006	牛客 67890123456789 号	25	0	算法	2020-01-02 11:00:00

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

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

uid	nick_name
1005	牛客 5678901234 号
1006	牛客 67890123...

解释：字符数大于 10 的用户有 1005 和 1006，长度分别为 13、17；因此需要对 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 ) > 13 THEN
        CONCAT( SUBSTR( nick_name, 1, 10 ), '...' ) ELSE nick_name
    END AS nick_name
FROM
    user_info
WHERE
    CHAR_LENGTH( nick_name ) > 10
GROUP BY
    uid;

大小写混乱时的筛选统计（较难）

描述 ：

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

id	exam_id	tag	difficulty	duration	release_time
1	9001	算法	hard	60	2021-01-01 10:00:00
2	9002	C++	hard	80	2021-01-01 10:00:00
3	9003	C++	hard	80	2021-01-01 10:00:00
4	9004	sql	medium	70	2021-01-01 10:00:00
5	9005	C++	hard	80	2021-01-01 10:00:00
6	9006	C++	hard	80	2021-01-01 10:00:00
7	9007	C++	hard	80	2021-01-01 10:00:00
8	9008	SQL	medium	70	2021-01-01 10:00:00
9	9009	SQL	medium	70	2021-01-01 10:00:00
10	9010	SQL	medium	70	2021-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-01 09:01:01	2020-01-01 09:21:59	80
2	1002	9003	2020-01-20 10:01:01	2020-01-20 10:10:01	81
3	1002	9002	2020-02-01 12:11:01	2020-02-01 12:31:01	83
4	1003	9002	2020-03-01 19:01:01	2020-03-01 19:30:01	75
5	1004	9002	2020-03-01 12:01:01	2020-03-01 12:11:01	60
6	1005	9002	2020-03-01 12:01:01	2020-03-01 12:41:01	90
7	1006	9001	2020-05-02 19:01:01	2020-05-02 19:32:00	20
8	1007	9003	2020-01-02 19:01:01	2020-01-02 19:40:01	89
9	1008	9004	2020-02-02 12:01:01	2020-02-02 12:20:01	99
10	1008	9001	2020-02-02 12:01:01	2020-02-02 12:31:01	98
11	1009	9002	2020-02-02 12:01:01	2020-01-02 12:43:01	81
12	1010	9001	2020-01-02 12:11:01	(NULL)	(NULL)
13	1010	9001	2020-02-02 12:01:01	2020-01-02 10:31:01	89

试卷的类别 tag 可能出现大小写混乱的情况，请先筛选出试卷作答数小于 3 的类别 tag，统计将其转换为大写后对应的原本试卷作答数。

如果转换后 tag 并没有发生变化，不输出该条结果。

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

tag	answer_cnt
C++	6

解释：被作答过的试卷有 9001、9002、9003、9004，他们的 tag 和被作答次数如下：

exam_id	tag	answer_cnt
9001	算法	4
9002	C++	6
9003	c++	2
9004	sql	2

作答次数小于 3 的 tag 有 c++和 sql，而转为大写后只有 C++本来就有作答数，于是输出 c++转化大写后的作答次数为 6。

思路 ：

首先，这题有点混乱，9004 根据示例数据查出来只有 1 次，这里显示有 2 次。

先看一下大小写转换函数：

1. UPPER(s) 或 UCASE(s) 函数可以将字符串 s 中的字母字符全部转换成大写字母；

2. LOWER(s) 或者 LCASE(s) 函数可以将字符串 s 中的字母字符全部转换成小写字母。

难点在于相同表做连接要查询不同的值

答案 ：

WITH a AS
  (SELECT tag,
          COUNT(start_time) AS answer_cnt
   FROM exam_record er
   JOIN examination_info ei ON er.exam_id = ei.exam_id
   GROUP BY tag)
SELECT a.tag,
       b.answer_cnt
FROM a
INNER JOIN a AS b ON UPPER(a.tag)= b.tag #a 小写 b 大写
AND a.tag != b.tag
WHERE a.answer_cnt < 3;