软通外派华为面试题

--找出每个班级里分数最高的人
select max(score) as '每个班级最高分'
from class
group by classid

--找出平均分在60分以上的班级  并且按平均分排序
select avg(cast(score as int)) as '平均分在60分以上的班级'
from class
group by classid
having convert(int,avg(cast(score as int))) >=60
order by avg(cast(score as int)) desc


--找出分数超过80分的人大于20人的班级
select classid as '分数超过80分的人大于20人的班级'
from class
where score > 80
group by classid
having count(userid) >20

第一个SQL题目是找人，不是找分，
下面SQL在Oracle下测试成功

	--1.找出每个班级里分数最高的人
	select t1.classid,t1.userid,t1.score from z_test t1,
	( select classid, max(score) as score from z_test group by classid ) t2
	where 
	t1.score = t2.score
	and t1.classid = t2.classid	
	
	--2.找出平均分在60分以上的班级  并且按平均分排序
	select * from (
	select classid,avg(score) as avgScore from z_test group by classid 
	) t1 where t1.avgScore>60 order by avgScore desc

	--3.找出分数超过80分的人大于20人的班级
	select * from (
	select classid,count(*) as score80  from z_test where score>80 group by classid
	) t1 where t1.score80>20 order by score80 desc

不好意思！失误

华为的题也不太难啊

跟文思创新一个样

堆排序恩恩

第一题可以先排序，再折半，半段逆循环，前段每隔一个插一个进去

关于数据库的，刚在机器上测了下
1、
SELECT t1.userId
FROM info t1 INNER JOIN
          (SELECT classID, MAX(score) AS mscore
         FROM info
         GROUP BY classID) t2 ON t1.classID = t2.classID AND t1.score = t2.mscore
2、
SELECT t1.classID
FROM info t1 INNER JOIN
          (SELECT classID, AVG(score) AS ascore
         FROM info
         GROUP BY classID) t2 ON t1.classID = t2.classID
WHERE (t2.ascore >= 60)
GROUP BY t1.classID
3、
SELECT classID, num
FROM (SELECT classID, COUNT(classId) AS num
        FROM info
        WHERE score >= 80
        GROUP BY classID) t2
WHERE (num >= 20)

有意思么，外包的面试题和华为正式招聘的要求相差天上地下