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

文章目录

• 一、多表查询
• • 1、子查询
  • 1.1、SQL21 浙江大学用户题目回答情况
  • • 1.1.1、SQL语句（第一种写法）
    • 1.1.2、SQL语句（第二种写法）
    • 1.1.3、SQL语句（第三种写法）

一、多表查询

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_idint 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

输入：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|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.resultfrom  question_practice_detail q,  user_profile uwhere  q.device_id = u.device_id  and university = '浙江大学'order by  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.resultfrom  question_practice_detail q  join user_profile u on q.device_id = u.device_id  and university = '浙江大学'order by  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,  resultfrom  question_practice_detailwhere  device_id in (    select      device_id    from      user_profile    where      university = '浙江大学'  )order by  question_id asc

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