数据库查询面试题
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
[解决办法]
呵呵
[解决办法]
哈哈,都是牛人。.
