SQL查询--to 汪捷

use tempdb
go

create table tbl_test
(
 id int identity(1,1),
 ip varchar(50)
)
go

insert into tbl_test values('192.168.1.1')
insert into tbl_test values('192.168.1.1')
insert into tbl_test values('192.168.1.1')
insert into tbl_test values('192.168.1.1')
insert into tbl_test values('192.168.1.2')
insert into tbl_test values('192.168.1.3')
insert into tbl_test values('192.168.1.3')

select * from tbl_test

-- 查询记录中大于1条的所有ip和数量
select count(ip) as ip_count,ip from tbl_test group by ip having count(ip) >= 2

评论

1 楼 wenhai_zhang 2010-06-26  

-- 查询符合上述条件的总条数
select count(*) from
(select count(ip) as ip_count,ip from tbl_test group by ip having count(ip) >= 2) as new_table