面试常考的 group by +having 试题

net更改
one
suggest




sql


学生
id
name




create table sd (id int primary key ,name varchar(22));
insert into sd values(1,'a');
insert into sd values(2,'b');
insert into sd values(3,'c');
课程
id
name


sid
chengji


create table course (id int primary key ,name varchar(22),sid int,chengji int);


insert into course values(1,'ca',1,99);
insert into course values(2,'cb',1,98);
insert into course values(3,'cc',1,22);


insert into course values(4,'cb',2,18);
insert into course values(5,'cc',2,82);


1 选了2们以上的学号


select s.id from sd s,course c where c.sid = s.id group by s.id having count(*) >=2 order by c.sid desc;
select s.id from sd s left join course c on c.sid = s.id group by s.id having count(*) >=2 order by c.sid desc;


2 总成绩大于150的学号
select s.id from sd s,course c where c.sid=s.id group by s.id having sum(c.chengji) >50;