实验8 SQL的复杂多表查询-2

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

USE `sqlexp-sztuoj`;
#请在此处添加实现代码
########## Begin ##########
SELECT s.user_id,s.problem_id
FROM(
    SELECT user_id,AVG(memory) AS AM
    FROM solution
    GROUP BY user_id
) AS Ta
JOIN solution s USING(user_id)
WHERE s.memory > Ta.AM

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

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

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级其中一个选手注册时间早即可的选手

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级选手注册时间都早的选手信息

USE `sqlexp-sztuoj`;
#请在此处添加实现代码
########## Begin ##########
SELECT *
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级选手注册时间都早的选手信息

USE `sqlexp-sztuoj`;
#请在此处添加实现代码
########## Begin ##########
SELECT *
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"选手参与过的所有比赛的选手信息

USE `sqlexp-sztuoj`;
#请在此处添加实现代码
########## Begin ##########
SELECT *
FROM users
WHERE NOT EXISTS(
    SELECT *
    FROM (
        SELECT contest_id
        FROM solution s
        WHERE user_id = '202002020217' AND contest_id IS NOT NULL
    ) s1
    WHERE NOT EXISTS (
        SELECT *
        FROM solution s2
        WHERE s1.contest_id = s2.contest_id AND users.user_id = s2.user_id
    )
)

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

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

USE mydata;
#请在此处添加实现代码
########## Begin ##########
SELECT DISTINCT a.JNO
FROM(
    SELECT *
    FROM SPJ
    WHERE PNO IN (
        SELECT PNO
        FROM (
            SELECT PNO,JNO
            FROM SPJ
            WHERE SNO = 'S1'
        ) Ta
    )
) a,
(
    SELECT *
    FROM SPJ
    WHERE PNO IN (
        SELECT PNO
        FROM (
            SELECT PNO,JNO
            FROM SPJ
            WHERE SNO = 'S1'
        ) Ta
    )
) b
WHERE a.JNO = b.JNO AND a.PNO != b.PN

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