今天面试遇到一道sql问题,有三小题,前两题答出,第三题不会,特请教诸位
有表Student(StudentID,StudentName,classID,sex),其中,sex为1是男生,0是女生,表Class(classid,classname)其中classID与classid相关联,问,取出只有一个男生的班级名称
SQL code:
select m.classname from Class m , Student n where m.classid = n.classid and n.sex = 1 group by m.classname having count(1) = 1
SQL code:
select classname from Class where classid in
(
select classID from Student where sex=1 group by classID having count(sex)=1
)
SQL code:
select
b.classname
from
Student a
join
Class b
on
a.classid=b.classid
and
a.sex=1
group by
b.classname having count(1) = 1
SQL code:
create table Student(StudentID int,StudentName varchar(10),classID int,sex int)
insert into Student values(1 , 'A' , 1 , 1)
insert into Student values(6 , 'A' , 1 , 0)
insert into Student values(2 , 'B' , 2 , 1)
insert into Student values(3 , 'B' , 2 , 1)
insert into Student values(4 , 'C' , 3 , 0)
insert into S
