数据库查询面试题
table：   test
field:   student,score(考试分数)，classroom(班级)
1，请查出得分超过60分的学生有20个以上的   班级
2，得分在60分以上的学生最到的班级



------解决方案--------------------
select classroom,count(*) from test
where score> 60
group by classroom
having count(*)> 20


select * from (select classroom,count(*) from test
where score> 60
group by classroom
order by count(*) desc)
where rownum=1
------解决方案--------------------
select classroom from
(select student,score,classroom from test where score> 60)
group by classroom having count(student)> =20;


select classroom,class from(
select classroom,count(classroom) class from
(select student,score,classroom from test where score> 60)
group by classroom) where class in(
select max(class) from(
select classroom,count(classroom) class from
(select student,score,classroom from test where score> 60)
group by classroom));

第二个语句我写的太麻烦了．．．不知道怎么简化下好，但和楼上那样用ＲＯＷＮＵＭ是不是会在有２个以上班级达到６０分以上人数并列最多的时候不能全部查出来．．
------解决方案--------------------
那就这样写
select * from (select a.belongorg,count(*),rank() over (order by count(*) desc) as a
from user_info a
group by belongorg
order by count(*) desc)
where a=1
------解决方案--------------------
呵呵
------解决方案--------------------
哈哈,都是牛人。.