如何用子查询合并SQL查询？获取Top10国家中的Top10城市客户数据

问题背景 #

我需要找出客户数量排名前10的国家中，客户数量排名前10的城市数据。目前已能通过手动指定Top10国家列表写出可用查询，但想用子查询（先获取客户数量Top10国家）替换手动列表，尝试合并时报错「子查询返回的列数过多」，求正确的合并方法。

可用查询（手动指定国家） #

SELECT 
    COUNT(A.customer_id) AS number_of_customers,
    D.country, C.city
FROM
    customer A
INNER JOIN 
    address B ON A.address_id = B.address_id
INNER JOIN 
    city C ON B.city_id = C.city_id
INNER JOIN 
    country D ON C.country_ID = D.country_ID
WHERE 
    country IN ('India', 'China', 'United States', 'Japan', 'Mexico', 'Brazil', 'Russian Federation', 'Phillipines', 'Turkey', 'Indonesia')
GROUP BY 
    C.city, D.country
ORDER BY 
    number_of_customers DESC
LIMIT 10

获取Top10国家的查询 #

SELECT 
    COUNT(A.customer_id) AS number_of_customers,
    D.country
FROM
    customer A
INNER JOIN 
    address B ON A.address_id = B.address_id
INNER JOIN 
    city C ON B.city_id = C.city_id
INNER JOIN 
    country D ON C.country_ID = D.country_ID
GROUP BY 
    D.country
ORDER BY 
    number_of_customers DESC
LIMIT 10

我的尝试及报错 #

SELECT 
    COUNT(A.customer_id) AS number_of_customers,
    D.country, C.city
FROM
    customer A
INNER JOIN 
    address B ON A.address_id = B.address_id
INNER JOIN 
    city C ON B.city_id = C.city_id
INNER JOIN 
    country D ON C.country_ID = D.country_ID
WHERE 
    country IN (SELECT COUNT(A.customer_id) AS number_of_customers, D.country
                FROM customer A
                INNER JOIN address B ON A.address_id = B.address_id
                INNER JOIN city C ON B.city_id = C.city_id
                INNER JOIN country D ON C.country_ID = D.country_ID
                GROUP BY D.country
                ORDER BY number_of_customers DESC
                LIMIT 10)
GROUP BY 
    C.city, D.country
ORDER BY 
    number_of_customers DESC
LIMIT 10

报错信息：

子查询返回的列数过多

错误原因 #

IN运算符要求子查询只能返回单个列（这里只需要国家名称），但你写的子查询同时返回了number_of_customers和country两列，数据库无法匹配这个多列结果，所以报错。

正确解法 #

方法一：修改子查询，只返回国家列

把子查询里的COUNT(A.customer_id)去掉，只保留D.country，让子查询仅返回国家名称列表，符合IN的要求：

SELECT 
    COUNT(A.customer_id) AS number_of_customers,
    D.country, C.city
FROM
    customer A
INNER JOIN 
    address B ON A.address_id = B.address_id
INNER JOIN 
    city C ON B.city_id = C.city_id
INNER JOIN 
    country D ON C.country_ID = D.country_ID
WHERE 
    country IN (
        SELECT D.country  -- 仅返回国家列
        FROM customer A
        INNER JOIN address B ON A.address_id = B.address_id
        INNER JOIN city C ON B.city_id = C.city_id
        INNER JOIN country D ON C.country_ID = D.country_ID
        GROUP BY D.country
        ORDER BY COUNT(A.customer_id) DESC  -- 直接用count排序，无需别名
        LIMIT 10
    )
GROUP BY 
    C.city, D.country
ORDER BY 
    number_of_customers DESC
LIMIT 10

方法二：用JOIN替代IN（性能更优）

把获取Top10国家的查询作为临时表，通过JOIN和主查询关联，这种方式在数据量大时性能更好：

SELECT 
    COUNT(A.customer_id) AS number_of_customers,
    D.country, C.city
FROM
    customer A
INNER JOIN 
    address B ON A.address_id = B.address_id
INNER JOIN 
    city C ON B.city_id = C.city_id
INNER JOIN 
    country D ON C.country_ID = D.country_ID
-- 关联Top10国家临时表
INNER JOIN (
    SELECT 
        D.country
    FROM customer A
    INNER JOIN address B ON A.address_id = B.address_id
    INNER JOIN city C ON B.city_id = C.city_id
    INNER JOIN country D ON C.country_ID = D.country_ID
    GROUP BY D.country
    ORDER BY COUNT(A.customer_id) DESC
    LIMIT 10
) AS top_countries ON D.country = top_countries.country
GROUP BY 
    C.city, D.country
ORDER BY 
    number_of_customers DESC
LIMIT 10

说明 #

两种方法都能实现需求，JOIN方式通常比IN子查询更高效，尤其是当数据库表数据量较大时，推荐优先使用。

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