QUOTE:

---

原帖由 imake 于 2008-6-30 12:05 发表
为什么不能这样写呢？
select company,count(employee)
from  works
group by company
having count(employee) = max(count(employee))

---

因为学会了over，就忘了having

select company,max((emp_num))
from works w,(select company,count(*) emp_num from works group by company ) s
where w.company=s.company;

QUOTE:

---

原帖由 imake 于 2008-6-30 12:05 发表
为什么不能这样写呢？
select company,count(employee)
from  works
group by company
having count(employee) = max(count(employee))

---

这样写在oracle上报-00935错误，分组函数的嵌套太深

select company,max(cnt) from
(select company,count(employee) cnt from works group by company)
group by companny

LS的好用，呵呵

QUOTE:

---

原帖由 tongwenbin 于 2008-7-1 11:01 发表
select company,max(cnt) from
(select company,count(employee) cnt from works group by company)
group by companny

---

谢谢你的建议，我试过了，要求是只显示一条记录，这样还是多条的。

mysql的东西怎么跑到oracle来的

只解决oracle下的：
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
Connected as lxj

SQL> select * from test;

COMP              QTY
---------- ----------
comp_a              1
comp_a              2
comp_a              3
comp_b              1
comp_b              2

SQL> select max(comp)keep(dense_rank first order by cnt desc),max(cnt) from (select comp,count(1) cnt from test group by comp) a;

MAX(COMP)KEEP(DENSE_RANKFIRSTO   MAX(CNT)
------------------------------ ----------
comp_a                                  3

SQL>

小马哥的sql跟6楼的一样有个小问题，就是对于几个公司人数刚好都一样多，谁也不是最多的时候，只能查到一个。

那就只能用dense_rank 了