如何在Python中用SQL子查询将过滤结果传入分组查询

现有businesses表，需求是编写单个SQL查询，找出拥有至少2家店铺的店主所在的前2热门城市（预期结果为London和Leicester）。目前已能分别实现查询前2热门城市的分组查询、查询拥有至少2家店铺店主的过滤查询，但不知如何合并为一个SQL查询。

合并后的SQL查询 #

SELECT b.city, COUNT(DISTINCT b.owner) AS qualified_owner_count
FROM businesses b
JOIN (
    -- 筛选出拥有至少2家店铺的店主
    SELECT owner
    FROM businesses
    GROUP BY owner
    HAVING COUNT(*) >= 2
) qualified_owners ON b.owner = qualified_owners.owner
GROUP BY b.city
ORDER BY qualified_owner_count DESC
LIMIT 2;

思路说明 #

• 子查询qualified_owners先筛选出满足"拥有至少2家店铺"的店主清单
• 将原表与该子查询结果关联，仅保留这些店主对应的店铺数据
• 按城市分组，用COUNT(DISTINCT owner)统计每个城市里符合条件的店主数量（避免同一店主在同一城市被重复计数）
• 按统计结果降序排列，取前2个城市，即为目标结果

数据库创建代码 #

# import libraries
import pandas as pd
import sqlite3

# create database
conn = sqlite3.connect("my_db.db")

# create table
cur = conn.cursor()
cur.execute("DROP TABLE IF EXISTS businesses;")
query = """
CREATE TABLE IF NOT EXISTS businesses (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    city TEXT NOT NULL,
    owner TEXT NOT NULL
    )
"""
cur.execute(query)
conn.commit()

# add rows to table
query = """
INSERT INTO businesses
    (id, name, city, owner)
VALUES
    (1, "Shop A", "London", "Tom"),
    (2, "Shop B", "London", "Tom"),
    (3, "Shop C", "London", "Tom"),
    (4, "Shop D", "Luton", "Alice"),
    (5, "Shop E", "Leeds", "Jenny"),
    (6, "Shop F", "Leicester", "James"),
    (7, "Shop G", "Leicester", "James"),
    (8, "Shop H", "Leicester", "Emma"),
    (9, "Shop I", "Leicester", "Emma"),
    (10, "Shop J", "Liverpool", "James"),
    (11, "Shop K", "Liverpool", "James"),
    (12, "Shop L", "Liverpool", "George"),
    (13, "Shop M", "Shefield", "Mary"),
    (14, "Shop N", "Shefield", "Mary"),
    (15, "Shop O", "Cambridge", "Oliver"),
    (16, "Shop P", "Manchester", "Harry")
"""
cur.execute(query)
conn.commit()

内容的提问来源于stack exchange，提问作者kiwi