某乎问答单日回答问题数大于等于3个的所有用户

answer_date	author_id	issue_id	char_len
2021-11-01	101	E001	150
2021-11-01	101	E002	200
2021-11-01	102	C003	50
2021-11-01	103	P001	35
2021-11-01	104	C003	120
2021-11-01	105	P001	125
2021-11-01	102	P002	105
2021-11-02	101	P001	201
2021-11-02	110	C002	200
2021-11-02	110	C001	225
2021-11-02	110	C002	220
2021-11-03	101	C002	180
2021-11-04	109	E003	130
2021-11-04	109	E001	123
2021-11-05	108	C001	160
2021-11-05	108	C002	120
2021-11-05	110	P001	180
2021-11-05	106	P002	45
2021-11-05	107	E003	56

请你统计11月份单日回答问题数大于等于3个的所有用户信息（author_date表示回答日期、author_id表示创作者id，answer_cnt表示回答问题个数），以上例子的输出结果如下：

answer_date	author_id	answer_cnt
2021-11-02	110	3

注：若有多条数据符合条件，按answer_date、author_id升序排序。

示例1

输入:

drop table if exists answer_tb;
CREATE TABLE answer_tb(
answer_date date NOT NULL, 
author_id int(10) NOT NULL,
issue_id char(10) NOT NULL,
char_len int(10) NOT NULL);
INSERT INTO answer_tb VALUES('2021-11-1', 101, 'E001' ,150);
INSERT INTO answer_tb VALUES('2021-11-1', 101, 'E002', 200);
INSERT INTO answer_tb VALUES('2021-11-1',102, 'C003' ,50);
INSERT INTO answer_tb VALUES('2021-11-1' ,103, 'P001', 35);
INSERT INTO answer_tb VALUES('2021-11-1', 104, 'C003', 120);
INSERT INTO answer_tb VALUES('2021-11-1' ,105, 'P001', 125);
INSERT INTO answer_tb VALUES('2021-11-1' , 102, 'P002', 105);
INSERT INTO answer_tb VALUES('2021-11-2',  101, 'P001' ,201);
INSERT INTO answer_tb VALUES('2021-11-2',  110, 'C002', 200);
INSERT INTO answer_tb VALUES('2021-11-2',  110, 'C001', 225);
INSERT INTO answer_tb VALUES('2021-11-2' , 110, 'C002', 220);
INSERT INTO answer_tb VALUES('2021-11-3', 101, 'C002', 180);
INSERT INTO answer_tb VALUES('2021-11-4' ,109, 'E003', 130);
INSERT INTO answer_tb VALUES('2021-11-4', 109, 'E001',123);
INSERT INTO answer_tb VALUES('2021-11-5', 108, 'C001',160);
INSERT INTO answer_tb VALUES('2021-11-5', 108, 'C002', 120);
INSERT INTO answer_tb VALUES('2021-11-5', 110, 'P001', 180);
INSERT INTO answer_tb VALUES('2021-11-5' , 106, 'P002' , 45);
INSERT INTO answer_tb VALUES('2021-11-5' , 107, 'E003', 56);

输出:

2021-11-02|110|3

原站题解

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

Mysql 解法, 执行用时: 36ms, 内存消耗: 6388KB, 提交时间: 2022-01-25

select b.answer_date,b.author_id,b.answer_cnt from (select a.answer_date,a.author_id,COUNT( a.issue_id) AS answer_cnt FROM answer_tb as a WHERE a.answer_date>='2021-11-01' AND a.answer_date<='2021-11-30'
GROUP BY a.answer_date,a.author_id) AS b WHERE b.answer_cnt>=3
ORDER BY b.answer_date,b.author_id ASC

Mysql 解法, 执行用时: 37ms, 内存消耗: 6340KB, 提交时间: 2021-12-08

select answer_date,  author_id, count(issue_id) answer_cnt 
from answer_tb 
where month(answer_date)=11 
group by answer_date, author_id 
having count(issue_id)>=3 
order by answer_date, author_id

Mysql 解法, 执行用时: 37ms, 内存消耗: 6392KB, 提交时间: 2022-01-22

select * from (
select answer_date, author_id, count(*) num
from answer_tb
where month(answer_date) = 11
group by answer_date, author_id
order by answer_date, author_id)a
where num >= 3

Mysql 解法, 执行用时: 37ms, 内存消耗: 6392KB, 提交时间: 2021-12-31

-- 请你统计11月份单日回答问题数大于等于3个的所有用户信息
-- （author_date表示回答日期、author_id表示创作者id，answer_cnt表示回答问题个数）

-- 表关系：一个author_id 一天根据多个issue_id生成多个char_len
-- 单日每个用户的回答次数 rank()
-- 日期+author+回答问题数

SELECT answer_date,author_id,answer_cnt
FROM (
    SELECT *,
    count(author_id) over (partition by answer_date,author_id) "answer_cnt"
    FROM answer_tb
     ) as a
WHERE answer_cnt >= 3 
GROUP BY answer_date,author_id,answer_cnt

Mysql 解法, 执行用时: 37ms, 内存消耗: 6392KB, 提交时间: 2021-12-16

select answer_date,author_id,count(issue_id) from answer_tb
group by answer_date,author_id
having count(issue_id)>=3
order by answer_date,author_id
;

上一题

下一题

详情