select * from girl ;
# +--+----+------+
# |id|name|boy_id|
# +--+----+------+
# |1 |A |1 |
# |2 |B |1 |
# |3 |C |3 |
# |4 |C |3 |
# |5 |C |3 |
# |6 |D |NULL |
# |7 |D |NULL |
# |8 |C |2 |
# +--+----+------+
# null 不参与比较的
select * from girl where boy_id !=3 ;
# +--+----+------+
# |id|name|boy_id|
# +--+----+------+
# |1 |A |1 |
# |2 |B |1 |
# |8 |C |2 |
# +--+----+------+
select * from girl where boy_id !=3 or boy_id is null ;
+--+----+------+
|id|name|boy_id|
+--+----+------+
|1 |A |1 |
|2 |B |1 |
|6 |D |NULL |
|7 |D |NULL |
|8 |C |2 |
+--+----+------+
# 对 null 常用的处理方法
# 方法1：coalesce(A,B),如果A为空则返回B,不为空则返回A
# 方法2：IF(A IS NULL ,B,C)如果A为空则返回B，不为空则返回C
# 方法3： case when then else end
#
# 考察点:
# count(*) 是统计 总计（可能是 count（主键））, count(score) 不统计 score 为 null 的数据 count(score)/ count(*) 就可以得出 需要的值
# 原因： count(score) 的时候 加了个过滤条件 ： select count（主键） from A where score is not null ;
# count(*) 是统计 总计（可能是 count（主键）） ， count(score) 不统计 score 为 null 的数据
# format(小数,小数点位数) 来精确小数点位数 ，等同于 round(小数，小数点位数)
#
select score from exam_record;
# +-----+
# |score|
# +-----+
# |80 |
# |NULL |
# |87 |
# |20 |
# |89 |
# |NULL |
# |90 |
# +-----+
select
count(*)
FROM
exam_record ;
# +--------+
# |count(*)|
# +--------+
# |7 |
# +--------+
select
count(score)
FROM
exam_record ;
# +------------+
# |count(score)|
# +------------+
# |5 |
# +------------+
# avg() 总数会不会算入 null 的 ,不会算入 的 ： avg(score) = sum (score)/count(score) 而不是： sum (score)/count(*)
select
avg(score)
FROM
exam_record ;
# +----------+
# |avg(score)|
# +----------+
# |73.2000 |
# +----------+
select
sum(score)/count(*)
FROM
exam_record ;
# +-------------------+
# |sum(score)/count(*)|
# +-------------------+
# |52.2857 |
# +-------------------+
select
sum(score)/count(score)
FROM
exam_record ;
# +-----------------------+
# |sum(score)/count(score)|
# +-----------------------+
# |73.2000 |
# +-----------------------+
drop table if exists exam_record;
CREATE TABLE exam_record (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid int NOT NULL COMMENT '用户ID',
exam_id int NOT NULL COMMENT '试卷ID',
start_time datetime NOT NULL COMMENT '开始时间',
submit_time datetime COMMENT '提交时间',
score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2020-01-02 09:01:01', '2020-01-02 09:21:01', 80),
(1001, 9001, '2021-05-02 10:01:01', '2021-05-02 10:30:01', 81),
(1001, 9001, '2021-09-02 12:01:01', null, null);
select * from exam_record;
# +--+----+-------+-------------------+-------------------+-----+
# |id|uid |exam_id|start_time |submit_time |score|
# +--+----+-------+-------------------+-------------------+-----+
# |1 |1001|9001 |2020-01-02 09:01:01|2020-01-02 09:21:01|80 |
# |2 |1001|9001 |2021-05-02 10:01:01|2021-05-02 10:30:01|81 |
# |3 |1001|9001 |2021-09-02 12:01:01|NULL |NULL |
# +--+----+-------+-------------------+-------------------+-----+
# 请统计有未完成状态的试卷的未完成数incomplete_cnt和未完成率incomplete_rate。由示例数据结果输出如下：
# exam_id incomplete_cnt complete_rate
# 9001 1 0.333
#
# 思路① 筛选出 为 null 的数据 , 然后分组 ，求完成率 (SELECT FORMAT(100.7654,3) 函数将 100.7654 保留为 三位数 )
select exam_id,
count(*) incomplete_cnt,
FORMAT(count(*) / (select count(1) from exam_record er0 where er0.exam_id = er.exam_id), 3) complete_rate
from exam_record er
where start_time is not null
and submit_time is null
group by exam_id order by exam_id;
# 思路② sum 求和 ，但是 如果 不是对表里某个字段处理，也可以用来当 count 处理
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 ;
# +-------+--------------+---------------+
# |exam_id|incomplete_cnt|incomplete_rate|
# +-------+--------------+---------------+
# |9001 |1 |0.333 |
# +-------+--------------+---------------+
# 思路③ 总数-已完成的=未完成
# count(*)-count(score)=未完成
select
exam_id,
count(*) - count(score) incomplete_cnt,
round((count(*) - count(score)) / COUNT(*),3) incomplete_rate
FROM
exam_record
WHERE
exam_id IN(
select
exam_id
from
exam_record
where
score is null
)
GROUP BY
exam_id ;
# count(*) 是统计 总计（可能是 count（主键）） ， count(score) 不统计 score 为 null 的数据
select
count(*)
FROM
exam_record ;
# +--------+
# |count(*)|
# +--------+
# |3 |
# +--------+
select
count(score)
FROM
exam_record ;
# +------------+
# |count(score)|
# +------------+
# |2 |
# +------------+
# 满级人类
drop table if exists examination_info,user_info,exam_record;
CREATE TABLE examination_info (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
exam_id int UNIQUE NOT NULL COMMENT '试卷ID',
tag varchar(32) COMMENT '类别标签',
difficulty varchar(8) COMMENT '难度',
duration int NOT NULL COMMENT '时长',
release_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE TABLE user_info (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid int UNIQUE NOT NULL COMMENT '用户ID',
`nick_name` varchar(64) COMMENT '昵称',
achievement int COMMENT '成就值',
level int COMMENT '用户等级',
job varchar(32) COMMENT '职业方向',
register_time datetime COMMENT '注册时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE TABLE exam_record (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid int NOT NULL COMMENT '用户ID',
exam_id int NOT NULL COMMENT '试卷ID',
start_time datetime NOT NULL COMMENT '开始时间',
submit_time datetime COMMENT '提交时间',
score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
INSERT INTO user_info(uid,`nick_name`,achievement,level,job,register_time) VALUES
(1001, '牛客1号', 10, 0, '算法', '2020-01-01 10:00:00'),
(1002, '牛客2号', 2100, 6, '算法', '2020-01-01 10:00:00');
INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES
(9001, 'SQL', 'hard', 60, '2020-01-01 10:00:00'),
(9002, 'SQL', 'easy', 60, '2020-01-01 10:00:00'),
(9004, '算法', 'medium', 80, '2020-01-01 10:00:00');
INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2020-01-02 09:01:01', '2020-01-02 09:21:59', 80),
(1001, 9001, '2021-05-02 10:01:01', null, null),
(1001, 9002, '2021-02-02 19:01:01', '2021-02-02 19:30:01', 87),
(1001, 9001, '2021-06-02 19:01:01', '2021-06-02 19:32:00', 20),
(1001, 9002, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 89),
(1001, 9002, '2021-09-01 12:01:01', null, null),
(1002, 9002, '2021-05-05 18:01:01', '2021-05-05 18:59:02', 90);
select * from examination_info ;
# +--+-------+---+----------+--------+-------------------+
# |id|exam_id|tag|difficulty|duration|release_time |
# +--+-------+---+----------+--------+-------------------+
# |1 |9001 |SQL|hard |60 |2020-01-01 10:00:00|
# |2 |9002 |SQL|easy |60 |2020-01-01 10:00:00|
# |3 |9004 |算法 |medium |80 |2020-01-01 10:00:00|
# +--+-------+---+----------+--------+-------------------+
select * from user_info ;
# +--+----+---------+-----------+-----+---+-------------------+
# |id|uid |nick_name|achievement|level|job|register_time |
# +--+----+---------+-----------+-----+---+-------------------+
# |1 |1001|牛客1号 |10 |0 |算法 |2020-01-01 10:00:00|
# |2 |1002|牛客2号 |2100 |6 |算法 |2020-01-01 10:00:00|
# +--+----+---------+-----------+-----+---+-------------------+
select * from exam_record ;
# +--+----+-------+-------------------+-------------------+-----+
# |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|9001 |2021-06-02 19:01:01|2021-06-02 19:32:00|20 |
# |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 |
# |7 |1002|9002 |2021-05-05 18:01:01|2021-05-05 18:59:02|90 |
# +--+----+-------+-------------------+-------------------+-----+
# 需求：出每个0级用户所有的高难度试卷考试平均用时和平均得分，未完成的默认试卷最大考试时长和0分处理
# 思路① 先筛选出 0级别 用户的数据 ；然后 group by 这些 零级用户 ；最后平均用时和平均得分(null 的话 用 要求的数据填充)
#
# select TIMESTAMPDIFF(MINUTE,'2008-08-01 22:00:00','2008-08-01 23:01:00') ;
# TIMESTAMPDIFF(MINUTE,start_time,submit_time)
# sum(TIMESTAMPDIFF(MINUTE, start_time,
# if(submit_time is null, select duration from examination_info where examination_info.,
# submit_time))) avg_score,
# sum(score) avg_time_took
select er.uid,
start_time ,
submit_time,
( select duration from examination_info where examination_info.exam_id = ei.exam_id ) duration ,
if (score is null ,0,score)
from user_info ui
inner join exam_record er on ui.uid = er.uid and ui.level = 0
inner join examination_info ei on er.exam_id = ei.exam_id and ei.difficulty = 'hard'
group by er.uid;
# 填充 null
select uid, start_time,if(submit_time is null, duration, submit_time) , score
from (select er.uid,
start_time,
submit_time,
(select duration from examination_info where examination_info.exam_id = ei.exam_id) duration,
if(score is null, 0, score) score
from user_info ui
inner join exam_record er on ui.uid = er.uid and ui.level = 0
inner join examination_info ei on er.exam_id = ei.exam_id and ei.difficulty = 'hard') tem ;
# +----+-------------------+----------------------------------------------+-----+
# |uid |start_time |if(submit_time is null, duration, submit_time)|score|
# +----+-------------------+----------------------------------------------+-----+
# |1001|2020-01-02 09:01:01|2020-01-02 09:21:59 |80 |
# |1001|2021-05-02 10:01:01|60 |0 |
# |1001|2021-06-02 19:01:01|2021-06-02 19:32:00 |20 |
# +----+-------------------+----------------------------------------------+-----+
select uid, start_time, if(submit_time is null,duration,TIMESTAMPDIFF(MINUTE,start_time,submit_time)) , score
from (select er.uid,
start_time,
submit_time,
(select duration from examination_info where examination_info.exam_id = ei.exam_id) duration,
if(score is null, 0, score) score
from user_info ui
inner join exam_record er on ui.uid = er.uid and ui.level = 0
inner join examination_info ei on er.exam_id = ei.exam_id and ei.difficulty = 'hard') tem ;
# +----+-------------------+-----------------------------------------------------------------------------+-----+
# |uid |start_time |if(submit_time is null,duration,TIMESTAMPDIFF(MINUTE,start_time,submit_time))|score|
# +----+-------------------+-----------------------------------------------------------------------------+-----+
# |1001|2020-01-02 09:01:01|20 |80 |
# |1001|2021-05-02 10:01:01|60 |0 |
# |1001|2021-06-02 19:01:01|30 |20 |
# +----+-------------------+-----------------------------------------------------------------------------+-----+
# 分组 求平均值 avg_score avg_time_took
select tem1.uid ,format(avg(score),0) ,format(avg(duration),1) from (select uid, start_time, if(submit_time is null,duration,TIMESTAMPDIFF(MINUTE,start_time,submit_time)) duration , score
from (select er.uid,
start_time,
submit_time,
(select duration from examination_info where examination_info.exam_id = ei.exam_id) duration,
if(score is null, 0, score) score
from user_info ui
inner join exam_record er on ui.uid = er.uid and ui.level = 0
inner join examination_info ei on er.exam_id = ei.exam_id and ei.difficulty = 'hard') tem) tem1
group by tem1.uid ;
# 其他思路： coalesce(A,B),如果A不为空返回A，否则返回B （英[?k????les] 美[?ko???les]）
SELECT uid,
ROUND(AVG(score_new), 0) avg_score,
ROUND(AVG(cost_time), 1) avg_time_took
FROM (SELECT a.uid,
start_time,
COALESCE(score, 0) score_new, #方法1：coalesce(A,B),如果A为空则返回B,不为空则返回A
IF(submit_time IS NULL, b.duration,
TIMESTAMPDIFF(minute, start_time, submit_time)) cost_time #IF(A IS NULL ,B,C)如果A为空则返回B，不为空则返回C
FROM exam_record a
LEFT JOIN examination_info b ON a.exam_id = b.exam_id
LEFT JOIN user_info c ON a.uid = c.uid
WHERE difficulty = 'hard'
AND level = '0') t1
GROUP BY uid;
|
