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

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的这两个指标，因为试卷作答记录表里只有这三个用户的作答记录。

示例1

输入:

drop table if exists user_info,exam_record;
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', 19, 0, '算法', '2020-01-01 10:00:00'),
  (1002, '牛客2号', 1200, 3, '算法', '2020-01-01 10:00:00'),
  (1003, '牛客3号♂', 22, 0, '算法', '2020-01-01 10:00:00'),
  (1004, '牛客4号', 25, 0, '算法', '2020-01-01 10:00:00'),
  (1005, '牛客555号', 2000, 7, 'C++', '2020-01-01 10:00:00'),
  (1006, '666666', 3000, 6, 'C++', '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, 9002, '2021-09-01 12:01:01', null, null),
(1001, 9003, '2021-09-02 12:01:01', null, null),
(1001, 9004, '2021-09-03 12:01:01', null, null),
(1002, 9001, '2020-01-01 12:01:01', '2020-01-01 12:31:01', 99),
(1002, 9003, '2020-02-01 12:01:01', '2020-02-01 12:31:01', 82),
(1002, 9003, '2020-02-02 12:11:01', null, null),
(1002, 9002, '2021-05-05 18:01:01', null, null),
(1002, 9001, '2021-09-06 12:01:01', null, null),
(1003, 9003, '2021-02-06 12:01:01', null, null),
(1003, 9001, '2021-09-07 10:01:01', '2021-09-07 10:31:01', 89);

输出:

1004|0|0.000
1003|1|0.500
1001|4|0.667

原站题解

上次编辑到这里，代码来自缓存点击恢复默认模板

Mysql 解法, 执行用时: 40ms, 内存消耗: 6524KB, 提交时间: 2022-04-18

select t1.uid, 
        if(incomplete_cnt is null, 0, incomplete_cnt) as incomplete_cnt,
        round(if(incomplete_rate is null, 0, incomplete_rate), 3) as incomplete_rate
from user_info t1
left join
    (select uid,
            sum(if(submit_time is null, 1, 0)) as incomplete_cnt,
            sum(if(submit_time is null, 1, 0)) / count(start_time) as incomplete_rate
    from exam_record
    group by uid
    ) t2
on t1.uid = t2.uid
left join
    (select t1.uid
    from exam_record t1
    join user_info t2
    on t1.uid = t2.uid
    where t2.level=0
    group by uid having sum(if(submit_time is null, 1, 0)) > 2 
    ) t3
on 1=1
where (t3.uid is not null and t1.level = 0)
or (t3.uid is null and t2.uid is not null)
group by t1.uid
order by incomplete_rate asc

Mysql 解法, 执行用时: 41ms, 内存消耗: 6396KB, 提交时间: 2022-05-09

with t_tag_cnt as (
    select b.uid,`level`,ifnull(count(start_time),0) as complete_cnt
    ,ifnull(count(start_time)-count(score),0) as incomplete_cnt
    ,max(count(start_time)-count(score)) over(partition by level) as max_incomplete_cnt
    from exam_record a
    right join user_info b on a.uid = b.uid
    group by b.uid,`level`
)
select uid
,incomplete_cnt,round(ifnull(incomplete_cnt/complete_cnt,0),3) as incomplete_rate
from t_tag_cnt where `level`=0 and max_incomplete_cnt>2
union
select uid
,incomplete_cnt,round(ifnull(incomplete_cnt/complete_cnt,0),3) as incomplete_rate
from t_tag_cnt where not EXists( select uid from t_tag_cnt where `level`=0 and max_incomplete_cnt>2) 
and complete_cnt != 0
order by incomplete_rate;

Mysql 解法, 执行用时: 41ms, 内存消耗: 6420KB, 提交时间: 2022-07-20

with number as (
      select uid
       from user_info join exam_record using (uid)
       where score is null and level = 0
     group by uid having count(1) >2)
   

select uid,count(start_time)-count(score),
round(ifnull((count(start_time)-count(score))/count(start_time),0),3) c
from user_info 
left join exam_record using (uid)
where 
  if( (select count(1) from number) > 0,
  level = 0,level >= 0 and start_time is not null)
group by uid
order by c

Mysql 解法, 执行用时: 41ms, 内存消耗: 6424KB, 提交时间: 2022-06-16

with temp as (
select 
uid,
`level`,
count(start_time)-count(submit_time) incomplete_cnt,
round(
    ifnull(1-count(submit_time)/count(start_time),0),3)incomplete_rate,
count(start_time) total_cnt
from user_info
left join exam_record using(uid)
group by uid
) 
select uid,incomplete_cnt,incomplete_rate
    from temp where exists(select uid from temp where `level`=0 and incomplete_cnt>2)
    and `level`=0
union 
select uid,incomplete_cnt,incomplete_rate
    from temp where not exists(select uid from temp where `level`=0 and incomplete_cnt>2)
    and total_cnt>0
order by incomplete_rate

Mysql 解法, 执行用时: 41ms, 内存消耗: 6432KB, 提交时间: 2022-04-18

WITH t_tag_count as (
    SELECT uid, `level`,
        COUNT(start_time) - COUNT(submit_time) as incomplete_cnt, -- 未完成数
        ROUND(
            IFNULL(1 - COUNT(submit_time) / COUNT(start_time), 0),
            3) as incomplete_rate, -- 此人未完成率
        COUNT(start_time) as total_cnt -- 总作答数
    FROM exam_record
    RIGHT JOIN user_info USING(uid)
    GROUP BY uid
)

SELECT uid, incomplete_cnt, incomplete_rate
FROM t_tag_count
WHERE EXISTS (
    SELECT uid FROM t_tag_count WHERE `level` = 0 AND incomplete_cnt > 2
) AND `level` = 0
UNION ALL
SELECT uid, incomplete_cnt, incomplete_rate
FROM t_tag_count
WHERE NOT EXISTS (
    SELECT uid FROM t_tag_count WHERE `level` = 0 AND incomplete_cnt > 2
) AND total_cnt > 0
ORDER BY incomplete_rate;

上一题

下一题

详情