楼上的基本正确,但是如果最大的phone数有重复就不对了。 下面的经过测试保证正确:select phone from t1 where state = 0 group by phone having count(phone) =( select max(num) from ( select phone,count(state) num from t1 where state=0 group by phone ) )
select phone ,max(state_count) from ( select phone,count(state) stste_count from .... where state=0 group by phone ) 内部select先把表中按phone分组,同时找到state=0的个数。外部select找到这里stat=0个数最大的记录。 对不对????????
UP 另外,leehuashi的答案不对,因为其答案跟以下sql是一致的: select phone,count(state) state_count from .... where state=0 group by phone
select phone,count(state) num
from ....
where state=0
group by phone
)
select phone,count(state) num
from ....
where state=0
group by phone
order by 2 desc
)
where rownum < 2
有两个字段,phone,state
我现在想得到state=0最多的phone号
下面的经过测试保证正确:select phone
from t1
where state = 0
group by phone
having count(phone) =(
select max(num)
from (
select phone,count(state) num
from t1
where state=0
group by phone
)
)
我想得到指定的次数的应改动什么?
也就是state=0 的,我想查出指定个数的!
比如一个phone的state值有3个等于0的!
select phone,count(state) stste_count
from ....
where state=0
group by phone
)
内部select先把表中按phone分组,同时找到state=0的个数。外部select找到这里stat=0个数最大的记录。
对不对????????
另外,leehuashi的答案不对,因为其答案跟以下sql是一致的:
select phone,count(state) state_count
from ....
where state=0
group by phone