mysql 查询算法,MySQL查询算法-我不知所措

I have 2 different tables.

My goal is to find people who use the same ip address with different (name and surname).

Table 2 - members

id name surname

87 john but

88 john but

89 alex lopez

90 david beckham

Table 1 - logs

member_id ip_adress

87 1.1.1.1

88 1.1.1.1

89 2.2.2.2

90 2.2.2.2

The result I want to get

ip_address members count

2.2.2.2 89,90 total (2 )

I have been trying for days, we could not solve.

As a result, I need to get the above output.

解决方案

Welcome to S/O, and you have a good question, confusing several, but you did give a good example that you did not want an IP if it was the same name / surname. In order to get what you are looking for, you need to self-join to the same logs table, but also the same members tables so you have the capacity of matching on IP first, then also comparing the name/surname of each person so they can be excluded if need be.

Its not perfect, but this does get what you are looking for from the sample data without specifically filtering on one IP address.

select

l1.ip_address,

group_concat(distinct l1.member_id order by l1.member_id ) UniqueMembers,

count(*) NumberOfMembers

from

logs l1

JOIN members m1

on l1.member_id = m1.id

join logs l2

on l1.ip_address = l2.ip_address

AND l1.member_id != l2.member_id

join members m2

on l2.member_id = m2.id

AND m1.name != m2.name

AND m1.surname != m2.surname

group by

l1.ip_address

I am using l1 and l2 for the respective "Logs" aliases and m1 and m2 for "members". So I am starting on the same member ID from l1 to m1. Then from first log table to second based on the IP address and must be a different member ID (why compare same person by same member ID). Now I can join l2 and m2 by its member ID and exclude it if the m1 and m2 names match because qualifying for != on the name and surname.

Should get you much closer to what you are looking for.