【头歌系统数据库实验】实验8 SQL的复杂多表查询-2

目录

第1关：基于派生表查询每个队员解答中超过他平均memory的user_id及题目编号problem_id

第2关：用ANY/ALL实现查询2019级选手（user_id前4位为2019）满足比2020级其中一个选手注册时间早即可的选手

第3关：用聚集查询实现查询2019级选手（user_id前4位为2019）满足比2020级其中一个选手注册时间早即可的选手

第4关：用ANY/ALL实现查询2019级选手所有比2020级选手注册时间都早的选手信息

第5关：用聚集查询实现查询2019级选手所有比2020级选手注册时间都早的选手信息

第6关：用 NOT EXISTS 实现查询至少参与过"202002020217"选手参与过的所有比赛的选手信息

第7关：求至少用了供应商 S1所供应的全部零件的工程号 JNO

---

如果对你有帮助的话，不妨点赞收藏评论一下吧，爱你么么哒😘❤️❤️❤️

第1关：基于派生表查询每个队员解答中超过他平均memory的user_id及题目编号problem_id

任务描述

基于派生表查询每个队员解答中超过他平均memory的user_id及题目编号problem_id（查询结果无需去重） 注意：按课本的别称表名+列名的方式，mysql8.0可以过，但oj题用的是mysql5.7，不能通过，所以仅用表名别称。

相关知识

solution：选手提交的题目解答 solution表如下图（仅显示前几条）：

现已构建solution表，结构信息如下：

---

开始你的任务吧，祝你成功!

USE `sqlexp-sztuoj`;
#请在此处添加实现代码
########## Begin ##########

select solution.user_id, problem_id 
from solution
join (select 
user_id, avg(memory) 
as am 
from solution 
group by user_id) uam
on solution.user_id = uam.user_id and memory > am;

########## End ##########

第2关：用ANY/ALL实现查询2019级选手（user_id前4位为2019）满足比2020级其中一个选手注册时间早即可的选手

任务描述

用ANY/ALL实现查询2019级选手（user_id前4位为2019）满足比2020级其中一个选手注册时间（reg_time）早即可的选手信息

相关知识

users为选手信息表； users表如下图（仅显示前几条）：

现已构建users表，结构信息如下：

---

开始你的任务吧，祝你成功

USE `sqlexp-sztuoj`;
#请在此处添加实现代码
########## Begin ##########
select user_id, reg_time, name 
from users
where user_id like '2019%' and reg_time < any(
    select reg_time 
    from users 
    where user_id like '2020%');


########## End ##########

第3关：用聚集查询实现查询2019级选手（user_id前4位为2019）满足比2020级其中一个选手注册时间早即可的选手

任务描述

用聚集查询实现查询2019级选手（user_id前4位为2019）满足比2020级其中一个选手注册时间（reg_time）早即可的选手信息

相关知识

users为选手信息表； users表如下图（仅显示前几条）：

现已构建users表，结构信息如下：

---

开始你的任务吧，祝你成功

USE `sqlexp-sztuoj`;
#请在此处添加实现代码
########## Begin ##########
select user_id, reg_time, name 
from users
where user_id like '2019%' and reg_time < (
    select max(reg_time) 
    from users 
    where user_id like '2020%');

########## End ##########

第4关：用ANY/ALL实现查询2019级选手所有比2020级选手注册时间都早的选手信息

任务描述

用ANY/ALL实现查询2019级选手所有比2020级选手注册时间都早的选手信息

相关知识

users为选手信息表； users表如下图（仅显示前几条）：

现已构建users表，结构信息如下：

---

开始你的任务吧，祝你成功!

USE `sqlexp-sztuoj`;
#请在此处添加实现代码
########## Begin ##########
select user_id, reg_time, name 
from users
where user_id like '2019%' and reg_time < all(
    select reg_time
    from users
    where user_id like '2020%'
);

########## End ##########

第5关：用聚集查询实现查询2019级选手所有比2020级选手注册时间都早的选手信息

任务描述

聚集查询实现查询2019级选手所有比2020级选手注册时间都早的选手信息

相关知识

users为选手信息表； users表如下图（仅显示前几条）：

现已构建users表，结构信息如下：

---

开始你的任务吧，祝你成功

USE `sqlexp-sztuoj`;
#请在此处添加实现代码
########## Begin ##########
select user_id, reg_time, name 
from users
where user_id like '2019%' and reg_time < (
    select min(reg_time)
    from users
    where user_id like '2020%'
);

########## End ##########

第6关：用 NOT EXISTS 实现查询至少参与过"202002020217"选手参与过的所有比赛的选手信息

任务描述

用 NOT EXISTS 实现查询至少参与过"202002020217"选手参与过的所有比赛的选手信息，contest_id不为NULL

相关知识

1、users为选手信息表； users表如下图（仅显示前几条）：

现已构建users表，结构信息如下：

2、solution为选手提交的题目解答 solution表如下图（仅显示前几条）：

现已构建solution表，结构信息如下：

---

开始你的任务吧，祝你成功!

USE `sqlexp-sztuoj`;
#请在此处添加实现代码
########## Begin ##########
select u.user_id, u.reg_time, u.name 
from users u
where not exists(
    select contest_id 
    from (
        select s.contest_id 
        from solution s
        where s.user_id = '202002020217' and s.contest_id is not null) s1
    where not exists(
        select user_id, contest_id 
        from solution s2
        where s1.contest_id = s2.contest_id and u.user_id = s2.user_id)
);


########## End ##########

第7关：求至少用了供应商 S1所供应的全部零件的工程号 JNO

任务描述

求至少用了供应商 S1所供应的全部零件的工程号 JNO

相关知识

供应情况表SPJ由供应商代码(SNO)、零件代码(PNO)、工程项目代码(JNO)、供应数量(QTY)组成，标识某供应商 供应某种零件 给某工程项目的数量为QTY。 SPJ表如下图：

现已构建SPJ表，结构信息如下：

---

开始你的任务吧，祝你成功!

USE mydata;
#请在此处添加实现代码
########## Begin ##########
select JNO
from SPJ
where PNO ='P1' and JNO in
(select JNO
 from SPJ
 where PNO='P2');
########## End ##########