关于查询--例子：查询男生最多的班级

Oracle数据库中的写法：

 

1. 利用子查询

select max(a.mailCount) from (select count(b.id) mailCount,basic_organ_id from t_card_basic b 
where b.basic_sex = '男' 
group by b.basic_organ_id) a;

2. 利用Oracle的特色rownum

select basic_organ_id, a, rownum from (select basic_organ_id,count(*) as a
from t_card_basic 
where basic_sex = '男'
group by basic_organ_id 
order by a desc)
where rownum = 1

 SQL Server 数据库中的写法：

select top 1 ClassID,count(*) as a
from Student 
where sex = 1 
group by ClassID  
order by a desc