一、多表查询
1、链接查询
1.1、SQL22 统计每个学校的答过题的用户的平均答题数
描述
运营想要了解每个学校答过题的用户平均答题数量情况，请你取出数据。
示例1
输入：
drop table if exists user_profile; drop table if exists question_practice_detail; CREATE TABLE user_profile ( device_id int NOT NULL, gender varchar(14) NOT NULL, age int , university varchar(32) NOT NULL, gpa float, active_days_within_30 int ); CREATE TABLE question_practice_detail ( device_id int NOT NULL, question_idint NOT NULL, result varchar(32) NOT NULL ); INSERT INTO user_profile VALUES(2138,‘male’,21,‘北京大学’,3.4,7); INSERT INTO user_profile VALUES(3214,‘male’,null,‘复旦大学’,4.0,15); INSERT INTO user_profile VALUES(6543,‘female’,20,‘北京大学’,3.2,12); INSERT INTO user_profile VALUES(2315,‘female’,23,‘浙江大学’,3.6,5); INSERT INTO user_profile VALUES(5432,‘male’,25,‘山东大学’,3.8,20); INSERT INTO user_profile VALUES(2131,‘male’,28,‘山东大学’,3.3,15); INSERT INTO user_profile VALUES(4321,‘male’,28,‘复旦大学’,3.6,9); INSERT INTO question_practice_detail VALUES(2138,111,‘wrong’); INSERT INTO question_practice_detail VALUES(3214,112,‘wrong’); INSERT INTO question_practice_detail VALUES(3214,113,‘wrong’); INSERT INTO question_practice_detail VALUES(6543,111,‘right’); INSERT INTO question_practice_detail VALUES(2315,115,‘right’); INSERT INTO question_practice_detail VALUES(2315,116,‘right’); INSERT INTO question_practice_detail VALUES(2315,117,‘wrong’); INSERT INTO question_practice_detail VALUES(5432,118,‘wrong’); INSERT INTO question_practice_detail VALUES(5432,112,‘wrong’); INSERT INTO question_practice_detail VALUES(2131,114,‘right’); INSERT INTO question_practice_detail VALUES(5432,113,‘wrong’);
输出：
北京大学|1.0000 复旦大学|2.0000 山东大学|2.0000 浙江大学|3.0000
1.1.1、SQL语句(第一种写法)
select u.university, round( (count(q.device_id) / count(distinct(q.device_id))), 4 ) avg_answer_cnt from user_profile u, question_practice_detail q where u.device_id = q.device_id group by university order by university asc
1.1.2、SQL语句(第二种写法)
select u.university, round( (count(q.device_id) / count(distinct(q.device_id))), 4 ) avg_answer_cnt from user_profile u join question_practice_detail q on u.device_id = q.device_id group by university order by university asc
