SQL 小号检测

题目来源：zhuanlan.zhihu.com/p/79366919

生成数据

create table log
(
  uid char(10),
  ip char(15),
  time timestamp
);

insert into log values
insert into log values
('a', '124', '2019-08-07 12:0:0'),
('a', '124', '2019-08-07 13:0:0'),
('b', '124', '2019-08-08 12:0:0'),
('c', '124', '2019-08-09 12:0:0'),
('a', '174', '2019-08-10 12:0:0'),
('b', '174', '2019-08-11 12:0:0'),
('a', '194', '2019-08-12 12:0:0'),
('b', '194', '2019-08-13 13:0:0'),
('c', '174', '2019-08-14 12:0:0'),
('c', '194', '2019-08-15 12:0:0')
;

sql

select uid_1, uid_2
from (
  select 
    t1.uid as uid_1, t1.ip, t2.uid as uid_2
  from 
    (select uid, ip from log group by uid, ip) t1 join
    (select uid, ip from log group by uid, ip) t2 
  on
    t1.uid < t2.uid and
    t1.ip = t2.ip
) t
group by uid_1, uid_2
having count(ip)>=3;