【牛客刷题--SQL篇】多表查询链接查询 SQL22统计每个学校的答过题的用户的平均答题数

💖个人主页：@与自己作战
💯作者简介：CSDN@博客专家、CSDN@大数据领域优质创作者、CSDN@内容合伙人、阿里云@专家博主
💞牛客刷题系列篇：【SQL篇】】【Python篇】【Java篇】
📌推荐刷题网站注册地址：【牛客网–SQL篇】
💘推荐理由：从0-1起步，循序渐进
🆘希望大佬们多多支持，携手共进
📝 如果文章对你有帮助的话，欢迎评论💬点赞👍收藏📂加关注
⛔如需要支持请私信我，💯必支持
👩‍👩‍👦‍👦网址注册地址：【牛客网–注册地址】👩‍👩‍👦‍👦

一、多表查询

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

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
2
3
4
5
6
7
8
9
10
11
12
13
14
15

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

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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

推荐刷题网站：【牛客网–SQL篇】
网址注册地址：【牛客网–注册地址】