SQL191 某乎问答高质量的回答中用户属于各级别的数量
题目描述
现有某乎问答创作者信息表author_tb如下(其中author_id表示创作者编号、author_level表示创作者级别,共1-6六个级别、sex表示创作者性别):
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 |
SQL Schema
sql
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);
答案
sql
SELECT IF(a2.author_level BETWEEN 1 AND 2, '1-2级',
IF(a2.author_level BETWEEN 3 AND 4, '3-4级', '5-6级')) AS `level_cut`,
COUNT(*) AS `num`
FROM answer_tb a1
INNER JOIN author_tb a2 USING (author_id)
WHERE char_len >= 100
GROUP BY level_cut
ORDER BY num DESC;