一道还算有技术含量的SQL面试题

 

曾曰：你有一个苹果，我有一个苹果，交换之后还是一个苹果；你有一个想法，我有一个想法，交换之后会有N个想法~~

昨天面试，碰到了一道还算有技术含量的面试题，现在跟大家分享如下：

给定如下的student表：

学号	姓名	成绩	班级
ID	name	result	calssID

请用SQL语句完成如下查询：

①，求出各班的及格率（成绩>=60)

②，求出个班的优秀率（成绩>=80)

呵呵，就是如此一道题，说这道题不错是因为考察了的确有点偏的一个点，说他蛋疼是因为及格率和优秀率你换个数不就成了啊，真是的~~

此题解决方案如下：

首先建立一个student表，以验证结果是否正确，脚本如下：

if OBJECT_ID('student','u') is not null
drop table student;
go
create table student
(
ID int identity(1,1) primary key,
name varchar(20),
result int,
classID int
)
go
insert into student(name, result, classID) values('Angle1',34,1)
insert into student(name, result, classID) values('Angle2',65,1)
insert into student(name, result, classID) values('Angle3',97,1)
insert into student(name, result, classID) values('Angle4',89,1)
insert into student(name, result, classID) values('Angle5',78,1)
insert into student(name, result, classID) values('Angle6',34,2)
insert into student(name, result, classID) values('Angle7',76,2)
insert into student(name, result, classID) values('Angle8',69,2)
insert into student(name, result, classID) values('Angle9',89,2)
insert into student(name, result, classID) values('Angle10',67,3)
insert into student(name, result, classID) values('Angle11',67,3)
go

如此建好表并插入了若干数据，查询方案如下：

select A.a '及格人数' , B.a as '总人数', CAST(convert(float,A.a)/convert(float,B.a) as decimal(5,3)) as '及格率' from
(select COUNT(*) as a ,classID as ID from student where result >= 60 group by classID) as A,
(select COUNT(*) as a ,classID as ID from student group by classID) as B
where A.ID = B.ID
go

主要思路是首先临时建立两个表，分别查询出及格人数和总人数，即表A和表B，分别有两列a，ID，分别表示人数和班级编号，然后将两个表进行关联查询即可
如上查询的时候，查询及格率是没问题的，因为每个班都有几个的，结果如下：

但是如果此时查询优秀人数，就有问题了，因为有的班级没有80分以上的，会直接将其忽略，结果如下：

即有的班级虽然没有人获得优秀，但是也要显示出来，要不数据不完整，此时可以考虑左右连接的问题了，修改查询方案如下：

select  isnull(A.a,0) '优秀人数' , isnull(B.a,0) as '总人数', ISNULL( CAST(convert(float,A.a)/convert(float,B.a) as decimal(5,3)),0) as '优秀率' from
(select COUNT(*) as a ,classID as ID from student where result >= 80 group by classID)
as A
right join 
(select COUNT(*) as a ,classID as ID from student group by classID) as B
on A.ID = B.ID
go

使用左连接还是右连接，跟表的位置有关，此处还是用了ISNULL()函数，即如果查询的数据为空，将其直接转为某个值，在此将其转为0，查询结果如下：

OK，以上就算是对这道题的所有思路，或许此方法不是最好的，希望大家多交流，呵呵~