mysql SELECT EXISTS 存在于多个表上

Question

有表:person,person_ip

两个表都有 pid 列作为主键，表 person 中有列 state_id，表 person_ip 中有列 state_id code> 有列 ip。

想要发现指定的 IP 地址是否分配给 state_id 不等于 2 的人。但总是得到结果 1，即使 state_id 是 0、1 或 2 。仅当 IP 地址根本未列出时，始终得到 0。我做错了什么？

SELECT EXISTS (
    SELECT person_ip.PID
    FROM person_ip,person
    WHERE person.PID=person_ip.PID
        AND person.state_id NOT IN (2)
        AND person_ip.ip='10.11.12.13'
)

Answer 1

这似乎是一个简单的加入..除非我错过了一些东西

select person.*
  from person
    inner join person_ip
      on person.pid = person_ip.pid
  where person.state_id <> 2
    and person_ip.ip_address = '10.0.0.1'

如果您想要排除 ip_address(如果该 ip_address 已分配给任何状态 = 2 的用户)，即使它也已分配给状态不为 2 的用户，请尝试:

select max(i)
  from (
    select *
      from (
        select 1 as i
          from dual
          where not exists (
            select 1
              from person p
                inner join person_ip pi
                  on p.pid = pi.pid
              where p.state_id = 2
                and pi.ip_address = '10.0.0.1'
          )
      ) q
    union 
    select 0
  ) qq

(dual是一个系统表，可以用作一种 stub 表)

here's a fiddle showing both versions

实际 sleep 后更新

好的，所以上面的查询有点.. 在那里。回到现实世界，这个可能更合适:

  select count(case when p1.state_id = 2 then 1 end) 
    from person p1
      inner join person_ip pi1
        on p1.pid = pi1.pid
    where pi1.ip_address = '10.0.0.1'
    group by pi1.ip_address;

如果您的 ip_address 已被 state_id 为 2 的人使用过，则返回 1 或更多；如果从未被 state_id 为 2 的人使用过，则返回 0。

如果该ip从未被使用过，则不会返回任何内容。

this fiddle具有上述所有三个查询。