GaussDB常见SQL练习

以下是GaussDB 部分 SQL 练习题，涵盖基础到进阶的典型场景。题目设计基于标准数据库场景（如员工管理、订单统计），并附答案、解析及关键点提示。所有语法均符合 GaussDB 的 PostgreSQL 兼容规范。

一、核心练习题（附答案与解析）

表结构说明（基于常见场景）：

• employees：员工表（emp_id 主键，name 姓名，dept_id 部门ID，salary 工资）
• departments：部门表（dept_id 主键，dept_name 部门名称）
• orders：订单表（order_id 主键，customer_id 客户ID，order_date 订单日期，amount 金额）

题目 1：基础查询（WHERE + JOIN）

问题：查询工资在 5000~8000 之间的员工姓名、部门名称，并按工资从高到低排序。
答案：

SELECT e.name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
WHERE e.salary BETWEEN 5000 AND 8000
ORDER BY e.salary DESC;

解析：

• JOIN 连接两个表获取部门名称。
• BETWEEN 精准筛选工资范围（GaussDB 支持）。
• ORDER BY 排序，DESC 降序（默认 ASC）。

题目 2：分组聚合（GROUP BY + HAVING）

问题：统计每个部门的平均工资，仅显示平均工资 > 4000 的部门，并按平均工资降序排列。
答案：

SELECT d.dept_name, AVG(e.salary) AS avg_salary
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
GROUP BY d.dept_name
HAVING AVG(e.salary) > 4000
ORDER BY avg_salary DESC;

解析：

• GROUP BY 按部门分组。
• HAVING 用于过滤分组后的结果（区别于 WHERE）。
• AVG() 聚合函数计算平均值。

题目 3：子查询（相关子查询）

问题：查询工资高于其所在部门平均工资的员工姓名、工资和部门名称。
答案：

SELECT e.name, e.salary, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.dept_id = e.dept_id
);

解析：

• 相关子查询：子查询引用外层查询的 e.dept_id。
• 用 > 比较员工工资与部门平均工资。
• 关键点：子查询需返回单值（AVG 保证）。

题目 4：窗口函数（窗口排名）

问题：查询每个部门工资排名前 3 的员工（允许并列），显示部门名称、员工姓名、工资。
答案：

SELECT dept_name, name, salary
FROM (
    SELECT 
        d.dept_name, 
        e.name, 
        e.salary,
        RANK() OVER (
            PARTITION BY d.dept_id 
            ORDER BY e.salary DESC
        ) AS rank
    FROM employees e
    JOIN departments d ON e.dept_id = d.dept_id
) ranked
WHERE rank <= 3;

解析：

• RANK() 窗口函数：并列排名（如工资相同则排名相同）。
• PARTITION BY 按部门分组，ORDER BY 按工资降序。
• 外层 WHERE 过滤排名 ≤ 3 的记录。
• 对比：若用 ROW_NUMBER() 会严格排序（无并列）。

题目 5：日期函数 + 聚合（时间范围统计）

问题：统计 2023 年 10 月的总订单金额，并计算订单数量。
答案：

SELECT 
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount
FROM orders
WHERE order_date >= '2023-10-01' AND order_date < '2023-11-01';

解析：

• 日期范围用 >= 和 < 精确控制（避免 BETWEEN 的边界问题）。
• SUM(amount) 计算总金额，COUNT(*) 统计订单数。
• GaussDB 特性：日期格式需为 YYYY-MM-DD。

drop table if exists student;

drop table if exists class;

create table student(sno int,sname varchar(20),score int,month int,cno int);

--core表示当月月考总分，月考总分为NULL，说明当月缺考

create table class(cno int,cname varchar(20));

insert into class values(1,'class1'),(2,'class2');

insert into student values(1,'Lee',610,1,1),(2,'Jerry',510,1,1),(5,'Lee',410,1,1),(3,'Tom',400,1,2),(4,'Jack',300,1,2),(6,'Jacy',NULL,1,2),(1,'Lee',410,2,1),(2,'Jerry',510,2,1),(5,'Lee',210,2,1),(3,'Tom',600,2,2),(4,'Jack',300,2,2),(6,'Jacy',510,2,2),(1,'Lee',410,3,1),(2,'Jerry',510,3,1),(5,'Lee',NULL,3,1),(3,'Tom',NULL,3,2),(4,'Jack',300,3,2),(6,'Jacy',410,3,2);

(1) 输出每月月考总分都比学号为5的同学分数高的所有学生信息

--计算的按照每个月相比，结果集累加（不符合题意）

select sno,sname,t1.month,t1.score from (select month,sno,sname,nvl(score,0) score from student) t1 join (select month,nvl(score,0) score from student where sno=5) t2 on t1.month=t2.month where t1.score>t2.score order by sno;

（1）获取学号为5的学生每个月的成绩

select month,nvl(score,0) score from student where sno=5;

（2）判断月考成绩比学号为5的学生高的所有学生的学号，根据学号分组计算每位学生有几次成绩大于学号为5的学生的成绩，

select t1.sno,count(*) as count from student t1 join (select month,nvl(score,0) score from student where sno=5) t2 on t1.month=t2.month where t1.score>t2.score group by t1.sno;

（3）having判断大于次数是否等于月份数量

select t1.sno,count(*) as count from student t1 join (select month,nvl(score,0) score from student where sno=5) t2 on t1.month=t2.month where t1.score>t2.score group by t1.sno having count=(select count(distinct month) from student);

（4）根据以上得出的sno获取学生信息

select student.* from student join (select t1.sno,count(*) as count from student t1 join (select month,nvl(score,0) score from student where sno=5) t2 on t1.month=t2.month where t1.score>t2.score group by t1.sno having count=(select count(distinct month) from student) ) stu2 on student.sno=stu2.sno;

(2)输出每次月考缺考的学生信息，要求打印姓名、班级编号和缺考次数。

select month,sname,cno,count(1) as "缺考次数" from student where score is null group by month,sname,cno;

(3)输出每次月考都和tom同时缺考的所有学生信息，要求打印学号、姓名和月考总分

select month,cno,sname,sum(nvl(score,0)) from student where month in (select month from student where sname='Tom' and score is NULL) and score is NULL and sname != 'Tom' group by month,cno,sname;

(4)输出全校月考中位分数。（GaussDB分布式没有median函数）

select round(avg(nvl(score,0)),0) from (select score from student order by nvl(score,0) limit (select ceil(count(1)/2) from student),case when (select count(1)%2 from student)=0 then 2 else 1  end);

--按照月份输出中位数

1. 给每个月的成绩一个排序序号，每个月是独立的，使用窗口函数，根据月份分组、成绩排序（nvl将score为null的值转为0）

2. 计算每个月的成绩数量

3. 根据月份把上面两张表关键起来

4. 这里因为窗口函数得到的row_number不能在同一层级用于计算，所以外层再嵌套一层循环，where根据判断count是否为偶数返回不同的条件，如果是偶数保留rowno中间的成绩，如果是奇数保留最中间一位就可以

5. 根据month分组，对同一月份的score求平均值，如果month对应只有一个值，对一个值求平均的结果还是这个值本身，所以不管month对应用于计算中位数的是两个成绩还是一个成绩，都统一求平均值

(5)统计每个班每次月考的最高分数，要求打印班级名称，考试时间和月考分数。

select cname,month,max(score) from student,class where student.cno=class.cno group by cname,month order by cname,month;

2.数据库对象管理及SQL应用2

(1)输出class1班级中比class2班级每月月考最低分还低的学生信息，要求打印学号、姓名和月考总分。

select t1.sno,t1.sname,nvl(t1.score,0) from (select * from student where cno=1) t1 join (select month,min(score) score from student where cno=2 group by month) t2 on t1.month=t2.month where nvl(t1.score,0)<nvl(t2.score,0);

(2)打印月考总分平均分最高的学生信息，输出学号，姓名和月考平均分。

select t1.* from (select sno,sname,round(avg(nvl(score,0)),2) /score from student group by sno,sname) t1 join (select max(score) score from (select sno,sname,round(avg(nvl(score,0)),2) score from student group by sno,sname)) t2 on t1.score=t2.score;

(3)输出每个学生月考平均分和最高月考平均分学生之间的分数差距，打印学号、姓名、月考平均分和差距分数。

select sno,sname,round(avg(nvl(score,0)),2) avgscore ,(select max(avgscore) from (select round(avg(nvl(score,0)),2) avgscore from student group by sno ))-avgscore from student group by sno,sname;

--计算所有学生的月考平均值

select round(avg(nvl(score,0)),2),avg from student group by sno;

--从上一步计算的所有学生的月考平均值中获取最大值

select max(avgscore) from (select round(avg(nvl(score,0)),2),avg from student group by sno);

3.数据库对象管理及SQL应用3

--创建表

create table stu(id int,math int,art int,phy int,music int);

--导入数据

insert into stu values (1,60,33,66,86);

(1)求math、phy总成绩以及art、music的总成绩

select sum(math+phy),sum(art+music) from stu;

(2)计算学生总成绩，并基于总成绩排序

select id,sum(math+phy+art+music) all_score from stu group by id order by all_score desc;

(3)art和music总分排名前5的总成绩加5分，查询最终的所有学生总成绩

(select stu.id,sum(score+math+phy) allscore from stu,(select id,sum(art+music)+5 score from stu group by id order by score desc limit 5) stu2 where stu.id=stu2.id group by stu.id)

union all

(select stu.id,sum(score+math+phy) allscore from stu,(select id,sum(art+music) score from stu group by id order by score desc offset 5) stu2 where stu.id=stu2.id group by stu.id);

4.数据库对象管理及SQL应用4

基于以下学生成绩表，完成以下实验要求。

--创建表

create table scopes(student_id int,chinese int,math int,english int,music int);

--导入数据

insert into scopes values(1,90,88,100,88);

insert into scopes values(2,988,88,100,99);

insert into scopes values(3,87,89,98,89);

insert into scopes values(4,91,88,76,99);

insert into scopes values(5,92,88,78,98);

insert into scopes values(6,93,88,76,87);

(1)计算每个学生的chinese和math总分，以及english和music总分，要求一条SQL语句实现，不能使用临时表。

select student_id,sum(chinese+math),sum(english+music) from scopes group by student_id;

(2)目前有一张权重表（各科有不同的权重，目前权重策略有2个），请算出每个学生结合权重计算后的成绩总和。要求一条SQL语句实现，不能使用临时表。每个学生都对应两个权重成绩。

权重表结构如下

create table weight(weight_id int,chinese decimal(10,2),math decimal(10,2),english decimal(10,2),music decimal(10,2));

insert into weight values(1,0.3,0.2,0.2,0.3);

insert into weight values(2,0.2,0.1,0.3,0.4);

select student_id,weight_id,sum(t1.chinese*t2.chinese + t1.math*t2.math + t1.english*t2.english + t1.music*t2.music) from scopes t1,weight t2 group by student_id,weight_id order by 1,2;

(3)结合上面的结果，将一个学生对应的两个权重成绩，合到一行，要求一条SQL语句实现，不能使用临时表

select tb1.student_id,weight_sum1,weight_sum2 from

--计算权重1

(select student_id,sum(t1.chinese*t2.chinese + t1.math*t2.math + t1.english*t2.english + t1.music*t2.music) weight_sum1 from scopes t1,weight t2 where weight_id=1 group by student_id) tb1

join

--计算权重2

(select student_id,sum(t1.chinese*t2.chinese + t1.math*t2.math + t1.english*t2.english + t1.music*t2.music) weight_sum2 from scopes t1,weight t2 where weight_id=2 group by student_id) tb2

--表连接

on tb1.student_id=tb2.student_id order by 1;

(4)按照两个权重成绩之和的大小，进行从大到小排序，且生成排序序号，要求生成连续排序序号，相同的值具有相同序号。一条SQL语句实现，不能使用临时表

select tb1.student_id,weight_sum1,dense_rank() over(order by weight_sum1 desc) weight_rank1,weight_sum2,dense_rank() over(order by weight_sum2 desc) weight_sum2 from

--计算权重1

(select student_id,sum(t1.chinese*t2.chinese + t1.math*t2.math + t1.english*t2.english + t1.music*t2.music) weight_sum1 from scopes t1,weight t2 where weight_id=1 group by student_id) tb1

join

--计算权重2

(select student_id,sum(t1.chinese*t2.chinese + t1.math*t2.math + t1.english*t2.english + t1.music*t2.music) weight_sum2 from scopes t1,weight t2 where weight_id=2 group by student_id) tb2

--表连接

on tb1.student_id=tb2.student_id;

--不要忘记把最后的order by 删除，最后的order by最后生效，会把dense_rank的排序更改

5.性能优化

当前有一张create table test(student_id int,class_id int,kemu varchar2(20),score int);

(1)查看202202班级里面语文最低分是多少，要保障走索引

--收集统计信息

analyze test;

--获取推荐索引

select *  from gs_index_advise('select min(grade) from test where kemu=''语文'' and class_id=''202202''');

--创建索引

create index index_ke_cls on test(kemu,class_id);

--查询202202班级里面语文最低分

select /*+ indexscan(index_ke_cls) */ min(grafe) from test where kemu='语文' and class_id='202202';

(2)查202202班级同一科目成绩比202201班级最高分的同学，根据以下SQL优化重写

原SQL：select * from test where score>(select max(score) from test where class_id=202201) and class_id = '202202';

select *  from test t1 join (select min(score) as min_score from test t2 where t1.class_id=202201) t3 on t1.score<t3.min_score where t1.class_id = 202202;

--原SQL中存在子查询，每扫描一次t1表，会遍历子查询结果，性能较差，改成join方式，消除子查询，性能有xxx提升至xxxms;

6.性能优化2

当前有三个表，分别是学生信息表student(sid,sname,cno)和202201班级成绩表score1(sid,course,score)，202202班级成绩表score2（同score1）

(1)查202201班级和202202班级所有人语文成绩前十的记录，第一个查询要使用union

(select * from score1 where course='chinese' order by score limit 10)

union

(select * from score2 where course='chinese' order by score limit 10);

(2)对以上SQL语句进行优化

--将union改为union all

(select * from score1 where course='chinese' order by score limit 10)

union all

(select * from score2 where course='chinese' order by score limit 10);

(3)查看两个班级相同的科目，202201班在202202班中不存在的成绩，要求使用not in

select * from score1 where score not in (select score from score2 where score1.course=score2.course);

(4)对以上SQL语句进行优化

--not in改为not exists

select * from score1 where not exists (select score from score2 where score1.score=score2.score and score1.course=score2.course);

(5)查询班级202201语文成绩最高的学生，要求先创建索引，并且能保证一定会使用索引

create index chi_index on score1(course);

select s1.* from student s1,(select sid,max(score) from score1 where course='chinese') s2 where s1.sid=s2.sid;

(6)查询202201班级的学生的成绩比202202班级的学生最高成绩还要大的学生信息，对以下给出的SQL进行改写

SQL：select stu.sid,stu.sname,sum(score) sumscore from student stu,score1 s1 where stu.sid=s1.sid group by stu.id,stu.sname having sumscore > (select max(score) from (select sum(score) score from score2 group by sid));

--先查询把所有所需字段全部获取，然后再根据已有字段判断

select stu.sno,stu.sname,sum(score) sumscore from studnet stu join score1 s1 on stu.sid=s1.sid cross join (select max(score) maxscore from (select sum(score) score from score2 group by id)) group by stu.sno,stu.sname,maxscore having sumscore>maxscore;

7.性能优化3

基于学生表(sno,sname,sno)，班级表(cno,cname)，课程表(courid,courname)，成绩表(sno,courid,score)完成关联查询

--创建表(考试时不一定有建表和数据插入代码)

create table student(sno varchar(20),sname varchar(50),cno int);

create table class(cno int,cname varchar(50));

create table course(courid int,courname varchar(50));

create table score(sno varchar(20),courid int,score int);

数据插入

insert into student values('1001','张三',1),('1002','李四',1),('1003','王五',2),('1004','赵六',2);

insert into class values(1,'1 班'),(2,'2 班');

insert into course values(1,'语文'),(2,'数学'),(3,'英语'),(4,'物理');

insert into score values('1001',1,84),('1001',1,64),('1001',2,86),('1001',2,94);

insert into score values('1001',3,76),('1001',3,56),('1001',4,48),('1001',4,84);

insert into score values('1002',1,83),('1002',1,85),('1002',2,46),('1002',2,74);

insert into score values('1002',3,65),('1002',3,76),('1002',4,56),('1002',4,98);

insert into score values('1003',1,86),('1003',1,74),('1003',2,88),('1003',2,54);

insert into score values('1003',3,86),('1003',3,76),('1003',4,67),('1003',4,76);

insert into score values('1004',1,100),('1004',1,100),('1004',2,87),('1004',2,86);

insert into score values('1004',3,69),('1004',3,67),('1004',4,84),('1004',4,92);

(1)语文平均成绩大于80的所有成绩，输出班级名，学号（或班级号），平均成绩，要求使用两个where非相关子查询

--in、where c1>(select ....)均属于非相关子查询

select t1.sno,cname,avg(score) from score t1,student t2,class t3 where t1.sno=t2.sno and t2.cno=t3.cno and t1.sno in (select sno from score where courid in (select courid from course where courname='语文') group by sno having avg(score)>80) and courid in (select courid from course where courname='语文') group by t1.sno,t3.cname;

(2)在上一题基础上，使用from查询优化

select t1.sno,cname,avgscore from (select t3.sno,t3.cno,avg(score) avgscore from score t1,course t2,student t3 where t1.sno=t3.sno and t1.courid=t2.courid and courname='语文' group by t3.sno,t3.cno having avgscore>80) t1,class t2 where t1.cno=t2.cno;

(3)在上一题基础上，使用父查询（消除子查询）

select t3.cno,t3.cname,avg(score) avgscore from score t1,student t2,class t3,course t4 where t1.sno=t2.sno and t2.cno=t3.cno and t1.courid=t4.courid and courname='语文' group by t1.sno,t3.cno,t3.cname having avgscore>80;

8.存储过程

当前有一张表stu(sno,math,art,physics,cno)

/*

create table stu(sno varchar(30),math float,art float,art float,physics float,cno int);

insert into stu values('1001',56,85,72,1);

insert into stu values('1002',55,35,32,1);

insert into stu values('1003',59,45,92,1);

insert into stu values('1004',73,66,65,1);

insert into stu values('1005',90,39,81,1);

*/

(1)查看每门学生成绩与每门平均成绩的差值

select sno,math,math-avgmath,art,art-avgart,physics,physics-avgphysics from stu,(select avg(math) avgmath,avg(art) avgart,avg(physics) avgphysics from stu);

(2)编写存储过程，输入学生id和科目名称输出对应的绩点值，0-59给0,60-69给0.1,70-79给0.2,80-89给0.3,90-100给0.4

create or replace procedure cla_point(id varchar(30),coursename varchar(30),point out float)

as

begin

case when coursename = 'math' then --当要查询的为数学成绩时

select (case when math <=59 the 0

when math <= 69 then 0.1

when math <= 79 then 0.2

when math <= 89 then 0.3

when math <=100 then 0.4

else 0.4 end) into point from stu where sno=id;

when

9.触发器

本题根据以下表完成相应触发器创建使用

--创建表

create table tab1(sname text,deptno int,salary float,title text);

create table dept(id int,dept_name text);

create table logger(sname text,dept_name text,log_date date);

创建触发器，要求在tab1表插入一行数据时，自动往logger表中插入一条记录，记录sname和部门名称，并用当天的日期来标注该行数据的生成时间。

(1)创建触发器函数T_INS_F

create or replace function T_INS_F() returns trigger as

$$

begin

insert into logger values(new.sname,(select dept_name from dept where id=new.deptno),sysdate);

return new;

end;

$$ language plpgsql;

(2)创建触发器 T_INS_TR

create trigger T_INS_TR after insert on tab1 for each row executor procedure T_INS_F();

(3)禁用表tab1上的所有触发器

alter table tab1 disable trigger all;

(4)删除T_INS_TR触发器

drop trigger T_INS_TR on tab1;

二、关键设计技巧总结

三、练习建议

1. 动手实践：

   • 在 GaussDB 实例中创建测试表（参考以下 DDL）：
     
     

     -- 创建员工表
     CREATE TABLE employees (
         emp_id SERIAL PRIMARY KEY,
         name VARCHAR(50),
         dept_id INT,
         salary NUMERIC(10,2)
     );
     
     -- 创建部门表
     CREATE TABLE departments (
         dept_id SERIAL PRIMARY KEY,
         dept_name VARCHAR(50)
     );

2. 进阶挑战：

   • 在题目 4 中，将 RANK() 改为 DENSE_RANK()，观察结果差异。
   • 在题目 5 中，用 DATE_TRUNC('month', order_date) 替代硬编码日期。

3. 避坑提示：

   • GaussDB 默认大小写不敏感（表名/列名自动转小写），建议统一用小写。
   • 避免使用 *，明确指定字段（如 e.name 而非 *）。