mysql - Compare a rows in a table for simularites and diffrences -


i have issue need block bots on site , need list of there account block

question answer this. same account using different ip address @ same time. need simple table of account numbers can copy , paste security

if account number appears 2 or more times under different ip address @ same time show me account number.

here example of table working with.

 table temp1  account          ip            last_used    14k4chc     79.110.19.199   2016-01-07 09:06:52 17ffhqy     79.110.19.199   2016-01-07 09:06:52 14k4chc     91.215.136.75   2016-01-07 09:06:52 17ffhqy     91.215.136.75   2016-01-07 09:06:52 15lessr     193.9.158.98    2016-01-07 09:06:51 

lines 1 thru 4 example of same 2 accounts using 2 different ip addresses @ same time spamming site , possible bot.

as starting point, identify different ip addresses same account , last_used time, use query this:

  select t.account        , t.last_used        , count(distinct t.ip) cnt_ip     temp1 t    group t.account, t.last_used   having count(distinct t.ip) > 1   order t.account, t.last_used 

this more rows want return, since returns distinct values of last_used.

the group by collapses rows have same values specified expressions. in case, account , last_used.

the count(distinct ) gets number of unique ip addresses each collapsed group. if of ip addresses given account , last_used values non-null , equal, return value of 1. include having clause filter out rows have same ip address.

this query starting point. used inline view, or enhanced.


Comments

Popular posts from this blog

Capture and play voice with Asterisk ARI -

java - Why database contraints in HSQLDB are only checked during a commit when using transactions in Hibernate? -

visual studio - Installing Packages through Nuget - "Central Directory corrupt" -