第二十六例:条件函数,计算25岁以上和以下的用户数量
- 题目地址:计算25岁以上和以下的用户数量牛客题霸牛客网 (nowcoder.com)、
- 初始化数据:
droptable if exists `user_profile`;
droptable if exists `question_practice_detail`;
CREATETABLE `user_profile` (
`id` intNOTNULL,
`device_id` intNOTNULL,
`gender` varchar(14) NOTNULL,
`age` int ,
`university` varchar(32) NOTNULL,
`gpa` float,
`active_days_within_30` int ,
`question_cnt` int ,
`answer_cnt` int
);
CREATETABLE `question_practice_detail` (
`id` intNOTNULL,
`device_id` intNOTNULL,
`question_id`int NOTNULL,
`result` varchar(32) NOTNULL
);
CREATETABLE `question_detail` (
`id` intNOTNULL,
`question_id`int NOTNULL,
`difficult_level` varchar(32) NOTNULL
);
INSERTINTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4,7,2,12);
INSERTINTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0,15,5,25);
INSERTINTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2,12,3,30);
INSERTINTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6,5,1,2);
INSERTINTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8,20,15,70);
INSERTINTO user_profile VALUES(6,2131,'male',28,'山东大学',3.3,15,7,13);
INSERTINTO user_profile VALUES(7,4321,'male',28,'复旦大学',3.6,9,6,52);
INSERTINTO question_practice_detail VALUES(1,2138,111,'wrong');
INSERTINTO question_practice_detail VALUES(2,3214,112,'wrong');
INSERTINTO question_practice_detail VALUES(3,3214,113,'wrong');
INSERTINTO question_practice_detail VALUES(4,6543,111,'right');
INSERTINTO question_practice_detail VALUES(5,2315,115,'right');
INSERTINTO question_practice_detail VALUES(6,2315,116,'right');
INSERTINTO question_practice_detail VALUES(7,2315,117,'wrong');
INSERTINTO question_practice_detail VALUES(8,5432,117,'wrong');
INSERTINTO question_practice_detail VALUES(9,5432,112,'wrong');
INSERTINTO question_practice_detail VALUES(10,2131,113,'right');
INSERTINTO question_practice_detail VALUES(11,5432,113,'wrong');
INSERTINTO question_practice_detail VALUES(12,2315,115,'right');
INSERTINTO question_practice_detail VALUES(13,2315,116,'right');
INSERTINTO question_practice_detail VALUES(14,2315,117,'wrong');
INSERTINTO question_practice_detail VALUES(15,5432,117,'wrong');
INSERTINTO question_practice_detail VALUES(16,5432,112,'wrong');
INSERTINTO question_practice_detail VALUES(17,2131,113,'right');
INSERTINTO question_practice_detail VALUES(18,5432,113,'wrong');
INSERTINTO question_practice_detail VALUES(19,2315,117,'wrong');
INSERTINTO question_practice_detail VALUES(20,5432,117,'wrong');
INSERTINTO question_practice_detail VALUES(21,5432,112,'wrong');
INSERTINTO question_practice_detail VALUES(22,2131,113,'right');
INSERTINTO question_practice_detail VALUES(23,5432,113,'wrong');
INSERTINTO question_detail VALUES(1,111,'hard');
INSERTINTO question_detail VALUES(2,112,'medium');
INSERTINTO question_detail VALUES(3,113,'easy');
INSERTINTO question_detail VALUES(4,115,'easy');
INSERTINTO question_detail VALUES(5,116,'medium');
INSERTINTO question_detail VALUES(6,117,'easy');
- 题目描述:想要将用户划分为25岁以下和25岁及以上两个年龄段,分别查看这两个年龄段用户数量
- 分析1:将用户划分为25岁和25岁及以上:
select
case
when age < 25 then'25岁以下'
when age >= then '25岁及以上'
end age_cnt
from
user_profile
groupby
age_cnt;
- 分析2:分别查看这两个年龄段用户数量,就是统计计数:使用关键词:count:
select
case
when age < 25 then'25岁以下'
when age >= then '25岁及以上'
end age_cnt
count(1) as number
from
user_profile
groupby
age_cnt;
- 代码示例:
select
case
when age < 25
or age isnull then '25岁以下'
when age >= 25 then '25岁及以上'
end age_cnt,
count(1) as number
from
user_profile
groupby
age_cnt;
第二十七例:日期函数,计算用户8月每天的练题数量
- 初始化数据:
droptable if exists `user_profile`;
droptable if exists `question_practice_detail`;
droptable if exists `question_detail`;
CREATETABLE `user_profile` (
`id` intNOTNULL,
`device_id` intNOTNULL,
`gender` varchar(14) NOTNULL,
`age` int ,
`university` varchar(32) NOTNULL,
`gpa` float,
`active_days_within_30` int ,
`question_cnt` int ,
`answer_cnt` int
);
CREATETABLE `question_practice_detail` (
`id` intNOTNULL,
`device_id` intNOTNULL,
`question_id`int NOTNULL,
`result` varchar(32) NOTNULL,
`date` dateNOTNULL
);
CREATETABLE `question_detail` (
`id` intNOTNULL,
`question_id`int NOTNULL,
`difficult_level` varchar(32) NOTNULL
);
INSERTINTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4,7,2,12);
INSERTINTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0,15,5,25);
INSERTINTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2,12,3,30);
INSERTINTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6,5,1,2);
INSERTINTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8,20,15,70);
INSERTINTO user_profile VALUES(6,2131,'male',28,'山东大学',3.3,15,7,13);
INSERTINTO user_profile VALUES(7,4321,'male',28,'复旦大学',3.6,9,6,52);
INSERTINTO question_practice_detail VALUES(1,2138,111,'wrong','2021-05-03');
INSERTINTO question_practice_detail VALUES(2,3214,112,'wrong','2021-05-09');
INSERTINTO question_practice_detail VALUES(3,3214,113,'wrong','2021-06-15');
INSERTINTO question_practice_detail VALUES(4,6543,111,'right','2021-08-13');
INSERTINTO question_practice_detail VALUES(5,2315,115,'right','2021-08-13');
INSERTINTO question_practice_detail VALUES(6,2315,116,'right','2021-08-14');
INSERTINTO question_practice_detail VALUES(7,2315,117,'wrong','2021-08-15');
INSERTINTO question_practice_detail VALUES(8,3214,112,'wrong','2021-05-09');
INSERTINTO question_practice_detail VALUES(9,3214,113,'wrong','2021-08-15');
INSERTINTO question_practice_detail VALUES(10,6543,111,'right','2021-08-13');
INSERTINTO question_practice_detail VALUES(11,2315,115,'right','2021-08-13');
INSERTINTO question_practice_detail VALUES(12,2315,116,'right','2021-08-14');
INSERTINTO question_practice_detail VALUES(13,2315,117,'wrong','2021-08-15');
INSERTINTO question_practice_detail VALUES(14,3214,112,'wrong','2021-08-16');
INSERTINTO question_practice_detail VALUES(15,3214,113,'wrong','2021-08-18');
INSERTINTO question_practice_detail VALUES(16,6543,111,'right','2021-08-13');
INSERTINTO question_detail VALUES(1,111,'hard');
INSERTINTO question_detail VALUES(2,112,'medium');
INSERTINTO question_detail VALUES(3,113,'easy');
INSERTINTO question_detail VALUES(4,115,'easy');
INSERTINTO question_detail VALUES(5,116,'medium');
INSERTINTO question_detail VALUES(6,117,'easy');
- 题目描述:想要计算出2021年8月用户练题数目的数量
- 分析1:计算日期当然使用日期函数day:
select
day(date) as day
from
question_detail
wheredatelike'2021-8-%';
- 代码示例解法1:
select
DAY(date) as day, count(date) as question_cnt
from
question_practice_detail
where
datelike'2021-08-%'
groupby
date;
- 代码示例解法2:
select
DAY(date) as day, count(date) as question_cnt
from
question_practice_detail
where
date('2021-8-31') >= dateanddate(2021-8-1) <= date
groupby
date;
- 代码示例解法3:
-- 使用YEAR()获取年份,使用函数MONTH()获取月份
select
DAY(date) as day, count(date) as question_cnt
from
question_practice_detail
where
year(date) = 2021
and month(date) = 08;
groupby
day;