如何统计一个同一天同一地址两个id登陆问题（类似马甲统计）

Question

如何能用sql 语句把两个id两次或两次以上同一天在用一ip登陆的记录给找出来------------------------------------------------------
现在表 t_login列如下
id,     riqi,                 ip     ,  bh
内容一下
aaa  2006-11-01 10:18:11      111       1
bbb  2006-11-01 10:50:12      111       2
ccc  2006-11-01 11:50:12      222       3
aaa  2006-12-01 16:18:11      999       4
bbb  2006-12-01 16:56:12      999       5
eee  2006-12-01 11:50:12      999       6
ddd  2006-12-01 11:50:12      888       7
-------------注：------------------------其中日期是字符型型的，格式为yyyymmddhhmmss(20061201115012 ),
为了便于大家看，上面我写成了2006-12-01 11:50:12格式---------问题1 --------------------
这其中，a,b两次同一天在用一ip登陆如何能用sql 语句把两个id两次或两次以上同一天在用一ip登陆的记录给找出来如
aaa  2006-11-01 10:18:11      111       1
bbb  2006-11-01 10:50:12      111       2
aaa  2006-12-01 16:18:11      999       4
bbb  2006-12-01 16:56:12      999       5
---------问题2 --------------------如何在任给一个id的情况下，找出有没有其他id两次以上同一天在用一ip和他一起登陆的记录给找出来如给aaa就找出aaa  2006-11-01 10:18:11      111       1
bbb  2006-11-01 10:50:12      111       2
aaa  2006-12-01 16:18:11      999       4
bbb  2006-12-01 16:56:12      999       5
如果给ccc或者ddd或eee，当然就没有马甲记录。-------------谢谢大家---------------------------------我自己写的语句都是用group by 几个列，然后几个列in这个记录集 来实现的，由于记录比较多，所以速度很慢希望大家能多多给出好的方法，不胜感激。

Answer 1

---------问题1 -------------------- 
这其中，a,b两次同一天在用一ip登陆 如何能用sql 语句把两个id两次或两次以上同一天在用一ip登陆的记录给找出来 如 id,    riqi,                ip    ,  bh 
aaa  2006-11-01 10:18:11      111      1 
bbb  2006-11-01 10:50:12      111      2 
aaa  2006-12-01 16:18:11      999      4 
bbb  2006-12-01 16:56:12      999      5 
--
select *
from t_login  as t
left join (select id,ip from t_login group by id,ip,left(riqi,8) having count(1) > 1) b
on t.id = b.id and t.ip = b.ip

Answer 2

------------------------------------
-- Author:  happyflystone  
-- Date:2008-12-02 21:34:31
-------------------------------------- Test Data: T_LOGIN
IF OBJECT_ID('T_LOGIN') IS NOT NULL 
    DROP TABLE T_LOGIN
Go
CREATE TABLE T_LOGIN(id NVARCHAR(3),riqi DATETIME,ip INT,bh INT)
Go
INSERT INTO T_LOGIN
 SELECT 'aaa','2006-11-01 10:18:11',111,1 UNION ALL
 SELECT 'bbb','2006-11-01 10:50:12',111,2 UNION ALL
 SELECT 'ccc','2006-11-01 11:50:12',222,3 UNION ALL
 SELECT 'aaa','2006-12-01 16:18:11',999,4 UNION ALL
 SELECT 'bbb','2006-12-01 16:56:12',999,5 UNION ALL
 SELECT 'eee','2006-12-01 11:50:12',999,6 UNION ALL
 SELECT 'ddd','2006-12-01 11:50:12',888,7 
GO
--Start
select t.* 
from t_login  as t 
right join (select ip,convert(char(10),riqi,120) as dt from t_login group by ip,convert(char(10),riqi,120) having count(1) > 1) b 
on t.ip = b.ip and dt = convert(char(10),t.riqi,120)
--Result:
/*
id   riqi                    ip          bh
---- ----------------------- ----------- -----------
aaa  2006-11-01 10:18:11.000 111         1
bbb  2006-11-01 10:50:12.000 111         2
aaa  2006-12-01 16:18:11.000 999         4
bbb  2006-12-01 16:56:12.000 999         5
eee  2006-12-01 11:50:12.000 999         6(5 行受影响)*/
--End 

Answer 3

非常感谢 happyflystone还有一点问题查询结果只需要
id   riqi                    ip          bh
---- ----------------------- ----------- -----------
aaa  2006-11-01 10:18:11.000 111         1
bbb  2006-11-01 10:50:12.000 111         2
aaa  2006-12-01 16:18:11.000 999         4
bbb  2006-12-01 16:56:12.000 999         5
不需要
eee  2006-12-01 11:50:12.000 999         6

Answer 4

--> --> (Andy)生成测试数据 2008-12-02
Set Nocount On
declare @1 table([id] nvarchar(50),[riqi] Datetime,[ip] int,[bh] int)
Insert @1
select N'aaa','2006-11-01 10:18:11',111,1 union all
select N'bbb','2006-11-01 10:50:12',111,2 union all
select N'ccc','2006-11-01 11:50:12',222,3 union all
select N'aaa','2006-12-01 16:18:11',999,4 union all
select N'bbb','2006-12-01 16:56:12',999,5 union all
select N'eee','2006-12-01 11:50:12',999,6 union all
select N'ddd','2006-12-01 11:50:12',888,7Declare @id nvarchar(50)
Set @id='aaa'
Select * 
From @1 As a 
Where  Exists(Select 1 From @1 Where id=@id And ip=a.ip )
And Exists(Select 1 From @1 Where id=a.id And riqi<>a.riqi)/*
id riqi ip bh
------------------------------------
aaa 2006-11-01 10:18:11.000 111 1
bbb 2006-11-01 10:50:12.000 111 2
aaa 2006-12-01 16:18:11.000 999 4
bbb 2006-12-01 16:56:12.000 999 5
*/

Answer 5

select d.* 
from t_login d
join (
select ','+ltrim(a.bh)+','+ ltrim( b.bh)+',' as col
from t_login a
left join t_login b 
on a.id = b.id and convert(char(10),a.riqi,120)> convert(char(10),b.riqi,120)
where b.bh is not null) c
on charindex(','+ltrim(d.bh)+',',col) > 0
--Result:
/*
id   riqi                    ip          bh
---- ----------------------- ----------- -----------
aaa  2006-11-01 10:18:11.000 111         1
aaa  2006-12-01 16:18:11.000 999         4
bbb  2006-11-01 10:50:12.000 111         2
bbb  2006-12-01 16:56:12.000 999         5(4 行受影响)
*/
--End 

Answer 6

create table tb (id varchar(10),riqi Datetime,ip int,bh int)
Insert tb
select 'aaa','2006-11-01 10:18:11',111,1 union all
select 'bbb','2006-11-01 10:50:12',111,2 union all
select 'ccc','2006-11-01 11:50:12',222,3 union all
select 'aaa','2006-12-01 16:18:11',999,4 union all
select 'bbb','2006-12-01 16:56:12',999,5 union all
select 'eee','2006-12-01 11:50:12',999,6 union all
select 'ddd','2006-12-01 11:50:12',888,7
---------问题1 -------------------- 
select * from tb t1 , 
(
  select id from tb m where exists (select 1 from 
  (select convert(varchar(10) , riqi , 120) riqi , ip from tb group by convert(varchar(10) , riqi , 120) , ip having count(*) > 1) n
  where n.riqi = convert(varchar(10) , m.riqi , 120) and n.ip = m.ip)
  group by id having count(*) > 1
) t2
where t1.id = t2.id
/*
id         riqi                                                   ip          bh          id         
---------- ------------------------------------------------------ ----------- ----------- ---------- 
aaa        2006-11-01 10:18:11.000                                111         1           aaa
aaa        2006-12-01 16:18:11.000                                999         4           aaa
bbb        2006-11-01 10:50:12.000                                111         2           bbb
bbb        2006-12-01 16:56:12.000                                999         5           bbb（所影响的行数为 4 行）
*/drop table tb

Answer 7

create table tb (id varchar(10),riqi Datetime,ip int,bh int)
Insert tb
select 'aaa','2006-11-01 10:18:11',111,1 union all
select 'bbb','2006-11-01 10:50:12',111,2 union all
select 'ccc','2006-11-01 11:50:12',222,3 union all
select 'aaa','2006-12-01 16:18:11',999,4 union all
select 'bbb','2006-12-01 16:56:12',999,5 union all
select 'eee','2006-12-01 11:50:12',999,6 union all
select 'ddd','2006-12-01 11:50:12',888,7---------问题1 -------------------- 
select * from tb t1 , 
(
  select id from tb m where exists (select 1 from 
  (select convert(varchar(10) , riqi , 120) riqi , ip from tb group by convert(varchar(10) , riqi , 120) , ip having count(*) > 1) n
  where n.riqi = convert(varchar(10) , m.riqi , 120) and n.ip = m.ip)
  group by id having count(*) > 1
) t2
where t1.id = t2.id
/*
id         riqi                                                   ip          bh          id         
---------- ------------------------------------------------------ ----------- ----------- ---------- 
aaa        2006-11-01 10:18:11.000                                111         1           aaa
aaa        2006-12-01 16:18:11.000                                999         4           aaa
bbb        2006-11-01 10:50:12.000                                111         2           bbb
bbb        2006-12-01 16:56:12.000                                999         5           bbb（所影响的行数为 4 行）
*/---------问题2 -------------------- 
declare @id as varchar(10)
set @id = 'aaa'select * from tb t1 ,
(
  select id from tb m where exists (select 1 from 
  (select convert(varchar(10) , riqi , 120) riqi , ip from tb group by convert(varchar(10) , riqi , 120) , ip having count(*) > 1) n
  where n.riqi = convert(varchar(10) , m.riqi , 120) and n.ip = m.ip)
  group by id having count(*) > 1
) t2 
where t1.id = t2.id and exists (select 1 from 
(
  select id from tb m where exists (select 1 from 
  (select convert(varchar(10) , riqi , 120) riqi , ip from tb group by convert(varchar(10) , riqi , 120) , ip having count(*) > 1) n
  where n.riqi = convert(varchar(10) , m.riqi , 120) and n.ip = m.ip)
  group by id having count(*) > 1
) t where id = @id
)
/*
id         riqi                                                   ip          bh          id         
---------- ------------------------------------------------------ ----------- ----------- ---------- 
aaa        2006-11-01 10:18:11.000                                111         1           aaa
aaa        2006-12-01 16:18:11.000                                999         4           aaa
bbb        2006-11-01 10:50:12.000                                111         2           bbb
bbb        2006-12-01 16:56:12.000                                999         5           bbb（所影响的行数为 4 行）
*/set @id = 'ccc'select * from tb t1 ,
(
  select id from tb m where exists (select 1 from 
  (select convert(varchar(10) , riqi , 120) riqi , ip from tb group by convert(varchar(10) , riqi , 120) , ip having count(*) > 1) n
  where n.riqi = convert(varchar(10) , m.riqi , 120) and n.ip = m.ip)
  group by id having count(*) > 1
) t2 
where t1.id = t2.id and exists (select 1 from 
(
  select id from tb m where exists (select 1 from 
  (select convert(varchar(10) , riqi , 120) riqi , ip from tb group by convert(varchar(10) , riqi , 120) , ip having count(*) > 1) n
  where n.riqi = convert(varchar(10) , m.riqi , 120) and n.ip = m.ip)
  group by id having count(*) > 1
) t where id = @id
)
/*
id         riqi                                                   ip          bh          id         
---------- ------------------------------------------------------ ----------- ----------- ---------- （所影响的行数为 0 行）
*/drop table tb