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
结果:
描述:
1)数据库:oracle
2)方法一的坑:ssex='f'必须放在最外层的条件中,如果放再内层的条件中,选出的sname为列,如果没有符合条件的,则会出现sname为null的情况。
3)方法二的坑:通常计算平均成绩,就会想到group by sc.sno,但是查询的时候,又要展示sname,该列不在分组中,所以很容易报错,最简单的办法就是把sname加入到分组。那有的人就会问,如果有姓名相同的人,会不会分到一个组,答案肯定不会,因为先按照sc.sno分组了,所以,即使有名字相同的,又同时满足条件,由于学号不同,还是会被查询出来(如结果所示)。