sql做题第十二天

第二十六例：条件函数，计算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;