select t.classroom,(count(classroom)) countnum from (select * from test t where t.score>60) t group by t.classroom having count(classroom)>20
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));第二个语句我写的太麻烦了...不知道怎么简化下好,但和楼上那样用ROWNUM是不是会在有2个以上班级达到60分以上人数并列最多的时候不能全部查出来..
那就这样写 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
(select * from test t where t.score>60) t
group by t.classroom having count(classroom)>20
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 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));第二个语句我写的太麻烦了...不知道怎么简化下好,但和楼上那样用ROWNUM是不是会在有2个以上班级达到60分以上人数并列最多的时候不能全部查出来..
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