oracle：平均分大于78的女同学的学号、姓名、平均分

古城微笑少年丶 2022-04-24 01:04 208阅读 0赞

create table student(
      sno varchar(10) primary key,
      sname varchar(10),
      ssex varchar(4)
    );
    insert into student values('001','赵','f');
    insert into student values('002','钱','m');
    insert into student values('003','孙','f');
    insert into student values('004','李','m');
    insert into student values('005','周','f');
    
    insert into student values('006','赵','f');
    
    create table course(
      cno varchar(10) primary key,
      cname varchar(30),
      ccredit int 
    );
    insert into course values('c01','sqlserver',1);
    insert into course values('c02','oracle',2);
    insert into course values('c03','java',3);
    insert into course values('c04','c语言',4);
    insert into course values('c05','软件工程',5);
    create table sc(
      sno varchar(10),
      cno varchar(10),
      grade int,
      primary key (sno,cno),
      foreign key (sno) references student(sno),
      foreign key (cno) references course(cno)
    );
    insert into sc values('001','c01',80);
    insert into sc values('001','c02',85);
    insert into sc values('001','c02',60);
    insert into sc values('001','c03',70);
    insert into sc values('001','c04',81);
    insert into sc values('001','c05',82);
    insert into sc values('002','c01',83);
    insert into sc values('002','c02',84);
    insert into sc values('003','c03',85);
    insert into sc values('003','c05',86);
    insert into sc values('004','c04',87);
    insert into sc values('004','c05',88);
    insert into sc values('005','c02',89);
    insert into sc values('005','c04',100);
    insert into sc values('005','c05',40);
    insert into sc values('006','c05',80);
    commit;
    
    --平均分大于78的女同学的学号、姓名、平均分
    --方法1：
    select sc.sno,(select sname from student a where a.sno = sc.sno) sname,avg(grade) from sc,student
    where sc.sno = student.sno and ssex='f'
    group by sc.sno
    having avg(grade)>78
    order by sc.sno;
    --方法2：
    select sc.sno,sname,avg(grade) from sc,student
    where sc.sno = student.sno and ssex = 'f'
    group by sc.sno,sname
    having avg(grade)>78

结果：

![20190411141602896.png][]

描述：

1）数据库：oracle

2）方法一的坑：ssex='f'必须放在最外层的条件中，如果放再内层的条件中，选出的sname为列，如果没有符合条件的，则会出现sname为null的情况。

3）方法二的坑：通常计算平均成绩，就会想到group by sc.sno,但是查询的时候，又要展示sname,该列不在分组中，所以很容易报错，最简单的办法就是把sname加入到分组。那有的人就会问，如果有姓名相同的人，会不会分到一个组，答案肯定不会，因为先按照sc.sno分组了，所以，即使有名字相同的，又同时满足条件，由于学号不同，还是会被查询出来（如结果所示）。

[20190411141602896.png]: /images/20220221/07bf327b9c414c28bb0e99552a974e9c.png