某乎问答高质量的回答中用户属于各级别的数量

author_id	author_level	sex
101	6	m
102	1	f
103	1	m
104	3	m
105	4	f
106	2	f
107	2	m
108	5	f
109	6	f
110	5	m

创作者回答情况表answer_tb如下（其中answer_date表示创作日期、author_id指创作者编号、issue_id指问题编号、char_len表示回答字数）：

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

回答字数大于等于100字的认为是高质量回答，请你统计某乎问答高质量的回答中用户属于1-2级、3-4级、5-6级的数量分别是多少，按数量降序排列，以上例子的输出结果如下：

level_cut	num
5-6级	12
3-4级	2
1-2级	1

示例1

输入:

drop table if exists author_tb;
CREATE TABLE author_tb(
author_id int(10) NOT NULL, 
author_level int(10) NOT NULL,
sex char(10) NOT NULL);
INSERT INTO author_tb VALUES(101 , 6, 'm');
INSERT INTO author_tb VALUES(102 , 1, 'f');
INSERT INTO author_tb VALUES(103 , 1, 'm');
INSERT INTO author_tb VALUES(104 , 3, 'm');
INSERT INTO author_tb VALUES(105 , 4, 'f');
INSERT INTO author_tb VALUES(106 , 2, 'f');
INSERT INTO author_tb VALUES(107 , 2, 'm');
INSERT INTO author_tb VALUES(108 , 5, 'f');
INSERT INTO author_tb VALUES(109 , 6, 'f');
INSERT INTO author_tb VALUES(110 , 5, 'm');

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);

输出:

5-6级|12
3-4级|2
1-2级|1

原站题解

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

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

select case 
        when b.author_level=1 or b.author_level=2 then '1-2级' 
        when b.author_level=3 or b.author_level=4 then '3-4级' 
        when b.author_level=5 or b.author_level=6 then '5-6级' 
        end level_cnt,COUNT(char_len) num
from answer_tb a
left join author_tb b
on a.author_id=b.author_id
where a.char_len>=100
group by level_cnt
order by num desc

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

#回答字数大于等于100字的认为是高质量回答，请你统计某乎问答高质量的回答中用户属于1-2级、3-4级、5-6级的数量分别是多少，按数量降序排列
select
    concat(level_cut,'级'),
    sum(cnt)  as num
from
(
    select
    author_level,count(*) cnt,
    case author_level
    when 1 then '1-2'
    when 2 then '1-2'
    when 3 then '3-4'
    when 4 then '3-4'
    when 5 then '5-6'
    when 6 then '5-6'
    end level_cut
    from answer_tb
    left join author_tb a on answer_tb.author_id = a.author_id
    where char_len >=100
    group by author_level
) a
group by level_cut
order by num desc

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

select (case when aut.author_level >=5 then "5-6级"
       when aut.author_level <=2 then "1-2级"
       else "3-4级" end) as level_cut,count(ans.author_id) as num
from answer_tb as ans 
left join author_tb as aut 
on ans.author_id=aut.author_id
where ans.char_len>=100
group by level_cut
order by num desc;

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

SELECT level_cut,
       count(issue_id) num

from(
SELECT case when author_level in (1,2) then '1-2级'
            when author_level in (3,4) then '3-4级'
            when author_level in (5,6) then '5-6级' end as level_cut,
       issue_id

FROM author_tb a
join answer_tb b
on a.author_id=b.author_id

WHERE b.char_len>=100) c
group by level_cut
order by num desc

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

SELECT 
    (CASE WHEN author_level >=5 THEN '5-6级'
          WHEN author_level <=2 THEN '1-2级'
          ELSE '3-4级' END) AS level_cut,
    COUNT(*) AS num


FROM  author_tb au JOIN answer_tb an ON au.author_id = an.author_id
WHERE char_len >= 100
GROUP BY level_cut
ORDER BY num DESC

;

上一题

下一题

详情