MySQL查询算法-我不知所措 [英] MySQL Query Algorithm - I am overwhelmed

问题描述

我有2个不同的表. 我的目标是找到使用相同IP地址但名称和姓氏不同的人.

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

我想要得到的结果

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.

推荐答案

欢迎使用S/O，您有一个很好的问题，混淆了几个问题，但是您确实给出了一个很好的示例，即如果您不想使用IP，相同的名字/姓氏.为了获得您想要的东西，您需要自我联接到相同的日志表，但也联接到相同的成员表，因此您首先要具有在IP上进行匹配的能力，然后还要比较每个人的姓名/姓氏，以便如果需要，可以将它们排除在外.

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.

这并不完美，但这确实可以从示例数据中获得所需的信息，而无需专门过滤一个IP地址.

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

我分别将l1和l2用作日志"别名，将m1和m2用作成员"别名.因此，我从l1到m1开始使用相同的成员ID.然后根据IP地址从第一个日志表到第二个日志表，并且必须是不同的成员ID(为什么要用相同的成员ID比较同一个人).现在，我可以通过其成员ID加入l2和m2，如果m1和m2名称匹配，则排除它，因为在名称和姓氏上符合！=.

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.

这篇关于MySQL查询算法-我不知所措的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！