select id, count(phone) as phone_count from table order by count(phone) desc
declare @row int set @row = 3 select id, phone from yourtable where phone in (select phone from yourtable group by phone having count(phone) >= @row)
declare @tem int set @tem=3 select id,count(phone) from yourtalbe group by id having count(phone)>=@tem order by count(phone) desc
如果想取排名,可以这样:select IDENTITY(int, 1,1) AS seq, X.id, X.phone, X.phone_count from (select id, phone, count(phone) as phone_count from table_name group by id, phone order by count(phone) desc) X 这样排名就出来了。 比如想取排名第二的: select Y.id, Y.phone, Y.phone_count from (select IDENTITY(int, 1,1) AS seq, X.id, X.phone, X.phone_count from (select id, phone, count(phone) as phone_count from table_name group by id, phone order by count(phone) desc) X ) Y where Y.seq = 2
select phone from table group by phone having count(phone)>insert into # select IDENTITY(int, 1,1) AS seq,phone from (select phone from table group by phone order by count(phone) desc) a
只查号码: select phone from yourtable group by phone having count(phone) >= @row
select phone,count(phone) from table group by phone having count(phone)>=yournumber
select identity(int,1,1) as 排名, count(phone) as 次数 , phone as 号码 into #temp from 表名 group by phone order by count(phone) desc那么查询最多的: select 号码 from #temp where 排名=1 第二多的: select 号码 from #temp where 排名=2 。
select id from yourtable group by id
select id,count(id) from yourtable group by id order by id desc
select id from yourtable group by id
select count(phone) as phone_count from yourtable group by phone order by count(phone) desc
select phone,count(phone) from table group by phone having count(phone)>=yournumber
select [name],re from test group by [name],re having count([name]) >= 2 其中test為表名,name,re為字段名
from table
order by count(phone) desc
set @row = 3
select id, phone from yourtable where phone in
(select phone from yourtable group by phone having count(phone) >= @row)
set @tem=3
select id,count(phone)
from yourtalbe
group by id
having count(phone)>=@tem
order by count(phone) desc
from (select id, phone, count(phone) as phone_count
from table_name
group by id, phone
order by count(phone) desc) X
这样排名就出来了。
比如想取排名第二的:
select Y.id, Y.phone, Y.phone_count
from (select IDENTITY(int, 1,1) AS seq, X.id, X.phone, X.phone_count
from (select id, phone, count(phone) as phone_count
from table_name
group by id, phone
order by count(phone) desc) X ) Y
where Y.seq = 2
select IDENTITY(int, 1,1) AS seq,phone
from (select phone from table group by phone order by count(phone) desc) a
select phone from yourtable group by phone having count(phone) >= @row
from table
group by phone
having count(phone)>=yournumber
into #temp
from 表名 group by phone order by count(phone) desc那么查询最多的:
select 号码 from #temp where 排名=1
第二多的:
select 号码 from #temp where 排名=2
。
order by count(phone) desc
from table
group by phone
having count(phone)>=yournumber
其中test為表名,name,re為字段名