使用选择的结果来分配多个条件

Question

假设我有一个与平民的虚拟数据库。每一行都包含他们的名字，城市，街道，街道号码和工作。一个最小的示例来生成SQLite的数据库：

import sqlite3
from pathlib import Path


def main():
    build_cmd = """CREATE TABLE civs (
    name TEXT,
    city TEXT,
    street TEXT,
    snumber INTEGER,
    job TEXT) """

    pdb = Path("dummy.db")
    if pdb.exists():
        pdb.unlink()
    conn = sqlite3.connect(str("dummy.db"))

    cur = conn.cursor()
    cur.execute(build_cmd)

    insert_cmds = """
    BEGIN TRANSACTION;
    INSERT INTO civs VALUES ('john', 'dublin', 'butchers_street', '42', 'butcher');
    INSERT INTO civs VALUES ('marie', 'london', 'butchers_street', '3', 'baker');
    INSERT INTO civs VALUES ('keith', 'london', 'camel_road', '7', 'carpenter');
    INSERT INTO civs VALUES ('anna', 'dublin', 'butchers_street', '9', 'butcher');
    INSERT INTO civs VALUES ('steve', 'london', 'maceys_alley', '1', 'blacksmith');
    INSERT INTO civs VALUES ('beth', 'dublin', 'butchers_street', '68', 'grocer');
    INSERT INTO civs VALUES ('lisa', 'dublin', 'church_street', '32', 'nun');
    COMMIT;
    """

    cur.executescript(insert_cmds)
    cur.close()
    conn.commit()
    conn.close()

if __name__ == '__main__':
    main()

现在，我想检索所有生活在同一城市和街道上的人，例如John。当我只想检查Street时，我可以这样工作：

SELECT civs.name, civs.city, civs.street
FROM
    civs
WHERE
    street = (
    SELECT street
    FROM civs
    WHERE
        name = 'john'
    )

但是，这当然还将返回居住在伦敦而不是都柏林的玛丽。

名称	City	Street
Marie	London	Butchers_street
John	Dublin	Butchers_street
Beth	Dublin	Butchers_street
Anna	Dublin	Butchers_street

，所以我尝试了以下内容，以下内容不起作用：

SELECT civs.name, civs.city, civs.street
FROM
    civs
WHERE
    street, city = (
    SELECT street, city
    FROM civs
    WHERE
        name = 'john'
    )

以下是有效的，但是它不是很干。有更好的方法吗？

SELECT civs.name, civs.city, civs.street
FROM
    civs
WHERE
    street = (
    SELECT street
    FROM civs
    WHERE
        name = 'john'
    )
    AND city = (
    SELECT city
    FROM civs
    WHERE
        name = 'john'
    )

注意 ：效率对我很重要。您可以假设它是一个冷表，并且所有列都是索引的。