【牛客刷题--SQL篇】多表查询 SQL21浙江大学用户题目回答情况（三种写法）

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

文章目录

一、多表查询

1、子查询

1.1、SQL21 浙江大学用户题目回答情况

• 描述

题目：现在运营想要查看所有来自浙江大学的用户题目回答明细情况，请你取出相应数据

• 示例1

输入：
drop table if exists

user_profile

;
drop table if exists

question_practice_detail

;
CREATE TABLE

user_profile

(

id

int NOT NULL,

device_id

int NOT NULL,

gender

varchar(14) NOT NULL,

age

int ,

university

varchar(32) NOT NULL,

gpa

float,

active_days_within_30

int ,

question_cnt

int ,

answer_cnt

int
);
CREATE TABLE

question_practice_detail

(

id

int NOT NULL,

device_id

int NOT NULL,

question_id

int NOT NULL,

result

varchar(32) NOT NULL
);
INSERT INTO user_profile VALUES(1,2138,‘male’,21,‘北京大学’,3.4,7,2,12);
INSERT INTO user_profile VALUES(2,3214,‘male’,null,‘复旦大学’,4.0,15,5,25);
INSERT INTO user_profile VALUES(3,6543,‘female’,20,‘北京大学’,3.2,12,3,30);
INSERT INTO user_profile VALUES(4,2315,‘female’,23,‘浙江大学’,3.6,5,1,2);
INSERT INTO user_profile VALUES(5,5432,‘male’,25,‘山东大学’,3.8,20,15,70);
INSERT INTO user_profile VALUES(6,2131,‘male’,28,‘山东大学’,3.3,15,7,13);
INSERT INTO user_profile VALUES(7,4321,‘male’,28,‘复旦大学’,3.6,9,6,52);
INSERT INTO question_practice_detail VALUES(1,2138,111,‘wrong’);
INSERT INTO question_practice_detail VALUES(2,3214,112,‘wrong’);
INSERT INTO question_practice_detail VALUES(3,3214,113,‘wrong’);
INSERT INTO question_practice_detail VALUES(4,6543,111,‘right’);
INSERT INTO question_practice_detail VALUES(5,2315,115,‘right’);
INSERT INTO question_practice_detail VALUES(6,2315,116,‘right’);
INSERT INTO question_practice_detail VALUES(7,2315,117,‘wrong’);
INSERT INTO question_practice_detail VALUES(8,5432,118,‘wrong’);
INSERT INTO question_practice_detail VALUES(9,5432,112,‘wrong’);
INSERT INTO question_practice_detail VALUES(10,2131,114,‘right’);
INSERT INTO question_practice_detail VALUES(11,5432,113,‘wrong’);

输出：
2315|115|right
2315|116|right
2315|117|wrong

输入：
droptableifexists`user_profile`;droptableifexists`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`intNOTNULL,`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');

输出：
2315|115|right2315|116|right2315|117|wrong

1.1.1、SQL语句（第一种写法）

select
q.device_id,
q.question_id,
q.result
from
question_practice_detail q,
user_profile u
where
q.device_id = u.device_id
and university = ‘浙江大学’
order by
q.question_id asc

select
  q.device_id,
  q.question_id,
  q.result
from
  question_practice_detail q,
  user_profile u
where
  q.device_id = u.device_id
  and university ='浙江大学'orderby
  q.question_id asc

1.1.2、SQL语句（第二种写法）

select
q.device_id,
q.question_id,
q.result
from
question_practice_detail q
join user_profile u on q.device_id = u.device_id
and university = ‘浙江大学’
order by
q.question_id asc

select
  q.device_id,
  q.question_id,
  q.result
from
  question_practice_detail q
  join user_profile u on q.device_id = u.device_id
  and university ='浙江大学'orderby
  q.question_id asc

1.1.3、SQL语句（第三种写法）

select
device_id,
question_id,
result
from
question_practice_detail
where
device_id in (
select
device_id
from
user_profile
where
university = ‘浙江大学’
)
order by
question_id asc

select
  device_id,
  question_id,
  result
from
  question_practice_detail
where
  device_id in(select
      device_id
    from
      user_profile
    where
      university ='浙江大学')orderby
  question_id asc

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