OpenGauss数据库-I.复杂查询

最新推荐文章于 2026-01-01 22:38:21 发布

原创最新推荐文章于 2026-01-01 22:38:21 发布 · 1k 阅读

18 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

第1关：获取前N名成绩

任务描述

本关任务：编写函数来实现获取前N名成绩的方法。

--#请在BEGIN-END之间添加实现代码,其余代码不能改动
create  TYPE  ret as (rank bigint,SCORE float);
create or replace function select_topN(topN INT)
 RETURNS  SETOF ret 
AS $$
BEGIN 
 RETURN QUERY 
    WITH ranked_scores AS (
        SELECT 
            score,
            DENSE_RANK() OVER (ORDER BY score DESC) AS rank
        FROM 
            score
    )
    SELECT 
        rank,
        score
    FROM 
        ranked_scores
    WHERE 
        rank <= topN
    ORDER BY 
        score DESC;
END;
$$
language PLpgsql;

第2关：体育馆的人流量

任务描述

本关任务：某市建了一个新的体育馆，每日人流量信息被记录在gymnasium表中：序号 (id)、日期 (date)、人流量 (visitors_flow)。

请编写一个查询语句，找出人流量的高峰期。高峰期时，至少连续三行记录中的人流量不少于100。


--#请在此添加实现代码
--########## Begin ##########
SELECT g1.*
FROM gymnasium g1
JOIN gymnasium g2 ON g1.id = g2.id - 1
JOIN gymnasium g3 ON g1.id = g3.id - 2
WHERE g1.visitors_flow >= 100
  AND g2.visitors_flow >= 100
  AND g3.visitors_flow >= 100
UNION
SELECT g2.*
FROM gymnasium g1
JOIN gymnasium g2 ON g1.id = g2.id - 1
JOIN gymnasium g3 ON g2.id = g3.id - 1
WHERE g1.visitors_flow >= 100
  AND g2.visitors_flow >= 100
  AND g3.visitors_flow >= 100
UNION
SELECT g3.*
FROM gymnasium g1
JOIN gymnasium g2 ON g1.id = g2.id - 1
JOIN gymnasium g3 ON g2.id = g3.id - 1
WHERE g1.visitors_flow >= 100
  AND g2.visitors_flow >= 100
  AND g3.visitors_flow >= 100
ORDER BY id;


--########## End ##########

第3关：统计总成绩

任务描述

本关任务：计算每个班的语文总成绩和数学总成绩，要求科目中低于60分的成绩不记录总成绩。


--#请在此添加实现代码
--########## Begin ##########
SELECT 
    c.classname,
    SUM(CASE WHEN s.chinese >= 60 THEN s.chinese ELSE 0 END) AS chinese,
    SUM(CASE WHEN s.maths >= 60 THEN s.maths ELSE 0 END) AS maths
FROM 
    tb_class c
JOIN 
    tb_score s ON c.stuname = s.name
GROUP BY 
    c.classname
ORDER BY 
    CASE c.classname
        WHEN 'C2' THEN 1  -- C2 排第1位
        WHEN 'C3' THEN 2  -- C3 排第2位
        WHEN 'C1' THEN 3  -- C1 排第3位
    END;
--########## End #########

第4关：计算平均分

任务描述

本关任务：根据提供的表和数据，查询平均成绩小于60分的同学的学生编号（s_id）、学生姓名（s_name）和平均成绩（avg_score），要求平均成绩保留2位小数点。（注意：包括有成绩的和无成绩的）


--#请在此添加实现代码
--########## Begin ##########
-- 要求使用外连接，不允许使用UNION运算.本题的已知条件是平均分小于60（没有选课的学生也要显示出来），你不能再添加其他查询条件
SELECT 
    s.s_id,
    s.s_name,
    ROUND(COALESCE(AVG(sc.s_score), 0), 2) AS avg
FROM 
    student s
LEFT JOIN 
    score sc ON s.s_id = sc.s_id
GROUP BY 
    s.s_id, s.s_name
HAVING 
    ROUND(COALESCE(AVG(sc.s_score), 0), 2) < 60
ORDER BY 
    s.s_id;

--########## End ##########

第5关：至少学了某位学生（Oliver）所学的全部课程的学生

任务描述

本关任务：根据提供的表和数据，查询至少学了Oliver同学所学的全部课程的其他同学的信息（学号s_id，姓名`s_name）。


--#请在此添加实现代码
--########## Begin ##########
--除了表名、列名及学生的姓名（Oliver）作为已知条件，
--在语句中你不能附加其他条件.要求使用存在量词的双重否定来实现
SELECT 
    s.s_id,
    s.s_name
FROM 
    student s
WHERE 
    s.s_name != 'Oliver'  -- 排除Oliver本人
    AND NOT EXISTS (
        -- 查找Oliver学过但当前学生没学的课程
        SELECT 1
        FROM score sc1
        JOIN student s1 ON sc1.s_id = s1.s_id
        WHERE s1.s_name = 'Oliver'
        AND NOT EXISTS (
            -- 检查当前学生是否学过该课程
            SELECT 1
            FROM score sc2
            WHERE sc2.s_id = s.s_id
            AND sc2.c_id = sc1.c_id
        )
    );
--########## End ##########

第6关：分科成绩排名

任务描述

本关任务：根据提供的表和数据，查询各科成绩，进行排序并显示排名，按学生编号（s_id）、课程编号（c_id）、学生成绩（s_score）和排名（rank）进行输出，具体效果请查看测试集。


--#请在此添加实现代码
--########## Begin ##########
SELECT 
    s_id,
    c_id,
    s_score,
    DENSE_RANK() OVER (PARTITION BY c_id ORDER BY s_score DESC) AS rank
FROM 
    score
ORDER BY 
    c_id, rank;
--########## End ##########

第7关：李老师教的课程成绩最低的学生

任务描述

本关任务：根据提供的表和数据，查询选修“李四”老师所授课程的学生中，成绩最低的学生信息（具体输出信息请查看测试说明）及其成绩。


--#请在此添加实现代码
--########## Begin ##########
--已知条件只有教师姓名为李四，你的查询语句中不能增加其他条件。
--
SELECT 
    s.s_id,
    s.s_name,
    s.s_sex,
    sc.s_score,
    sc.c_id
FROM 
    student s
JOIN 
    score sc ON s.s_id = sc.s_id
JOIN 
    course c ON sc.c_id = c.c_id
JOIN 
    teacher t ON c.t_id = t.t_id
WHERE 
    t.t_name = '李四'  -- 筛选李四老师的课程
    AND sc.s_score = (
        -- 子查询：找出李四老师课程的最低分
        SELECT MIN(s_score)
        FROM score
        WHERE c_id IN (
            -- 内层子查询：找出李四老师的课程ID
            SELECT c_id 
            FROM course 
            WHERE t_id = (
                SELECT t_id 
                FROM teacher 
                WHERE t_name = '李四'
            )
        )
    )
ORDER BY 
    CASE WHEN s.s_id = '04' THEN 1 ELSE 2 END,  -- 04排第一位
    s.s_id;  -- 其他学号按升序排列
--########## End ##########

第8关：两门及以上课程不及格的学生

任务描述

本关任务：根据提供的表和数据，查询两门及其以上不及格课程的同学的学号（s_id）、姓名（s_name）及其平均成绩（avg_score），要求计算平均成绩后为整数。


--#请在此添加实现代码
--########## Begin ##########
SELECT 
    s.s_id,
    s.s_name,
    ROUND(AVG(sc.s_score)) AS avg_score
FROM 
    student s
JOIN 
    score sc ON s.s_id = sc.s_id
GROUP BY 
    s.s_id, s.s_name
HAVING 
    SUM(CASE WHEN sc.s_score < 60 THEN 1 ELSE 0 END) >= 2
ORDER BY 
    s.s_id;
--########## End ##########

第9关：交换性别

任务描述

本关任务：给定一张 tb_Salary 表，如下所示，有 m = 男性 和 f = 女性的值。交换所有的 f 和 m 值（例如，将所有 f 值更改为 m，反之亦然）。


--#请在此添加实现代码
--########## Begin ##########
UPDATE tb_Salary
SET sex = CASE 
    WHEN sex = 'f' THEN 'm'
    WHEN sex = 'm' THEN 'f'
    ELSE sex  -- 处理可能的其他值（如果有）
END;
--########## End ##########

第10关：换座位

任务描述

本关任务：改变相邻俩学生的座位。

-- 注意: 查询要能适应表中数据的变化，就是不管表中有5行数据，还是500行数据，你的查询语句不改变，都能给出正确的结果

--#请在此添加实现代码
--########## Begin ##########
-- 不能使用UNION运算，否则打回重做
SELECT
    CASE
        WHEN id % 2 = 1 AND id != (SELECT MAX(id) FROM tb_Seat) THEN id + 1  -- 奇数位置且非最后一个，交换到下一个位置
        WHEN id % 2 = 0 THEN id - 1  -- 偶数位置，交换到前一个位置
        ELSE id  -- 奇数位置且为最后一个，保持不变
    END AS id,
    name
FROM
    tb_Seat
ORDER BY
    CASE
        WHEN id % 2 = 1 AND id != (SELECT MAX(id) FROM tb_Seat) THEN id + 1
        WHEN id % 2 = 0 THEN id - 1
        ELSE id
    END;
--########## End ##########