第二十例：分组查询，分组排序练习题
- 题目链接：分组排序练习题牛客题霸牛客网 (nowcoder.com)
- 初始化数据：
droptable if exists user_profile;
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
);
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);
- 题目描述：查看不同大学的用户平均发帖情况，并期望结果按照平均发帖情况进行升序排列。
- 分析1：不同大学的平均发帖情况：select university round(avg(question_cnt),4) as avg_question_cnt from user_profile group by university;
- 分析2：然后按照升序排列使用asc：select university ,round(avg(question_cnt),4) as avg_question_cnt from user_profile group by university order by avg_question_cnt asc;
- 代码示例：
select
university,round(avg(question_cnt), 4) as avg_question_cnt
from
user_profile
groupby
university
orderby
avg_question_cnt asc;
第二十一例：子查询，浙江大学用户题目回答情况
- 题目链接：浙江大学用户题目回答情况牛客题霸牛客网 (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
);
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,118,'wrong');
INSERTINTO question_practice_detail VALUES(9,5432,112,'wrong');
INSERTINTO question_practice_detail VALUES(10,2131,114,'right');
INSERTINTO question_practice_detail VALUES(11,5432,113,'wrong');
- 题目：现在运营想要查看所有来自浙江大学的用户题目回答明细情况，请你取出相应数据
分析1：需要在user_profile表找出浙江大学的数据:select device_id from user_profile where university='浙江大学';
分析2：在question_practice_detail表中找到对应设备的id，涉及两表就需要做表连接，这里使用等值连接inner join，用来返回相同一行的数据：up.device_id=qpd.device_id;
示例代码
select
up.device_id,qpd.question_id,qpd.result
from
user_profile up
inner join
question_practice_detail qpd
where
up.university='浙江大学'and qpd.device_id=up.device_id
orderby
qpd.question_idasc;
