mysql牛客刷题（SQL进阶挑战）

手册

阿里sql手册

插入记录

SQL1 插入记录（一）

这里的id是自增主键（PRIMARY KEY)，这就意味着不需要你自己手动填入，它会跟随表格行数进行自己增加（比如这样增加id值👉1，2，3…n）。
所以我们在插入数据的时候，方法之一： 可以指定插入的列名， 这样就不用填写id这一列的数据，让他自增。具体插入代码如下：

insert into exam_record(uid,exam_id,start_time,submit_time,score)
values(1001,9001,'2021-09-01 22:11:12','2021-09-01 23:01:12',90),
(1002,9002,'2021-09-04 07:01:02',null,null)

方法之二：把id的值设置为NULL或0，这样MySQL会自己处理这个自增的id列。 具体代码如下

INSERT INTO exam_record VALUES
(NULL, 1001, 9001, '2021-09-01 22:11:12', '2021-09-01 23:01:12', 90),
(NULL, 1002, 9002, '2021-09-04 07:01:02', NULL, NULL);

引申一个时间的知识点
以下正确的表达

interval 时间间隔关键字，常和date_add() 或 date_sub()搭配使用。

A.T_DATE = B.T_DATE+ interval '1' hour	1小时以后

A.T_DATE = B.T_DATE+ interval 1 hour	1小时以后

A.T_DATE = B.T_DATE+ interval +1 hour	1小时以后

A.T_DATE = B.T_DATE+ interval -1 hour	1小时之前

A.T_DATE = B.T_DATE+ interval '-1' hour	1小时之前

SQL2 插入记录（二）

插入记录的方式汇总：

普通插入（全字段）：INSERT INTO table_name VALUES (value1, value2, ...)
普通插入（限定字段）：INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...)
多条一次性插入：INSERT INTO table_name (column1, column2, ...) VALUES (value1_1, value1_2, ...), (value2_1, value2_2, ...), ...
从另一个表导入：INSERT INTO table_name SELECT * FROM table_name2 [WHERE key=value]

细节剖析：
新表exam_record_before_2021已创建好；
第一列为自增主键列，不能直接复制过去；
只复制2021年之前的记录；
只复制已完成了的试题作答纪录；
本题可采用第四种插入方式，需根据细节剖析的点做稍微改动，改为限定字段插入，即只插入除自增id列以外的列:

INSERT INTO exam_record_before_2021(uid, exam_id, start_time, submit_time, score)
SELECT uid, exam_id, start_time, submit_time, score
FROM exam_record
WHERE YEAR(submit_time) < '2021';

SQL3 插入记录（三）

明确考点：

插入记录的方式汇总：

普通插入（全字段）：INSERT INTO table_name VALUES (value1, value2, ...)
普通插入（限定字段）：INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...)
多条一次性插入：INSERT INTO table_name (column1, column2, ...) VALUES (value1_1, value1_2, ...), (value2_1, value2_2, ...), ...
从另一个表导入：INSERT INTO table_name SELECT * FROM table_name2 [WHERE key=value]
带更新的插入：REPLACE INTO table_name VALUES (value1, value2, ...) （注意这种原理是检测到主键或唯一性索引键重复就删除原记录后重新插入）

细节剖析：

高难度SQL试卷；
时长为一个半小时，等于90分钟；
2021-01-01 00:00:00 作为发布时间；
不管该ID试卷是否存在，都要插入成功；

本题可采用第五种插入方式，试卷ID列有唯一性索引，自增主键可直接设置为NULL或0或DEFAULT:

replace into 跟 insert into功能类似，不同点在于：replace into 首先尝试插入数据到表中，

如果发现表中已经有此行数据（根据主键或者唯一索引判断）则先删除此行数据，然后插入新的数据；
否则，直接插入新数据。

要注意的是：插入数据的表必须有主键或者是唯一索引！否则的话，replace into 会直接插入数据，这将导致表中出现重复的数据。

REPLACE INTO examination_info VALUES
 (NULL, 9003, "SQL", "hard", 90, "2021-01-01 00:00:00");

当然也可以限定字段插入，写作：

REPLACE INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES
 (9003, "SQL", "hard", 90, "2021-01-01 00:00:00");

最后还有一种曲折方式，就是先删除可能存在的9003号试卷，再用insert into插入：

DELETE FROM examination_info WHERE exam_id=9003;
INSERT INTO examination_info (exam_id, tag, difficulty, duration, release_time) VALUES
 (9003, "SQL", "hard", 90, "2021-01-01 00:00:00")

更新记录

SQL4 更新记录（一）

明确考点：

修改记录的方式汇总：

设置为新值：UPDATE table_name SET column_name=new_value [, column_name2=new_value2] [WHERE column_name3=value3]
根据已有值替换：UPDATE table_name SET key1=replace(key1, '查找内容', '替换成内容') [WHERE column_name3=value3]

细节剖析：

tag为PYTHON的tag字段全部修改为Python

思路实现：

本题采用两种修改方式均可，语义为『当tag为PYTHON时，修改tag为Python』，先用第一种:

UPDATE examination_info
SET tag = "Python"
WHERE tag = "PYTHON";

如果采用第二种，写作：

UPDATE examination_info
SET tag = REPLACE(tag, "PYTHON", "Python")
WHERE tag = "PYTHON";

思维扩展：第二种方式不仅可用于整体替换，还能做子串替换，例如要实现将tag中所有的PYTHON替换为Python（如CPYTHON=>CPython），可写作：

UPDATE examination_info
SET tag = REPLACE(tag, "PYTHON", "Python")
WHERE tag LIKE "%PYTHON%";

SQL5 更新记录（二）

请把exam_record表中2021年9月1日之前开始作答的未完成记录全部改为被动完成，
即：将完成时间改为'2099-01-01 00:00:00'，分数改为0。

这里需要解决的是读懂题意 这里的最关键的是如何判断出来什么才是未完成的,我个人认为不是分数为0的,而是未提交的才是未完成的,所以接下来就是比较简单的,只需要找到表中,2021-9-1之前答题的,然后submit_time 是null的数据,找到后进行更新数据即可

UPDATE exam_record
SET submit_time = '2099-01-01 00:00:00',score=0
where start_time<'2021-09-01 00:00:00' AND score is NULL

删除记录

SQL6 删除记录（一）

请删除exam_record表中作答时间小于5分钟整且分数不及格（及格线为60分）的记录；

明确考点：

删除记录的方式汇总：

根据条件删除：DELETE FROM tb_name [WHERE options] [ [ ORDER BY fields ] LIMIT n ]
全部删除（表清空，包含自增计数器重置）：TRUNCATE tb_name

时间差：

TIMESTAMPDIFF(interval, time_start, time_end)可计算time_start-time_end的时间差，单位以指定的interval为准，常用可选：
    SECOND 秒
    MINUTE 分钟（返回秒数差除以60的整数部分）
    HOUR 小时（返回秒数差除以3600的整数部分）
    DAY 天数（返回秒数差除以3600*24的整数部分）
    MONTH 月数
    YEAR 年数

细节剖析：

作答时间小于5分钟整的记录；
分数不及格（及格线为60分）的记录；

思路实现：

本题采用第一种删除方式，满足条件1和条件2就删除:

DELETE FROM exam_record
WHERE TIMESTAMPDIFF(MINUTE, start_time, submit_time) < 5
    AND score < 60;

SQL7 删除记录（二）

请删除exam_record表中未完成作答或作答时间小于5分钟整的记录中，开始作答时间最早的3条记录。

明确考点：

删除记录的方式汇总：

根据条件删除：DELETE FROM tb_name [WHERE options] [ [ ORDER BY fields ] LIMIT n ]
全部删除（表清空，包含自增计数器重置）：TRUNCATE tb_name

时间差：

TIMESTAMPDIFF(interval, time_start, time_end)可计算time_start-time_end的时间差，单位以指定的interval为准，常用可选：
    SECOND 秒
    MINUTE 分钟（返回秒数差除以60的整数部分）
    HOUR 小时（返回秒数差除以3600的整数部分）
    DAY 天数（返回秒数差除以3600*24的整数部分）
    MONTH 月数
    YEAR 年数

细节剖析：

未完成作答的记录；
或作答时间小于5分钟整的记录；
开始作答时间最早的3条记录；

思路实现：

本题采用第一种删除方式，满足条件1或条件2就删除，但只删除3条记录:

DELETE FROM exam_record
WHERE submit_time IS NULL
    OR TIMESTAMPDIFF(MINUTE, start_time, submit_time) < 5
ORDER BY start_time
LIMIT 3;

SQL8 删除记录（三）

请删除exam_record表中所有记录，并重置自增主键。 

truncate table exam_record;

truncate table 在功能上，与不带where字句的delete语句相同；二者均删除表中的全部行，但truncate table 比delete速度更快，且使用的系统和事务日志资源少。 truncate 删除表中的所有行，但表的结构及其列，约束，索引等保持不变。新行标识所用的技术值重置为该列的种子。如果想保留标识计数值，轻盖拥delete 。如果要删除表定义及其数据，请使用drop table 语句。

表的创建、修改与删除

SQL9 创建一张新表

以下语句用法

primary key--->主键
unique key--->唯一约束
not null--->非空
comment--->评论
default--->默认值
default current_timestamp--->默认为当前时间
default charset=utf8--->默认编码

create table user_info_vip(
    id int(11) not null primary key auto_increment comment '自增ID',
    uid int(11) not null unique key comment '用户ID',
    nick_name varchar(64) comment '昵称',
    achievement	int(11) default 0 comment '成就值',
    level int(11) comment '用户等级',
    job varchar(32) comment '职业方向',
    register_time	datetime default CURRENT_TIMESTAMP comment '注册时间'
)default charset=utf8

SQL10 修改表

现有一张用户信息表user_info，其中包含多年来在平台注册过的用户信息。
请在用户信息表，字段level的后面增加一列最多可保存15个汉字的字段school；
并将表中job列名改为profession，同时varchar字段长度变为10；
achievement的默认值设置为0。

alter table user_info add school varchar(15) after level;
增加列在某列之后
alter table 增加的表格 add 增加列的名称 数据类型 位置(after level 在level 之后)

alter table user_info change job profession varchar(10);
更换列的名称及数据类型
alter table user_info change 原列名 修改列名 修改数据类型

alter table user_info modify achievement int(11) default 0;
更改数据类型
alter table 表名 modify 修改列名称 数据类型 默认值等

SQL11 删除表

把2011到2014年的备份表都删掉；
如果存在才删除；

明确考点：

表的创建、修改与删除：

1.1 直接创建表：

CREATE TABLE
[IF NOT EXISTS] tb_name -- 不存在才创建，存在就跳过
(column_name1 data_type1 -- 列名和类型必选
  [ PRIMARY KEY -- 可选的约束，主键
   | FOREIGN KEY -- 外键，引用其他表的键值
   | AUTO_INCREMENT -- 自增ID
   | COMMENT comment -- 列注释（评论）
   | DEFAULT default_value -- 默认值
   | UNIQUE -- 唯一性约束，不允许两条记录该列值相同
   | NOT NULL -- 该列非空
  ], ...
) [CHARACTER SET charset] -- 字符集编码
[COLLATE collate_value] -- 列排序和比较时的规则（是否区分大小写等）

1.2 从另一张表复制表结构创建表：

 CREATE TABLE tb_name LIKE tb_name_old

1.3 从另一张表的查询结果创建表：

 CREATE TABLE tb_name AS SELECT * FROM tb_name_old WHERE options

2.1 修改表：ALTER TABLE 表名 修改选项 。选项集合：

{ ADD COLUMN <列名> <类型>  -- 增加列
 | CHANGE COLUMN <旧列名> <新列名> <新列类型> -- 修改列名或类型
 | ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT } -- 修改/删除 列的默认值
 | MODIFY COLUMN <列名> <类型> -- 修改列类型
 | DROP COLUMN <列名> -- 删除列
 | RENAME TO <新表名> -- 修改表名
 | CHARACTER SET <字符集名> -- 修改字符集
 | COLLATE <校对规则名> } -- 修改校对规则（比较和排序时用到）

3.1 删除表：DROP TABLE [IF EXISTS] 表名1 [, 表名2]。

DROP TABLE IF EXISTS exam_record_2011, exam_record_2012, exam_record_2013, exam_record_2014;

索引的创建、删除

SQL12 创建索引

现有一张试卷信息表examination_info，其中包含各种类型试卷的信息。为了对表更方便快捷地查询，需要在examination_info表创建以下索引，规则如下：
在duration列创建普通索引idx_duration、在exam_id列创建唯一性索引uniq_idx_exam_id、在tag列创建全文索引full_idx_tag。

明确考点：
索引创建、删除与使用：

1.1 create方式创建索引：

CREATE
  [UNIQUE -- 唯一索引
  | FULLTEXT -- 全文索引
  ] INDEX index_name ON table_name -- 不指定唯一或全文时默认普通索引
  (column1[(length) [DESC|ASC]] [,column2,...]) -- 可以对多列建立组合索引 

首先创建一个表：create table t1 (id int primary key,username varchar(20),password varchar(20));
创建单个索引的语法：create index 索引名 on 表名（字段名）

索引名一般是：表名_字段名

给id创建索引：create index t1_id on t1(id);

创建联合索引的语法：create index 索引名 on 表名（字段名1，字段名2）

给username和password创建联合索引：create index t1_username_password on t1(username,password)

1.2 alter方式创建索引：

ALTER TABLE tb_name ADD [UNIQUE | FULLTEXT] [INDEX] index_content(content)

2.1 drop方式删除索引：

DROP INDEX <索引名> ON <表名>

2.2 alter方式删除索引：

ALTER TABLE <表名> DROP INDEX <索引名>

3.1 索引的使用：

索引使用时满足最左前缀匹配原则，即对于组合索引(col1, col2)，在不考虑引擎优化时，条件必须是col1在前col2在后，或者只使用col1，索引才会生效；
索引不包含有NULL值的列
一个查询只使用一次索引，where中如果使用了索引，order by就不会使用
like做字段比较时只有前缀确定时才会使用索引
在列上进行运算后不会使用索引，如year(start_time)<2020不会使用start_time上的索引
细节剖析：
在duration列创建普通索引idx_duration；
在exam_id列创建唯一性索引uniq_idx_exam_id；
在tag列创建全文索引full_idx_tag；

-- 普通索引
CREATE INDEX idx_duration ON examination_info(duration);

-- 唯一索引
CREATE UNIQUE INDEX uniq_idx_exam_id ON examination_info(exam_id);

-- 全文索引
CREATE FULLTEXT INDEX full_idx_tag ON examination_info(tag);

或者

---alter 创建索引
alter table examination_info add index idx_duration(duration);

alter table examination_info add unique index uniq_idx_exam_id(exam_id);

alter table examination_info add fulltext index full_idx_tag(tag);

SQL13 删除索引

描述
请删除examination_info表上的唯一索引uniq_idx_exam_id和全文索引full_idx_tag。

后台会通过 SHOW INDEX FROM examination_info 来对比输出结果。

方法一：
alter table examination_info drop index uniq_idx_exam_id;
alter table examination_info drop index full_idx_tag;
方法二：
drop index   uniq_idx_exam_id on examination_info;
drop index  full_idx_tag  on examination_info;

聚合函数

SQL14 SQL类别高难度试卷得分的截断平均值

计算所有用户完成SQL类别高难度试卷得分的截断平均值（去掉一个最大值和一个最小值后的平均值）

筛选SQL高难度试卷：where tag=“SQL” and difficulty=“hard”
计算截断平均值：(和-最大值-最小值) / (总个数-2): (sum(score) - max(score) - min(score)) / (count(score) - 2)
联表查询 join on

select tag,difficulty,
round((sum(score)-max(score)-min(score))/(count(score)-2),1) as clip_avg_score
from examination_info ei
join exam_record er
on ei.exam_id = er.exam_id
where tag = "SQL" and difficulty = "hard"

SQL15 统计作答次数

总作答次数：count(start_time) as total_pv；
试卷已完成作答数，count(A)会忽略A的值为null的行：count(submit_time) as complete_pv；
已完成的试卷数，已完成时才计数用if判断，试卷可能被完成多次，需要去重用distinct：
count(distinct exam_id and score is not null) 

0会计入count，null不会计入count！！！

select
count(start_time) as total_pv,
count(submit_time) as complete_pv,
count(distinct exam_id and score is not null) as complete_exam_cnt
from exam_record

SQL16 得分不小于平均分的最低分

题目主要信息：
从试卷作答记录表中找到类别为的SQL试卷得分不小于该类试卷平均得分的用户最低得分
其中试卷信息记录在表examination_info（包括试卷ID、类别、难度、时长、发布时间），答题信息记录在表exam_record（包括试卷ID、用户ID、开始时间、结束时间、得分）

问题拆分：
要找类别为SQL的试卷平均得分：
得分信息在exam_record，试卷类别在表examination_info中，因此要将两个表以exam_id连接。知识点：join…on…
从连接后的表中找到类别为SQL的试卷的分数。知识点：select…from…where…
计算得分的平均值。知识点：avg()

找到类别SQL的试卷得分大于平均得分的最小值：
得分信息在exam_record，试卷类别在表examination_info中，因此要将两个表以exam_id连接。知识点：join…on…
从连接后的表中找到类别为SQL的试卷且分数大于刚刚找到的平均分的分数。知识点：select…from…where…and…
从中选出最小值。知识点：min()

select min(er.score) as min_score_over_avg
from exam_record er join examination_info ei
on er.exam_id = ei.exam_id
where ei.tag = 'SQL' 
and score>=(select avg(e1.score)
           from exam_record e1 join examination_info e2
           on e1.exam_id = e2.exam_id
           where tag = 'SQL')

另一种联表查询的方法
试卷作答记录表关联试卷信息表：join using

on a.c1 = b.c1 等同于 using(c1) ,前提是两个表都有相同字段c1

select min(score) as min_score_over_avg
from exam_record
join examination_info using(exam_id)
where tag = 'SQL'
and score>=(
    select avg(score)
    from exam_record
    join examination_info using(exam_id)
    where tag = 'SQL'
)

分组查询

SQL17 平均活跃天数和月活人数

题目主要信息：
计算2021年每个月里试卷作答区用户平均月活跃天数avg_active_days和月度活跃人数mau
结果保留两位小数
问题拆分：
根据提交时间submit_time不为空筛选活跃的的人。知识点：select…from…where…
筛选每个月的平均活跃天数和总活跃人数：
根据月份来选择时间。知识点：date_format() 通过这个函数匹配’%Y%m’年份和月份；
计算用户平均活跃天数：
根据不同的日期且不同的用户ID统计每个月用户的总活跃天数。知识点：distinct、count()、date_format()
统计每个月用的总人数。知识点：distinct、count()
总天数/总人数得到每个月的用户平均活跃天数；
计算每月总活跃人数，直接统计每月不同的用户id数。知识点：count()、distinct
按照月份分组group by date_format(submit_time, ‘%Y%m’) 知识点：group by …
保留两位小数。 知识点：round(x,2)

202107活跃用户数
    count(distinct uid)
202107活跃天数(按照用户和时间两个约束确定天数)
    count（distinct uid,date_format(submit_time,'%y%m%d'）
202107月活跃天数
    count（distinct uid,date_format(submit_time,'%y%m%d'）/count(distinct uid)
保留两位小数
round（count（distinct uid,date_format(submit_time,'%y%m%d'）/count(distinct uid),2）

select date_format(submit_time, '%Y%m') as month,
       round((count(distinct uid, date_format(submit_time, '%y%m%d'))) / count(distinct uid), 2) as avg_active_days,
       count(distinct uid) as mau
from exam_record
where submit_time is not null
and year(submit_time) = 2021
group by date_format(submit_time, '%Y%m')

其中

count(distinct uid, date_format(submit_time, '%y%m%d')，麻烦问一下，这行代码是什么意思

对uid和日期的不同计数，相当于“与”
当且仅当使用distinct对uid，date_format(submit_time, ‘%y%m%d’)这两个字段筛选出的结果集进行去重后可以使用。

【distinct uid, date_format(submit_time, ‘%y%m%d’)】 把【】内的理解为一个键即可

SQL18 月总刷题数和日均刷题数

题目：月总刷题数month_q_cnt 和日均刷题数avg_day_q_cnt（按月份升序排序），该年的总体情况
约束条件：2021年每月，date_format(submit_time,‘%y%m’)=2021xx
月总刷题数(month_q_cnt)=count(question_id) group by 月份

日均刷题数avg_day_q_cnt=月总刷题记录/月天数，（按月份升序排序）order by date_format(submit_time,‘%y%m’) 保留三位小数，round(,3)

date_format用法

月天数：dayofmonth(last_day())返回每月天数

DAYOFMONTH(d) 函数返回 d 是一个月中的第几天，范围为 1～31。例如 DAYOFMONTH(‘2017-12-15’) 2017-12-15 是这个月的第 15 天。

LAST_DAY()函数是取某个月最后一天的日期。

MAX 函数返回一列中的最大值。NULL 值不包括在计算中。

COUNT (*) 表示的是直接查询符合条件的数据库表的行数。

该年的总体情况

select 
    date_format(submit_time, '%Y%m')as submit_month,
    count(submit_time) as month_q_cnt,
    round(count(submit_time) / max(day(last_day(submit_time))), 3) as avg_day_q_cnt
from practice_record
where  score is not null and year(submit_time) = 2021
group by date_format(submit_time, '%Y%m')
                
union all
                
select 
        "2021汇总" as submit_month,
        count(*) as month_q_cnt,
        round(count(*) / 31, 3) as avg_day_q_cnt  -- /30 会不通过用例
from practice_record where score is not null
                and year(submit_time) = '2021'

order by submit_month;

第4行的max，貌似只是配合新版本的group by来使用的，两个混用也不会影响结果。

SQL19 未完成试卷数大于1的有效用户

条件：2021年每个未完成试卷作答数大于1的有效用户的数据（有效用户指完成试卷作答数至少为1且未完成数小于5）

2021年用where

where YEAR(start_time) = 2021

未完成试卷作答数：无submit_time或者无score。用if函数。

select sum(if(submit_time is null, 1, 0)) as incomplete_cnt
    , sum(if(submit_time is null, 0, 1)) as complete_cnt

有效用户：完成试卷作答数至少为1且未完成数小于5。用having做筛选。

having incomplete_cnt<5
and complete_cnt >= 1

未完成试卷作答数大于1：

having incomplete_cnt > 1

输出结果：

detail列：作答完成的试卷的日期和类型。用concat()连接？看了大神的答案才知道可以有个group_concat()函数。

group_concat([distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator '分隔符'])

通过使用distinct可以排除重复值；如果希望对结果中的值进行排序，可以使用order by子句；separator是一个字符串值，缺省为一个逗号。

group_concat(distinct CONCAT(DATE_FORMAT(start_time, '%Y-%m-%d'),':',tag) separator ';')

整理之后得到：
前置知识
concat和group_concat的区别

1、concat和group_concat都是用在sql语句中做拼接使用的，但是两者使用的方式不尽相同，concat是针对以行数据做的拼接，而group_concat是针对列做的数据拼接，且group_concat自动生成逗号。

2、concat的使用

select concat(id, ",", classId) from user;

3、group_concat的使用：group_concat一般和group by 结合使用比较多

select group_concat(username) from user group by classId;

讲讲concat函数：

使用方法：CONCAT(str1,str2,…) 

返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。

SELECT CONCAT(CAST(int_xxx AS CHAR), char_col)
MySQL的concat函数可以连接一个或者多个字符串,如

SELECT CONCAT('my', 's', 'ql');

-> ‘mysql’

 SELECT CONCAT('my', NULL, 'ql');

-> NULL

 SELECT CONCAT(14.3);

-> ‘14.3’

MySQL中group_concat函数

完整的语法如下：

group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])

基本查询

select * from aa;  

±-----±-----+
| id| name |
±-----±-----+
|1 | 10|
|1 | 20|
|1 | 20|
|2 | 20|
|3 | 200 |
|3 | 500 |
±-----±-----+
6 rows in set (0.00 sec)

以id分组，把name字段的值打印在一行，逗号分隔(默认)

select id,group_concat(name) from aa group by id;  

±-----±-------------------+
| id| group_concat(name) |
±-----±-------------------+
|1 | 10,20,20|
|2 | 20 |
|3 | 200,500|
±-----±-------------------+
3 rows in set (0.00 sec)

以id分组，把name字段的值打印在一行，分号分隔

select id,group_concat(name separator ';') from aa group by id;  

±-----±---------------------------------+
| id| group_concat(name separator ‘;’) |
±-----±---------------------------------+
|1 | 10;20;20 |
|2 | 20|
|3 | 200;500 |
±-----±---------------------------------+
3 rows in set (0.00 sec)

以id分组，把去冗余的name字段的值打印在一行，

逗号分隔

select id,group_concat(distinct name) from aa group by id;  

±-----±----------------------------+
| id| group_concat(distinct name) |
±-----±----------------------------+
|1 | 10,20|
|2 | 20 |
|3 | 200,500 |
±-----±----------------------------+
3 rows in set (0.00 sec)

以id分组，把name字段的值打印在一行，逗号分隔，以name排倒序

select id,group_concat(name order by name desc) from aa group by id;  

±-----±--------------------------------------+
| id| group_concat(name order by name desc) |
±-----±--------------------------------------+
|1 | 20,20,10 |
|2 | 20|
|3 | 500,200|
±-----±--------------------------------------+
3 rows in set (0.00 sec)

select uid
        , sum(if(submit_time is null, 1, 0)) as incomplete_cnt
        , sum(if(submit_time is null, 0, 1)) as complete_cnt
        , group_concat(distinct CONCAT(DATE_FORMAT(start_time, '%Y-%m-%d'),':',tag) separator ';') as detail
from exam_record er join examination_info ei on er.exam_id = ei.exam_id
where YEAR(start_time) = 2021 
group by uid
having incomplete_cnt>1
and incomplete_cnt<5
and complete_cnt >= 1
order by incomplete_cnt desc

嵌套子查询

SQL20 月均完成试卷数不小于3的用户爱作答的类别

明确题意：
统计月均完成试卷数不小于3的用户爱作答的类别及作答次数，按次数降序输出

问题拆解：
筛选完成了的试卷的记录。知识点：where
筛选月均完成数不小于3的用户。知识点：
按用户分组

group by uid；

统计当前用户完成试卷总数

count(exam_id)；

统计该用户有完成试卷的月份数

count(distinct DATE_FORMAT(start_time, "%Y%m"))；

分组后过滤

having count(exam_id) / count(distinct DATE_FORMAT(start_time, "%Y%m")) >= 3；

关联试卷作答记录表和试卷信息表。知识点：join examination_info using(exam_id)
筛选满足条件的用户。知识点：where uid in (…)
统计这些用户作答的类别及计数。知识点：按用户分组group by uid；计数count(tag)；
按次数降序输出。知识点：order by tag_cnt desc

select tag, count(tag) as tag_cnt
from exam_record
join examination_info using(exam_id)
where uid in (
    select uid
    from exam_record
    where submit_time is not null 
    group by uid
    having count(exam_id) / count(distinct DATE_FORMAT(start_time, "%Y%m")) >= 3
)
group by tag
order by tag_cnt desc

SQL21 试卷发布当天作答人数和平均分

明确题意：
计算每张SQL类别试卷发布后，当天5级以上的用户作答的人数uv和平均分avg_score； 按人数降序，相同人数的按平均分升序

问题分解：
获取每张SQL类别试卷发布日期，作为子查询：
筛选试卷类别：WHERE tag = “SQL”
获取试卷ID和发布日期：SELECT exam_id, DATE(release_time)
筛选发布当天的作答记录：WHERE (exam_id, DATE(start_time)) IN (…)
筛选5级以上的用户：AND uid IN (SELECT uid FROM user_info WHERE level > 5)
按试卷ID分组：GROUP BY exam_id
计算作答人数：count( DISTINCT uid ) AS uv
计算平均分（保留1位小数）：ROUND(avg( score ), 1) AS avg_score
细节问题：
表头重命名：as
按人数降序，按平均分升序：ORDER BY uv DESC, avg_score ASC

select exam_id,
count(distinct uid) as uv,
round(avg(score),1) as avg_score
from exam_record
where (exam_id,date(start_time)) in
      (
          select exam_id,
          date(start_time)
          from examination_info
          where tag = "SQL"
      )
      and  uid in(
      select uid
      from user_info
      where level>5)
group by exam_id
order by uv desc,avg_score asc

SQL22 作答试卷得分大于过80的人的用户等级分布

题目主要信息：
统计作答SQL类别的试卷得分大于过80的人的用户等级分布，按数量降序排序，相同数量按照等级降序
用户信息表user_info（uid用户ID，nick_name昵称, achievement成就值, level等级, job职业方向, register_time注册时间）
试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）
试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）
问题拆分：
以每个等级分组，即level作为分组依据，便于计算每个等级的符合条件的人数。知识点：group by
对于每个等级，我们只挑选类别为SQL、且得分大于80的不同的用户进行统计人数，相同的用户做了多次只统计一次：
上述两个要求加上分组的等级分布在三个表中，我们可以将exam_record表根据exam_id与examination_info表连接在一起，然后将exam_record表根据uid与user_info连接在一起，这样三个表就连结在一起了。知识点：join…on…
用where语句判断上述两种情况。知识点：where
按照人数的降序，相同情况下等级降序输出。order by level_cnt desc, level desc 知识点：order by
代码：

select level,
       count(distinct u_i.uid) as level_cnt
from exam_record e_r join examination_info e_i
on e_r.exam_id = e_i.exam_id
join user_info u_i
on e_r.uid = u_i.uid
where tag = 'SQL'
and score > 80
group by level
order by level_cnt desc, level desc

或者

select level,count(distinct e.uid) as level_cnt
from exam_record e
join user_info u 
on e.uid=u.uid
where e.exam_id IN
(
    select exam_id
    from examination_info
    where tag="SQL"
) and e.score>80
group by level 
order by level_cnt desc,level desc

合并查询

SQL23 每个题目和每份试卷被作答的人数和次数

知识点是UNION后的排序问题，ORDER BY子句只能在最后一次使用。 如果想要在UNION之前分别单独排序，那么需要这样：

SELECT * FROM
( SELECT * FROM t1  ORDER BY 字段 ) newt1 ## 一定要对表重新命名，否则报错
UNION
SELECT * FROM
( SELECT * FROM t2  ORDER BY 字段 ) newt2

题目主要信息：
请统计每个题目和每份试卷被作答的人数和次数，分别在试卷区和题目区按uv & pv降序显示
试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分），这是试卷区
题目练习表practice_record（uid用户ID, question_id题目ID, submit_time提交时间, score得分），这是题目区
问题拆分：
先统计试卷区每份试卷被回答的人数和次数：
以试卷exam_id作为分组，便于统计每份试卷被作答的人数和次数。知识点：group by
对于每一组即每一份试卷，统计作答的人数，即uid的数量，要注意去重，即同一人可能回答多次。知识点：count()、distinct
对于每一组即每一份试卷，统计被作答次数，只需要统计出现多少次即可，不用去重。知识点：count()
对查询结果按照先uv再pv的降序排序，order by uv desc, pv desc
再统计题目区每份试卷被回答的人数和次数：
以试卷question_id作为分组，便于统计每个题目被作答的人数和次数。知识点：group by
对于每一组即每个题目，统计作答的人数，即uid的数量，要注意去重，即同一人可能回答多次。知识点：count()、distinct
对于每一组即每个题目，统计被作答次数，只需要统计出现多少次即可，不用去重。知识点：count()
对查询结果按照先uv再pv的降序排序，order by uv desc, pv desc
从试卷区的选择中选出全部与从题目区的选择中选出的全部合并，select * from () exam union select * from () practice。知识点：union

union(或称为联合)的作用是将多个结果合并在一起显示出来。 union和union all的区别是,union会自动压缩多个结果集合中的重复结果，而union all则将所有的结果全部显示出来，不管是不是重复。

MySQL COUNT(*)函数
参考链接
COUNT(*)函数返回由SELECT语句返回的结果集中的行数。COUNT(*)函数计算包含NULL和非NULL值的行，即：所有行。
如果使用COUNT(*)函数对表中的数字行进行计数，而不使用WHERE子句选择其他列，则其执行速度非常快。
这种优化仅适用于MyISAM表，因为MyISAM表的行数存储在information_schema数据库的tables表的table_rows列中; 因此，MySQL可以很快地检索它。
MySQL COUNT(expression)
COUNT(expression)返回不包含NULL值的行数。
MySQL COUNT(DISTINCT expression)
MySQL COUNT(DISTINCT expression)返回不包含NULL值的唯一行数

代码：

select * from
(select exam_id as tid,
       count(distinct uid) as uv,
       count(*) as pv
from exam_record
group by exam_id
order by uv desc, pv desc) exam
union
select * from
(select question_id as tid,
       count(distinct uid) as uv,
       count(*) as pv
from practice_record
group by question_id
order by uv desc, pv desc) practice

SQL24 分别满足两个活动的人

题目主要信息：
分别筛选出2021年每次试卷得分都能到85分的人（activity1）和至少有一次用了一半时间就完成高难度试卷且分数大于80的人（activity2）
按照用户ID排序
问题拆分：

• 筛选2021年每次试卷得分都大于等于85的人和字符串’activity1’：

对于条件1，所有试卷都大于等于85则说明其最小的分数值也大于等于85，限制年份则使用YAER即可，注意我们获取最小分数需要使用MIN，则在获取每个用户对应的最小分数前需要分组，所以我们需要分组后再查询，所以需要使用HAVING而不是WHERE，且需要在GROUP BY之后。

按照uid进行分组划分，统计每个用户的得分情况。知识点：group by
选出提交时间在2021年的试卷。知识点：select…from…where…、year()
对于每组要求判断最小得分不小于85。知识点：having、min()

• 筛选2021年至少有一次用了一半时间就完成高难度试卷且分数大于80的人和字符串’activity2’

接下来查询满足条件2的uid，首先高难度试卷其实就是试卷的难度为hard，而规定时间的一半其实就是试卷信息表中duration的一半，所以需要连接两表
获取时间值的差值可以使用TIMESTAMPDIFF，并指定单位为minute即可，最后再指定分数和日期即可，

试卷信息和考试信息分布在两个表中，须将其通过exam_id连接起来。知识点：join…on…
从连接后的两个表格中满足四个条件的不重复的用户ID，因为只要求至少一次下述情况(知识点：distinct、where…and…)：
提交时间是2021年。year(e_r.submit_time) = 2021
试卷难度是困难。e_i.difficulty = ‘hard’
得分大于80。e_r.score > 80
只用了试卷要求时间一半不到的时间就完成。timestampdiff(minute, e_r.start_time, e_r.submit_time) * 2 < e_i.duration
将两个筛选合并。知识点：union all
按照用户ID排序输出。知识点：order by uid

• 语法

当使用union 时，mysql 会把结果集中重复的记录删掉，而使用union all ，mysql 会把所有的记录返回，且效率高于union 。
timestampdiff用法
代码：

select uid,
       'activity1' as activity
from exam_record
where year(submit_time) = 2021
group by uid
having min(score) >= 85
union all
select distinct uid,
       'activity2' as activity
from exam_record e_r join examination_info e_i
on e_r.exam_id = e_i.exam_id
where year(e_r.submit_time) = 2021
and e_i.difficulty = 'hard'
and e_r.score > 80
and timestampdiff(minute, e_r.start_time, e_r.submit_time) * 2 < e_i.duration
order by uid

连接查询

SQL25 满足条件的用户的试卷完成数和题目练习数

注意要用left join，因为有些uid可能没做某个试卷或练习，也要保留记录

其实看到这道题，我一开始满脑子在想，怎么结合在一起，因为条件基本顺从于exam_record这张表，结果写得非常复杂且不正确

后面我想了下，为啥不能先把这些数据表先做简化呢

于是第一步，我先从统计那步下手，分别将exam_record完成试卷数 和 practice_record题目练习数一一算出来

select 
    uid,
    count(submit_time) as exam_cnt
from exam_record
where YEAR(submit_time) = 2021
group by uid

select
    uid,
    count(submit_time) as question_cnt
from practice_record
where YEAR(submit_time) = 2021
group by uid

第二步，我着重于它的筛选条件“高难度SQL试卷得分平均值大于80并且是7级的红名大佬”
这步其实针对于exam_record,user_info,examination_info这三张表，后两者只是用于连接作用，因此难度并不大
需要注意的是，该SQL只查询Uid是为了后续的衔接，加上其他字段并无太大实际作用

select
    uid
from exam_record
join examination_info using(exam_id)
join user_info using(uid)
where tag = 'SQL' and difficulty = 'hard' and `level` = 7
group by uid
having avg(score) >= 80

第三步，前者条件也筛选出来了，后者试卷数和练习数也写出来了，只剩下组装这项工程了
试卷数和练习数的SQL语句里面留下uid这个字段，目的就是链接三者

select 
uid,
exam_cnt,
if(question_cnt is null,0,question_cnt) question_cnt
from (
select uid,
count(submit_time) as exam_cnt
from exam_record
where year(submit_time)=2021
group by uid) t
left join
(select uid,
count(submit_time) as question_cnt
from practice_record
where year(submit_time)=2021
group by uid) t2 using(uid)
where uid in(
select uid
from exam_record
join examination_info using(exam_id)
join user_info using(uid)
where tag = 'SQL' and difficulty = 'hard' and level =7
group by uid
having avg(score)>=80
)
order by exam_cnt asc,question_cnt desc

SQL26 每个6/7级用户活跃情况

统计每个6/7级用户总活跃月份数、2021年活跃天数、2021年试卷作答活跃天数、2021年答题活跃天数，按照总活跃月份数、2021年活跃天数降序排序
用户信息表user_info（uid用户ID，nick_name昵称, achievement成就值, level等级, job职业方向, register_time注册时间）
试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）
题目练习记录表practice_record（uid用户ID, question_id题目ID, submit_time提交时间, score得分）

知识点
做统计的时候,null是不计算在count以内的。 所以字段的值最好不要设置为null。 比如:select count(user_id) as beyond_num from fs_users_added where credits<410 && user_id!= 75语句,就统计不到null的数据行

每个用户都要统计，因此要对uid分组。知识点：group by

select ui.uid,
count(distinct date_format(start_time,'%Y%m')) act_month_total,
count(distinct if(YEAR(start_time)=2021,date_format(start_time,"%Y%m%d"),null)) act_days_2021,
count(distinct if(year(start_time)=2021 and tag='exam',date(start_time),null)) act_days_2021_exam,
count(distinct if(year(start_time)=2021 and tag ='practice',date(start_time),null)) act_days_2021_question
from user_info ui
left join (
select uid,
submit_time start_time,
'practice' tag
from practice_record
union all 
select uid,
start_time,
'exam' tag
from exam_record) mon
on mon.uid = ui.uid
where ui.level>5
group by uid
order by act_month_total desc,act_days_2021 desc;

专用窗口函数

窗口函数

窗口函数简介
阿里窗口函数介绍
窗口函数配合这个使用

SQL27 每类试卷得分前3名

一、知识点总结

1）窗口函数：有三种排序方式
rank() over() 1 2 2 4 4 6 (计数排名，跳过相同的几个，eg.没有3没有5)
row_number() over() 1 2 3 4 5 6 (赋予唯一排名)
dense_rank() over() 1 2 2 3 3 4 (不跳过排名，可以理解为对类别进行计数)

2）聚合函数：通常查找最大值最小值的时候，首先会想到使用聚合函数。
a.group by的常见搭配：常和以下聚合函数搭配

avg（）-- 求平均值
count（）-- 计数
sum（）-- 求和
max（） -- 最大值
min（）-- 最小值

b.group by 的进阶用法，和with rollup一起使用。

3）左右连接
左连接：表1 left join 表2 on 表1.字段=表2.字段 （以表1为准，表2进行匹配）
右连接：表1 right join 表2 on 表1.字段=表2.字段 （以表2为准，表1进行匹配）
全连接：表1 union all 表2 （表1 和表2的列数必须一样多，union 去除重复项，union all 不剔除重复项）
内连接：表1 inner join 表2（取表1和表2相交部分）
外连接：表1 full outer join 表2 （取表1和表2不相交的部分）
ps：MYSQL 不支持外连接，可以用左右连接后再全连接代替

题目主要信息：
找到每类试卷得分的前3名，如果两人最大分数相同，选择最小分数大者，如果还相同，选择uid大

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

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

问题拆分：
筛选出一个各类标签与用户及排名的表格：

标签信息和得分信息分布在两个表格，需要将其用exam_id连接在一起。知识点：join…on…
排名是以每个标签每个用户为组的，因此要分组。group by tag, e_r.uid 知识点：group by
对每类标签使用分组聚合排名。知识点：row_number() over partition by 排名优先级先是每个用户的最大得分降序，然后是每个用户的最低得分降序，最后用户ID降序。知识点：order by、min()、max()
从上述表格中选出排名小于等于3的标签、用户ID及排名。知识点：select…from…where…

ROW_NUMBER() OVER()用法

row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的)

select tag, uid, ranking
from(
    select tag, e_r.uid,
    row_number() over (partition by tag order by tag, max(score) desc, min(score) desc, e_r.uid desc) as ranking
    from exam_record e_r join examination_info e_i
    on e_r.exam_id = e_i.exam_id
    group by tag, e_r.uid
)ranktable
where ranking <= 3

SQL28 第二快/慢用时之差大于试卷时长一半的试卷

明确题意：
找到第二快和第二慢用时之差大于试卷时长的一半的试卷信息，按试卷ID降序排序

问题分解：
统计每套试卷第二快和第二慢的用时及试卷信息，生成子表 t_exam_time_took:
统计每次完成试卷的用时及试卷信息，生成子表 t_exam_record_timetook:
关联试卷作答表和试卷信息表：exam_record JOIN examination_info USING(exam_id)
筛选完成了的试卷：WHERE submit_time IS NOT NULL
统计作答用时：TimeStampDiff(SECOND, start_time, submit_time) / 60 as time_took
计算第二慢用时，取按试卷分区耗时倒排第二名：
NTH_VALUE(time_took, 2) OVER (PARTITION BY exam_id ORDER BY time_took DESC) as max2_time_took
计算第二快用时，取按试卷分区耗时正排第二名：
NTH_VALUE(time_took, 2) OVER (PARTITION BY exam_id ORDER BY time_took ASC) as max2_time_took
筛选第二快/慢用时之差大于试卷时长一半的试卷：WHERE max2_time_took - min2_time_took > duration / 2
细节问题：
表头重命名：as
按试卷ID降序排序：ORDER BY exam_id DESC
用timestampdiff求出差值，记得用秒才准确，用minute不准确！

NTH_VALUE用法
NTH_VALUE 返回 analytic_clause 定义的窗口中第 n 行的 measure_expr 值。返回的值具有 measure_expr 的数据类型。

NTH_VALUE (measure_expr, n) [ FROM { FIRST | LAST } ]
[ { RESPECT | IGNORE } NULLS ] OVER (analytic_clause)

timestampdiff用法
计算时间差值

MICROSECOND  微秒
SECOND  秒
MINUTE  分钟
HOUR  小时
DAY  天
WEEK  周
MONTH  月份
QUARTER 
YEAR  年份

select exam_id,duration,release_time
from(
select distinct exam_id,duration,release_time,
nth_value(time_took,2) over (partition by exam_id order by time_took desc) as max2_time,
nth_value(time_took,2) over (partition by exam_id order by time_took asc ) as min2_time
    from(
    select exam_id,duration,release_time,
    timestampdiff(second,start_time,submit_time) as time_took
    from exam_record join examination_info using(exam_id)
    where submit_time is not null)  as t_exam_record
    
) as t_exam_time_took
where max2_time -min2_time >duration*60/2
order by exam_id desc;

SQL 29.连续两次作答试卷的最大时间窗

参考题解

SELECT uid, days_window, ROUND(days_window * exam_cnt / diff_days, 2) as avg_exam_cnt
FROM (
    SELECT uid,
        count(start_time) as exam_cnt,  -- 此人作答的总试卷数
        DATEDIFF(max(start_time), min(start_time))+1 as diff_days, -- 最早一次作答和最晚一次作答的相差天数
        max(DATEDIFF(next_start_time, start_time))+1 as days_window -- 两次作答的最大时间窗
    FROM (
        SELECT uid, exam_id, start_time,
            lead(start_time,1) over(
                PARTITION BY uid ORDER BY start_time) as next_start_time -- 将连续的下次作答时间拼上
        FROM exam_record
        WHERE YEAR(start_time)=2021
    ) as t_exam_record_lead
    GROUP BY uid
) as t_exam_record_stat
WHERE diff_days > 1
ORDER BY days_window DESC, avg_exam_cnt DESC;

SQL30 近三个月未完成试卷数为0的用户完成情况

题目主要信息：
找到每个人近三个有试卷作答记录的月份中没有试卷是未完成状态的用户的试卷作答完成数，按试卷完成数和用户ID降序排名
试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）
问题拆分：
先从表exam_record中筛选出用户ID、答题开始时间、得分以及月份的降序排列：
用户ID、开始时间、得分可以直接获取。
月份降序我们用分组连续排名。知识点：dense_rank() over()、date_format() 对每个用户ID内进行排名，因为一个月可能出现多次，所以要采用连续排名，月份大的在前面月份小的在后，符合离现在最近的月份在前。dense_rank() over(partition by uid order by date_format(start_time, ‘%Y%m’) desc) as recent_months
从上述结果中筛选出每个用户近三个的答题数：
对于每个用户进行筛选，因此要以uid分组。知识点：group by
只筛出近三个月的内容，因此上述排名我们只要排名小于等于3的。知识点：where
过滤掉未完成试卷的用户，需要再分组后判断每组用户ID出现次数和得分出现次数是否一致，因为有得分才代表完成了试卷。知识点：having、count()
统计上述没有过滤掉的结果中，每人的得分的总数，代表完成了多少试卷。知识点：count()
根据先答题数后用户ID的降序次序输出。order by exam_complete_cnt desc, uid desc 知识点：order by…desc

select uid,
count(score) as exam_complete_cnt
from(
select uid,start_time,score,
dense_rank() over (partition by uid order by date_format(start_time,'%Y%m')
            desc) as recent_months
    from exam_record
) recent_table
where recent_months <=3
group by uid
having count(score)=count(uid)
order by exam_complete_cnt desc,uid desc

SQL31 未完成率较高的50%用户近三个月答卷情况

参考题解

SELECT t1.uid,
    DATE_FORMAT(start_time,'%Y%m')start_month,  -- 取月份数
    COUNT(start_time) total_cnt, -- 答卷数
    COUNT(submit_time) complete_cnt  -- 完成数
FROM (
    SELECT * ,
    dense_rank()over(partition by uid order by date_format(start_time,'%Y-%m') desc) time_rk -- 对作答时间进行排序
    FROM exam_record
    )t1
    
RIGHT JOIN (
    SELECT * 
    FROM (
        SELECT uid,
        PERCENT_RANK()over( ORDER BY count(submit_time)/count(start_time) ) rate_rk -- 对完成率进行分数排序
        FROM exam_record
        WHERE  exam_id IN 
        (SELECT exam_id FROM examination_info WHERE tag='SQL') -- SQL试卷
        GROUP BY uid
        ) A
    WHERE rate_rk<=0.5  -- 查找排名低于50%的用户
    AND uid IN (SELECT uid FROM user_info WHERE level IN (6,7)) -- 查找6.7级用户
    )t2 ON t1.uid=t2.uid
    
WHERE time_rk<=3 -- 查找作答时间最近的3个月
GROUP BY uid,start_month 
ORDER BY uid,start_month -- 按照用户id和月份进行升序排序
;

SQL32 试卷完成数同比2020年的增长率及排名变化

concat实现字符串拼接

CAST函数用于将某种数据类型的表达式显式转换为另一种数据类型。CAST()函数的参数是一个表达式，它包括用AS关键字分隔的源值和目标数据类型。

语法：CAST (expression AS data_type)

expression：任何有效的SQServer表达式。
AS：用于分隔两个参数，在AS之前的是要处理的数据，在AS之后是要转换的数据类型。
data_type：目标系统所提供的数据类型，包括bigint和sql_variant，不能使用用户定义的数据类型。

unsigned 是mysql自定义的类型（默认），表示无符号数值即非负数。signed为整型默认属性。

区别1：起到约束数值的作用。

区别2：可以增加数值范围。

以tinyint为例，它的取值范围-128-127，加不加signed都默认此范围。加上unsigned表示范围0-255，其实相当于把负数那部分加到正数上。例如身高、体重或者年龄等字段一般不会为负数，此时就可以设置一个 UNSIGNED ，不允许负数插入。

先找出2020年上半年的完成数及排名, 然后找出2021年的完成数及排名, 最后链接两表进行计算即可

select aa.tag,
t1.exam_cnt_20,
t2.exam_cnt_21,
concat(round((t2.exam_cnt_21-t1.exam_cnt_20)/t1.exam_cnt_20*100,1),"%") as growth_rate,
rank1 as exam_cnt_rank_20,
rank2 as exam_cnt_rank_21,
# 注意一般数据库默认都是unsigned, 是不能出现负数的, 可用cast(字段 as signed)即可
cast(rank2 as signed) -cast(rank1 as signed) as rank_delta
from(
    select exam_id,
    count(submit_time) exam_cnt_20,
# 备注: 这里是不能用partition的, 因为是根据分组之后的exam_id进行的
    rank() over(order by count(submit_time) desc) as rank1
    from exam_record
    where year(start_time) =2020
    and month(start_time) <=6
    group by  exam_id
    # 筛选出2020和2021年均有完成记录的tag
    having exam_cnt_20>=1
) t1
join (
select exam_id,
count(submit_time) exam_cnt_21,
rank() over(order by count(submit_time) desc) as rank2
from exam_record
where year(submit_time) =2021
    and month(start_time) <=6
    group by exam_id
    having exam_cnt_21>=1
) t2 on t1.exam_id = t2.exam_id
join examination_info aa on t1.exam_id = aa.exam_id
order by growth_rate desc,t2.exam_cnt_21 

聚合窗口函数

参考题解
阿里窗口函数介绍
窗口函数配合这个使用

主要考察聚类窗口函数，和聚类窗口函数的用法和GROUP BY 函数类似。
MIN()OVER() :不改变表结构的前提下，计算出最小值
MAX()OVER():不改变表结构的前提下，计算出最大值
COUNT()OVER():不改变表结构的前提下，计数
SUM()OVER():不改变表结构的前提下，求和
AVG()OVER():不改变表结构的前提下，求平均值
在我的理解窗口函数的关键词是“不改变表格结构”，查出的数据，单独放一列。

SQL33 对试卷得分做min-max归一化

select uid,exam_id,
round(sum(max_min)/count(max_min),0) avg_new_score
from(
select uid,exam_id,score,
if(max_x = min_x,score,(score-min_x)*100/(max_x-min_x)) max_min
    from(
    select uid,a.exam_id,score,
    min(score) over(partition by exam_id) min_x,
    max(score) over(partition by exam_id) max_x
    from exam_record a 
    left join examination_info b 
    on a.exam_id = b.exam_id
    where difficulty ='hard'
        and score is not null
    )t1
) t2
group by exam_id,uid
order by exam_id,avg_new_score desc

SQL34 每份试卷每月作答数和截止当月的作答总数。

看到有人问：为啥已经GROUP BY 了，在窗口函数中还要用到PARTITION BY？其实是因为少走了一步，我用一个子查询解释下。
解释原因在以下链接

参考题解

SELECT exam_id,DATE_FORMAT(start_time,'%Y%m')start_month, COUNT(start_time) month_cnt,
SUM(COUNT(start_time))OVER(Partition by exam_id ORDER BY DATE_FORMAT(start_time,'%Y%m'))cum_exam_cnt
FROM  exam_record
GROUP BY exam_id,start_month;

SQL35 每月及截止当月的答题情况

参考题解

SELECT start_month ,#每个月     COUNT(DISTINCT uid) mau, #月活用户数   
    SUM(new_day) month_add_uv, #新增用户
    MAX(SUM(new_day))OVER(ORDER BY start_month) max_month_add_uv, #截止当月的单月最大新增用户数
    SUM(SUM(new_day))OVER(ORDER BY start_month) cum_sum_uv
FROM (
    SELECT *,DATE_FORMAT(start_time,'%Y%m') start_month,     IF(start_time=MIN(start_time)OVER(PARTITION BY uid),1,0) new_day
    FROM exam_record)t1
GROUP BY start_month;

空值处理

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

select exam_id,
sum(if(score is null,1,0)) as incomplete_cnt,
round(sum(if(score is null,1,0))/count(start_time),3)  as incomplete_rate
from exam_record
group by exam_id
having incomplete_cnt>=1

这里一直有个疑问，sql执行顺序是先having再select，为啥having还能用select的字段别名incomplete_cnt 大佬们求指教

因为sum（）是聚集函数，而聚集函数相较于having是优先执行的，所以是有了sum()的结果后再执行having的

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

题目主要信息：
输出每个0级用户所有的高难度试卷考试平均用时和平均得分，未完成的默认试卷最大考试时长和0分处理
问题拆分：
筛选出每个0级用户高难度题的得分及耗时：
得分信息、用户信息、题目信息分布三个表格中，我们用exam_id将exam_record和examination_info连在一起，再通过uid连上user_info。知识点：join…on…
从连接后的表格中筛选出用户等级为0试题难度为hard的信息。知识点：where
修改得分为空的分数为0。if(score is not null, score, 0) as new_score 知识点：if
计算用户做这份试卷的用时，没有提交时间就设置为试卷限制时间。if(submit_time is not null, timestampdiff(minute, start_time, submit_time), duration) as cost_time 知识点：if、timestampdiff
筛选出来的信息记为new_table
从new_table中筛选出每个用户的平均得分及平均用时，要以uid分组统计。知识点：group by、round()、avg()

select uid,
round(avg(new_score),0) as avg_score,
round(avg(cost_time),1) as avg_time_took
from(
select er.uid as uid,
if(score is not null,score,0) as new_score,
if(submit_time is not null,timestampdiff(minute,start_time,submit_time),duration) cost_time
from exam_record er join examination_info ei
on er.exam_id = ei.exam_id
join user_info ui using(uid)
where level =0 and difficulty ='hard'
) newtable
group by uid

高级条件语句

SQL38 筛选限定昵称成就值活跃日期的用户

通配符%表示任意多个任意字符_表示一个任意字符

select uid,
nick_name,
achievement
from user_info
where nick_name like '牛客_号'
and achievement between 1200 and 2500
and (uid in (
    select uid 
    from exam_record
    where date_format(submit_time,'%Y%m')='202109')
     or uid in(
     select uid
     from practice_record
     where date_format(submit_time,'%Y%m')='202109')
    )

SQL39 筛选昵称规则和试卷规则的作答记录

SQL里学习通配符时LIKE和RLIKE的区别

mysql的正则表达式用法

mysql的正则表达式

RLIKE后面可以跟正则表达式。

正则表达式"^[0-9]+$"的意思：
1、字符^

意义：表示匹配的字符必须在最前边。

例如：^A不匹配“an A”中的‘A’，但匹配“An A”中最前面的‘A’。

2、字符$

意义：与^类似，匹配最末的字符。

例如：t$不匹配“eater”中的‘t’，但匹配“eat”中的‘t’。

3、字符[0-9]

意义：字符列表，匹配列出中的任一个字符。你可以通过连字符-指出字符范围。

例如：[abc]跟[a-c]一样。它们匹配“brisket”中的‘b’和“ache”中的‘c’。

4、字符+

意义：匹配+号前面的字符1次及以上。等价于{1,}。

例如：a+匹配“candy”中的‘a’和“caaaaaaandy”中的所有‘a’。

select uid,exam_id,round(avg(score),0) as 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

SQL40 根据指定记录是否存在输出不同情况

1）exists 和 not exists
exists：也就是exists（非空才是真）括号里的内容为“非空”则继续执行外层的查询，否则为假则整个查询结果为空。
not exists ：也就是not exists（空才是真）括号里的内容为空则外层的查询为真继续执行，否则为假则整个查询结果为空
详细用法见这里：SQL 子查询 EXISTS 和 NOT EXISTS
1、题目解读
求:请你筛选表中的数据，当有任意一个0级用户未完成试卷数大于2时，输出每个0级用户的试卷未完成数和未完成率（保留3位小数）；若不存在这样的用户，则输出所有有作答记录的用户的这两个指标。结果按未完成率升序排序。

题目中隐藏的坑与难点
我只想吐槽牛客题目的阅读理解太难了，你家题目的阅读理解，就是最大的坑和难点。😒😒😒
“当有任意一个0级用户未完成试卷数大于2时，输出每个0级用户的试卷未完成数和未完成率（保留3位小数）；若不存在这样的用户，则输出所有有作答记录的用户的这两个指标。”说来惭愧，这句话我理解了挺久。这句话的意思其实就是：
当level=0 且 incomplete_cnt>2这种情况出现时是结果A，
如果这种情况不出现时是结果B，结果A B 出现的场景是互斥的也就是指只会出现一个结果
小数点要保留3位数，incomplete_cnt空值要用0表示。
那我只需要把结果A和B都分别列出来，再根据真是条件选择即可。
需求字段

uid 用户ID
incomplete_cnt 未完成试卷数
incomplete_rate 试卷未完成率

2、步骤拆分
看了好几个讨论，笼统来看是两种解题方法。
解法1：先计算出每个用户的试卷未完成数和试卷未完成率，再直接给出两个条件分别对应的结果A和B，因为是互斥条件，直接并联结果A和B即可。

with t1 as(
select b.uid,level,count(start_time) total_cnt, #每个用户答题数
    count(start_time) - count(submit_time) incomplete_cnt, # 每个用户未完成数
    ifnull(round((count(start_time)-count(submit_time))/count(start_time),3),0) incomplete_rate #求每个用户的未完成率，如果为null，则返回0
from exam_record a
    right join user_info b on a.uid = b.uid
group by b.uid
) #命名为t1的子查询

select uid,incomplete_cnt,incomplete_rate
from t1
where exists(select uid from t1
             where level =0 and incomplete_cnt >2 
            ) # 出现level为0且incomplete_cnt >2 大于2的时
    and level = 0 #输出level =0的用户未完成数和未完成率
union all
select uid,incomplete_cnt,incomplete_rate
from t1
where not exists(select uid from t1 
                where level =0 and incomplete_cnt >2 
                )# 没有出现level为0且incomplete_cnt >2 大于2的
    and total_cnt>0 #输出level =0的用户未完成数和未完成率
order by incomplete_rate

SQL41 各用户等级的不同得分表现占比

select level,score_grade,
round(count(uid)/total,3) as ratio
from(
    select ui.uid as uid,
    exam_id,score,level,
    case when score>=90 then '优'
        when score >=75 then '良'
        when score >=60 then '中'
        else '差' end as score_grade,
    count(*) over(partition by level) as total
    from user_info ui join exam_record er
    on ui.uid = er.uid
    where score is not null
)t1
group by level,score_grade
order by level desc,ratio desc

限量查询

SQL42 注册时间最早的三个人

select uid,nick_name,register_time
from user_info
order by register_time limit 3

SQL43 注册当天就完成了试卷的名单第三页

用户信息、试卷信息、考试信息分布在三个表中，我们需要依靠exam_id将exam_record表和examination_info e_i表连接在一起，然后依靠uid再连接上user_info表。知识点：join…on…
对每个人的分数信息计算最大值，要以uid分组。知识点：group by
筛选出每组职位是算法，试卷标签是算法且注册日期等于试卷提交日期的信息。知识点：where、date
求最大分数。知识点：max
根据最大分数排序。知识点：order by
输出第三页，每页3条数据，及从第6条开始（0为起点），限制3条。limit 6, 3

select ui.uid as uid,
level,register_time,
max(score) as max_score
from exam_record er join examination_info ei
on er.exam_id = ei.exam_id
join user_info ui using(uid)
where job ='算法'
and tag ='算法'
and date(register_time) = date(submit_time)
group by uid
order by max_score desc
limit 6,3 # 从0开始计数，共计三次

文本转换函数

SQL44 修复串列了的记录

通过 SUBSTRING_INDEX 对错误输入的 tag 字段进行拆分。
SUBSTRING_INDEX(str, delim, count) 有三个参数，如果count是正数，那么就是从左往右数，第N个分隔符的左边的全部内容。如果是负数，那么就是从右边开始数，第N个分隔符右边的所有内容。
SUBSTRING_INDEX(tag, ‘,’, 1) 可以获得左数第一个内容 tag，SUBSTRING_INDEX(tag, ‘,’, -1) 获得右数第一个 duration
SUBSTRING_INDEX(SUBSTRING_INDEX(tag, ‘,’, 2), ‘,’, -1) 切分两次，获得中间位置的 difficulty

select exam_id,
substring_index(tag,',',1) as tag,
substring_index(substring_index(tag,',',2),',',-1) as difficulty,
substring_index(tag,',',-1) as duration
from examination_info
where tag like '%,%'

SQL45 对过长的昵称截取处理

明确题意：
输出昵称字符数大于10的用户信息，对于字符数大于13的用户昵称输出前10个字符然后加上三个点号：『…』

问题分解：
筛选昵称字符数大于10的用户：WHERE CHAR_LENGTH(nick_name) > 10
对字符数大于13的用户昵称做处理：IF(CHAR_LENGTH(nick_name) > 13,
前10个字符加上三个点号：CONCAT(SUBSTR(nick_name, 1, 10), ‘…’)
细节问题：
表头重命名：as

substr,substring和substring_index区别

1.substr和substring区别

首先申明，substr()是基于Oracle的，substring()是基于SQL Server的，切记不可混用，否则会报错！
MySQL: SUBSTR( ), SUBSTRING( )
Oracle: SUBSTR( )
SQL Server: SUBSTRING( )

2. substr

1、substr(str,pro,len)在字符串str中从pro位置(第一个字节即为1)开始截取长度为len的字符，如substr(Georgi,-2)=gi

3. 字符串截取：substring(str, pos); substring(str, pos, len)

3.1 从字符串的第 4 个字符位置开始取，直到结束。

select substring('example.com', 4);

±-----------------------------+
| substring(‘example.com’, 4) |
±-----------------------------+
| mple.com |
±-----------------------------+
3.2 从字符串的第 4 个字符位置开始取，只取 2 个字符。

select substring('example.com', 4, 2);

±--------------------------------+
| substring(‘example.com’, 4, 2) |
±--------------------------------+
| mp |
±--------------------------------+
3.3 从字符串的第 4 个字符位置（倒数）开始取，直到结束。

select substring('example.com', -4);

±------------------------------+
| substring(‘example.com’, -4) |
±------------------------------+
| .com |
±------------------------------+
3.4 从字符串的第 4 个字符位置（倒数）开始取，只取 2 个字符。

select substring('example.com', -4, 2);

±---------------------------------+
| substring(‘example.com’, -4, 2) |
±---------------------------------+
| .c |
±---------------------------------+
我们注意到在函数 substring(str,pos, len)中， pos 可以是负值，但 len 不能取负值。

4. 字符串截取：substring_index(str,delim,count)

4.1 截取第二个 ‘.’ 之前的所有字符。

select substring_index('www.example.com', '.', 2);

±-----------------------------------------------+
| substring_index(‘www.example.com’, ‘.’, 2) |
±-----------------------------------------------+
| www.example |
±-----------------------------------------------+
4.2 截取第二个 ‘.’ （倒数）之后的所有字符。

select substring_index('www.example.com', '.', -2);

±------------------------------------------------+
| substring_index(‘www.example.com’, ‘.’, -2) |
±------------------------------------------------+
| example.com |
±------------------------------------------------+

select uid,
if(char_length(nick_name)>13,
  concat(substring(nick_name,1,10),'...'),
  nick_name) as nick_name
from user_info
where char_length(nick_name)>10

SQL46 大小写混乱时的筛选统计

with as好处

你没遇到负责的业务场景啊，同一个结果集，进行几次筛选，有这个with as 就可以共用了，减少消耗。

明确题意：

筛选出试卷作答数小于3的类别tag，统计将其转换为大写后对应的原本试卷作答数。

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

统计每类试卷的作答数（区分大小写），生成临时表 t_tag_count:
    左连接试卷作答表和试卷信息表：exam_record LEFT JOIN examination_info USING(exam_id)
    按试卷类别分组：GROUP BY tag
    统计每类试卷作答数：SELECT tag, COUNT(uid) as answer_cnt
对表t_tag_count进行自连接，假设取出的两条记录分别为a和b：t_tag_count as a JOIN t_tag_count as b
选出满足题目条件的结果：
    a.tag转大写后和b.tag一样：ON UPPER(a.tag) = b.tag
    a.tag转换后必须发生变化：a.tag != b.tag
    a的试卷作答数小于3：a.answer_cnt < 3

细节问题：

表头重命名：as

with t_tag_count as(
select tag,count(uid) as answer_cnt
    from exam_record 
    join examination_info using(exam_id)
    group by tag
)

select a.tag,b.answer_cnt
from t_tag_count a 
join t_tag_count b
on  upper(a.tag) = b.tag
and a.tag!=b.tag
and a.answer_cnt<3