表结构和数据如下:
ID NAME IP LOGINTIME
1 CXD 192.168.1.1 2011-08-21 13:30:00.000
2 ZHANG 192.168.1.2 2011-08-21 14:30:00.000
3 YE 192.168.1.3 2011-08-21 14:31:00.000
4 YE 192.168.1.4 2011-08-21 14:31:23.000
5 CXD 192.168.1.1 2011-08-21 14:31:23.000
6 ZHANG 192.168.1.8 2011-08-21 14:31:23.000要求
同个NAME在一天内存在2个或超过2不同登陆ip记录
如何显示出来。显示的结果需要NAME,次数
ID NAME IP LOGINTIME
1 CXD 192.168.1.1 2011-08-21 13:30:00.000
2 ZHANG 192.168.1.2 2011-08-21 14:30:00.000
3 YE 192.168.1.3 2011-08-21 14:31:00.000
4 YE 192.168.1.4 2011-08-21 14:31:23.000
5 CXD 192.168.1.1 2011-08-21 14:31:23.000
6 ZHANG 192.168.1.8 2011-08-21 14:31:23.000要求
同个NAME在一天内存在2个或超过2不同登陆ip记录
如何显示出来。显示的结果需要NAME,次数
from tab
group by name
having count(distinct ip) > 1
order by count(distinct ip) desc
select name,count(distinct ip) from tb
group by name having count(distinct ip)>1
where exists(select 1 from tb
where name=a.name and convert(varchar(10),LOGINTIME,120)=convert(varchar(10),a.LOGINTIME,120)
and IP<>a.IP)
select name,row_number() over(partition by name,ip order by name,ip) nm
from tb
)select name,nm from t
where nm>=2
insert into tb
select 1,'CXD','192.168.1.1','2011-08-21 13:30:00.000' union
select 2,'ZHANG','192.168.1.2','2011-08-21 14:30:00.000' union
select 3,'YE','192.168.1.3','2011-08-21 14:31:00.000' union
select 4,'YE','192.168.1.4','2011-08-21 14:31:23.000' union
select 5,'CXD','192.168.1.1','2011-08-21 14:31:23.000' union
select 6,'ZHANG','192.168.1.8','2011-08-21 14:31:23.000' select * from tb a
where exists(select 1 from tb
where name=a.name and convert(varchar(10),LOGINTIME,120)=convert(varchar(10),a.LOGINTIME,120)
and IP<>a.IP)/*
id name ip logintime
----------- ---------- -------------------- -----------------------
2 ZHANG 192.168.1.2 2011-08-21 14:30:00.000
3 YE 192.168.1.3 2011-08-21 14:31:00.000
4 YE 192.168.1.4 2011-08-21 14:31:23.000
6 ZHANG 192.168.1.8 2011-08-21 14:31:23.000(4 行受影响)
*
from
tb t
where
exists(select 1 from tb where name=a.name and convert(varchar(10),LOGINTIME,120=convert(varchar(10),a.LOGINTIME,120) and IP<>a.IP)
或者时间不是一天,是范围呢?select * from tb a
where exists(select 1 from tb
where name=a.name and convert(varchar(10),LOGINTIME,120)=convert(varchar(10),a.LOGINTIME,120)
and IP<>a.IP)
这个语句要怎么改
id name ip logintime
----------- ---------- -------------------- -----------------------
2 ZHANG 192.168.1.2 2011-08-21 14:30:00.000
3 YE 192.168.1.3 2011-08-21 14:31:00.000
4 YE 192.168.1.4 2011-08-21 14:31:23.000
6 ZHANG 192.168.1.8 2011-08-21 14:31:23.000
但是是大于3次,或者4次
时间也不是一天,是范围
麻烦各位再帮帮忙
insert into #tb
select 1,'CXD','192.168.1.2','2011-08-21 13:30:00.000' union
select 2,'ZHANG','192.168.1.2','2011-08-21 14:30:00.000' union
select 3,'YE','192.168.1.3','2011-08-21 14:31:00.000' union
select 4,'YE','192.168.1.4','2011-08-21 14:31:23.000' union
select 5,'CXD','192.168.1.1','2011-08-21 14:31:23.000' union
select 6,'ZHANG','192.168.1.8','2011-08-21 14:31:23.000' union
select 1,'CXD','192.168.1.1','2011-08-27 13:30:00.000'
select* from #tb
select name,convert(varchar(10),logintime,23) as 日期,
SUM(case when logintime=logintime and ip=ip then 1 else 0 end) as 数量
from #tb
group by name,convert(varchar(10),logintime,23)