mysql - 根据兄弟属性统计 SQL 记录

Question

我有一张人表，其中包含姓名、位置(他们居住的地方)和 parent_id( parent 存储在另一张 table 上)。例如:

name    | location  | parent_id
--------+-----------+-----------
Joe     | Chicago   | 12
Sammy   | Chicago   | 13
Bob     | SF        | 13
Jim     | New York  | 13
Jane    | Chicago   | 14
Dave    | Portland  | 14
Al      | Chicago   | 15
Monica  | Boston    | 15
Debbie  | New York  | 15
Bill    | Chicago   | 16
Bruce   | New York  | 16

我需要统计有多少人住在芝加哥并且拥有住在纽约的 sibling (共享一个 parent_id)。所以对于上面的例子，计数为 3。

name    | location  | parent_id
--------+-----------+-----------
Joe     | Chicago   | 12
Sammy   | Chicago   | 13   * sibling Jim lives in New York
Bob     | SF        | 13
Jim     | New York  | 13
Jane    | Chicago   | 14
Dave    | Portland  | 14
Al      | Chicago   | 15   * sibling Debbie lives in New York
Monica  | Boston    | 15
Debbie  | New York  | 15
Bill    | Chicago   | 16   * sibling Bruce lives in New York
Bruce   | New York  | 16

有人可以帮我写SQL查询这个计数吗？

Answer 1

看起来 Minh 的回答很有效，但这是另一个使用自连接的示例。

SELECT Count(DISTINCT a.child_id)
FROM people a
    JOIN people b ON a.parent_id = b.parent_id
WHERE a.location = 'Chicago' AND b.location = 'New York'

应该只为上面列出的表格生成“3”。

编辑:根据 Lithis 的建议添加了一个 DISTINCT a.parent_id。

EDIT2:正如 Uueerdo 所指出的，对于 2 个住在芝加哥的 sibling 和 1 个住在纽约的 sibling 的情况，child_id 或某种唯一 ID 确实会有所帮助。我已经编辑了原始查询以反射(reflect)这一点。

由于这不是您问题的真正“答案”，因为没有这样的 child_id，我会推迟 Uueerdo 的回答，抱歉!