软通的面试题

info表，：classId,userId,score

 

1.找出每个班级里分数最高的人  

2.找出平均分在60分以上的班级  并且按平均分排序

3.找出分数超过80分的人大于20人的班级

 

原题出处：

http://www.javaeye.com/topic/459851?page=1

 

数据模型以SQLServer2000为例

 

declare @temp table(
 classId varchar(5),
 userId varchar(10),
 score tinyint
)

INSERT INTO @temp VALUES('1','001',70)
INSERT INTO @temp VALUES('1','002',50)
INSERT INTO @temp VALUES('1','003',90)
INSERT INTO @temp VALUES('2','001',100)
INSERT INTO @temp VALUES('2','002',90)
INSERT INTO @temp VALUES('3','001',90)
INSERT INTO @temp VALUES('3','002',100)
INSERT INTO @temp VALUES('3','003',150)

 

 

分析（1）：

 ======条件：班级分组查询、分数高无法直接得到人名，最后查询条件变成两个不确定值，则必须使用联合查询。

 

SELECT t1.classId,t1.userId,t1.score FROM @temp t1,(SELECT classId,MAX(score) AS score FROM @temp GROUP BY classId) t2
WHERE t1.classId=t2.classId AND t1.score=t2.score

 

分析（2）

======条件：班级分组、求平均数可以直接查询得出班级名。

SELECT classId,AVG(score) AS score FROM @temp GROUP BY classId ORDER BY AVG(score) DESC

 

分析（3）

======条件：班级分组、人数大于20、分数超过80分的人，无法直接得到。但是该查询符合子查询条件，
因为其中两个重要条件（只有一个列为动态值），一个可以从条件中直接得到，一个则可以从子查询当中得到。

a：（找出班的总人数大于20，并且分数超过80分的人）

SELECT * FROM @temp WHERE score>80 AND classId IN
(SELECT classId FROM @temp GROUP BY classId HAVING COUNT(userId)>20)

 

b：（找出班级分数大于80，且该人数总和超过20的人）

SELECT * FROM @temp WHERE score>80 AND classId IN
(SELECT classId FROM @temp WHERE score>80 GROUP BY classId HAVING COUNT(userId)>20)