Home >Database >Mysql Tutorial >group by 在sqlserver与oracle中的差别
group by 在sqlserver与oracle中的差别
- WBOYOriginal
- 2016-06-07 15:10:041436browse
在sqlServer中group by 不能作为子句查询： 如：select * from (select deptno,count(*) counts from emp group by deptno) 上面在sqlserver中运行会报错，说明sqlserver中group by 不能作为子句 下面sql语句为查找人数最多的部门以及该部门号： select * fr
在sqlServer中group by 不能作为子句查询：
如：select * from (select deptno,count(*) counts from emp group by deptno)
上面在sqlserver中运行会报错，说明sqlserver中group by 不能作为子句
下面sql语句为查找人数最多的部门以及该部门号：
select * from (select deptno,count(*) counts from emp group by deptno) where counts = (select max(count) from (select deptno,count(*) count from emp group by deptno));
人数最多的专业在sqlserver中查询：
select classID,COUNT(*) from student group by classID having
COUNT(*)=(select top 1 COUNT(*) as 人数 from student group by classID order by COUNT(*))
or
COUNT(*)=(select top 1 COUNT(*) as 人数 from student group by classID order by COUNT(*) desc)